Start by creating a Microsoft Entra application and service principal that can access resources like those mentioned in this documentation :
- Navigate to Azure Active Directory:
- Go to the Azure portal.
- Search for and select "Azure Active Directory".
- 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".
- 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 :
- Go to the SQL Server Resource:
- Search for and select your SQL Server resource in the Azure portal.
- 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.
- 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).
- 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 :
- 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.
- Set Authentication Type:
- In the "Linked service" configuration, set the Authentication type to "Service Principal".
- 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.
- 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