How to create service principal, link to the azure sql database in the azure portal.

Question

I want to know how to create service principal to connect to azure SQL server database.

(I tried by creating user assigned identity. But this also not succeeded.) The user assigned manage identity is configured by, *create user assigned manage identity and then assigned sql for azure services of that. *Then this USMI is assigned to both sql server and azure data factory including for owner roles and manage identity sections as well. But still coming following error.,

Error:

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'servername came here', Database: 'db name came here', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.

Login failed for user '<token-identified principal>'., SqlErrorNumber=18456,Class=14,State=1,

Thanks

Answer 1

Start by creating a Microsoft Entra application and service principal that can access resources like those mentioned in this documentation :

1. Navigate to Azure Active Directory:
   • Go to the Azure portal.
   • Search for and select "Azure Active Directory".
2. Register a New Application:
   • In the Azure Active Directory pane, select "App registrations" > "New registration".
   • Provide a name for your application.
   • Choose the supported account types (usually "Accounts in this organizational directory only").
   • Click "Register".
3. Create a Client Secret:
   • After the app is registered, go to "Certificates & secrets" > "New client secret".
   • Provide a description and set an expiration period.
   • Click "Add" and note down the secret value (you won't be able to see it again).

Then, you need to assign a role to the application like mentioned in this section :

1. Go to the SQL Server Resource:
   • Search for and select your SQL Server resource in the Azure portal.
2. Set Active Directory Admin:
   • Under the "Settings" section, select "Active Directory admin".
   • Click "Set admin" and search for the service principal you created.
   • Select it and save the changes.
3. Add the Service Principal to the Database:
   • Navigate to the SQL Database.
   • Open "Query editor" (directly from the Azure portal or using SQL Server Management Studio).
4. Run the following SQL commands to create a user and assign roles:

      
      CREATE USER [YourServicePrincipalName] FROM EXTERNAL PROVIDER;
      
      ALTER ROLE db_datareader ADD MEMBER [YourServicePrincipalName];
      
      ALTER ROLE db_datawriter ADD MEMBER [YourServicePrincipalName];
      
   

Now you need you need to configure the connection in ADF, you can check this link for guidance :

1. Create or Configure a Linked Service:
   • Navigate to your Azure Data Factory.
   • Go to "Manage" > "Linked services".
   • Create a new linked service or edit an existing one for Azure SQL Database.
2. Set Authentication Type:
   • In the "Linked service" configuration, set the Authentication type to "Service Principal".
3. Enter Connection Details:
   • Service Principal ID: Enter the Application (client) ID of your service principal.
   • Service Principal Key: Enter the client secret you noted earlier.
   • Tenant: Enter your Azure Active Directory tenant ID.
4. Test Connection:
   • Click on "Test connection" to ensure that everything is set up correctly.

More links :

https://stackoverflow.com/questions/77717829/how-to-connect-linked-service-in-azure-data-factory

https://learn.microsoft.com/en-us/azure/data-factory/concepts-linked-services?tabs=data-factory

https://learn.microsoft.com/en-us/azure/data-factory/parameterize-linked-services?tabs=data-factory