Opening
Azure Data Factory (ADF) has emerged as a powerful tool for seamlessly connecting and orchestrating data flows across diverse data sources and destinations. One such destination is Azure SQL Database, a highly scalable and secure relational database service within the Azure ecosystem. To establish a secure and private connection between ADF and Azure SQL Database, private endpoints and system-assigned managed identities come into play.
This blog post delves into the process of connecting ADF to Azure SQL Database. By leveraging private endpoints, we eliminate the need for publicly accessible endpoints, enhancing the overall security posture of the data pipeline. Additionally, system-assigned managed identity simplifies identity management, allowing ADF to automatically authenticate with Azure SQL Database without the need for manual credential configuration.
Throughout this guide, I will walk you through the step-by-step instructions for configuring private endpoints and system-assigned managed identities, enabling you to establish a secure and private connection between ADF and Azure SQL Database. By the end of this post, you will have a comprehensive understanding of integrating these powerful Azure services to achieve a robust and secure data pipeline. The same principles can be applied to other Azure resources connecting to all offerings of Azure SQL, making it a versatile approach for securing data access within the Azure ecosystem.
Middlegame
For this guide, I will walk you through creating and configuring the following resources:
- Resource group (DemoRG)
- Azure Data Factory (SQLChessDemoADF)
- Azure SQL Database (DemoDB)
- Two Azure Active Directory (Microsoft Entra ID) Groups: DBA (to be set as Database Admin on the Azure SQL DB) and ServicePrincipals (a group with all the managed identities as members, to which we’ll grant read access to the Azure SQL Db)
1. Create a resource group
2. Create two Azure Active Directory groups (DBA & ServicePrincipals)
After creating both groups, add your account as a member of DBA. This will allow you to connect to the database using SQL Server Management Studio.
3. Create an Azure SQL logical server
The key difference from what might be considered a default logical SQL Server installation is using one of the two authentication methods containing Microsoft Entra. I went for the Entra-only in this demo, but the SQL + Entra is also a good option, depending on your setup. By enabling Entra and setting up the DBA group as database admin on the logical SQL Server, two things are achieved:
- All members of the DBA group inherit admin permissions on this Azure SQL logical server.
- Entra authentication becomes enabled, and other Entra users or groups can be granted different permissions on databases hosted on this Azure SQL logical server.
4. Create an Azure SQL DB
5. Create an Azure Data Factory
A system-assigned managed identity is created as part of the Azure Data Factory deployment. To find it, you need to navigate to Microsoft Entra ID and follow the next steps:
You will notice that the managed identity was created with the same name as the ADF. We’ll add it to the ServicePrincipals group, and then we’ll grant the group permissions on some tables in our Azure SQL DB using the following script:
--Master database
CREATE LOGIN [ServicePrincipals] FROM EXTERNAL PROVIDER;
GO
--DemoDB database
CREATE USER [ServicePrincipals] FROM LOGIN [ServicePrincipals];
GO
GRANT SELECT ON [SalesLT].[Customer] TO [ServicePrincipals];
GO
GRANT SELECT ON [SalesLT].[Product] TO [ServicePrincipals];
GO
6. Configure a managed private endpoint in ADF to Azure SQL DB
7. Create a linked service from ADF to Azure SQL DB
Endgame
Now that we have finished setting up our private connection between ADF and Azure SQL DB, using managed identities as an authentication method, let’s test it and confirm functionality:
Our ADF has successfully connected to our Azure SQL DemoDB and can only access the tables we granted access to in Step 5. The same principles described in this article can be used to connect any Azure resource that supports system-assigned managed identities and private endpoints to Azure SQL DB.
Useful links:
