Auto create table creation in ADF Fails due to permissions in SQL DB

Question

Hi

I am trying to create table through Auto Create Table option in ADF COPY Activity Sink. But it fails with following permissions. What kind of permissions do I need to give and to whom. I have connected from ADF to SQL DB using Private End Points. To which user do I need to give permission?

Answer 1

Hi @Krishnamohan Nadimpalli ,

Thank you for posting query in Microsoft Q&A Platform.

Here you are using Service principle to connect to SQL DB and perform table creation. Hence, we need to make sure this service principle is created as user in SQL DB and has permissions to do so.

Below are the high level steps to do.

• A service principle needs to be created within Azure Active Directory
• Assign identity to the Azure SQL server
• Assign Directory Readers permission to the Azure SQL server
• Set Azure SQL Server Active Directory admin as Azure Active Directory Group
• Create a service principal user in the Azure SQL database

Below documentation has step by step explanation of above process. Kindly check the same. Here once user created in DB make sure to give proper CREATE table permissions or db_owner permissions as per your compliance.
https://thomasthornton.cloud/2020/10/06/query-azure-sql-database-using-service-principal-with-powershell/

Kindly check below documentation as well for same.
https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial

Hope this helps. Please let us know if any further queries.

-------
Please consider hitting Accept Answer button. Accepted answers help community as well.