In this article we will have a step by step demo on how usingAzure Managed Identity, Azure Data Factory authenticates Azure SQL Database and Synapse Workspace authenticates Azure Synapse Analytics.
Microsoft Recommendation on Passwordless Authentication
Managed identities are the recommended authentication option for secure, passwordless connections between Azure resources.
Azure Managed Identity Authentication: WHAT it is and WHY we need it?
“A common challenge for developers is the management of secrets, credentials, certificates, and keys used to secure communication between services. Managed identities eliminate the need for developers to manage these credentials.” : Microsoft
- Managed identities allow authentication of Azure resource without credentials.
- So it is not required to manage credential for authentication of Azure resource.
- This Identity is provided and managed by Azure Active Directory (Azure AD) to Azure service instance.
- Managed identities provide an identity for the service instance when connecting to resources that support Azure Active Directory (Azure AD) authentication
- The Service Principal is created for the identity of resource in the Azure AD tenant is trusted by the subscription.
Example: Azure Data Factory can access Azure SQL Database using Managed Identity i.e. without using credential or password of Azure SQL Database which is passwordless manner.
Azure Managed Identity:Two types of managed identities:
- System-assigned:
- We need to enable a Azure managed identity directly on a service instance
- It’s life time in Azure AD is tied to that service instance’s lifecycle.
- By design, only that Azure resource can use this identity to request tokens from Azure AD.
- User-assigned:
- We need to create a managed identity as a standalone Azure resource.
- It can be assigned it to one or more instances of a Azure resource.
- It’s lifetime does not depend on the life cycle of resource tagged to it.
In this tutorial focus on System assigned managed identity i.e. Managed Identity.
Configuration required for this demo:
- Enable managed identity for Azure Data Factory, Azure Synapse
- Configure source Azure SQL Server & Azure SQL Database
- Create contained database users for the managed identity and grant access permission.
Enable Manged Identity of Azure Data Factory & Azure Synapse:
- When we create a data factory or Synapse workspace through Azure portal or PowerShell, managed identity will always be created automatically.
- A data factory or Synapse workspace is mapped with a managed identity for Azure resources representing service when authenticating to other resources in Azure.
- We can use this Azure Managed Identity for Azure SQL Database authentication.
- So data factory or Synapse workspace can access and copy data from or to that database by using this identity.
Configure Source Azure SQL Server & Azure SQL Database
Configure Azure SQL Server level Authentication and Access:
First, let’s create a managed identity for Azure SQL Server as shown in below image:
- Open Azure portal and login
- Navigate and select Azure SQL Server
- Select Identity at left hand side under Security as highlighted in image
- Then click the status ‘On’ under System assigned managed identity and save.
Allow Azure resources to access this server:
We can allow it Azure SQL server creation or after creation by
- Navigate to Networking,
- then check Allow Azure services and resources to access this server as shown in below image.
Database Level Setup: Enable Azure AD authentication
For Azure Data factory or Synapse workspace connect to Azure SQL Database or dedicated SQL Pool using a managed identity, we need to perform below steps to provide the Managed Identity permission to the database.
- Set Active Directory admin for Azure SQL Server
- Create a contained database user and grant required permission to Managed Identity.
Set Active Directory admin for Azure SQL Server:
We need to perform below steps to set an Active Directory admin using Azure AD account to access to this server.
- Navigate to Azure SQL Database or Azure Synapse Analytics SQL Pool resource
- Select Active Directory Admin or SQL Active Directory Admin under Settings, for SQL Server and Synapse Workspace respectively.
Initially there is no admin is se up hence it is mentioned as ‘No Active Directory admin‘ in below image:
- Then, in the above image as shown please select ‘Set admin‘ to find and select user from active directory as shown in below image.
A shown in above image, On the Active Directory admin page, search for a user or group to be an administrator for the SQL Server and click Select.
Once it is selected and saved, we can see admin set up completed as shown at below image:
This user from Azure Active Directory will be used to add contained user and provide access permission to sql database in next section.
We can skip this step if we allow Synapse workspace connect to source Azure SQL Database via SQL authentication.
Create a contained database user:
Azure SQL Database and Azure Synapse Analytics support Azure Active Directory identities as contained database users
Here we will create a contained database user in Azure SQL or Azure Synapse database that is mapped to the Azure Active Directory identity.
In this case, we will create a contained database user for Azure Data Factory and Azure Synapse.
For that, first let us open Sql Server Management Studio and log into Azure Sql Server:
- Server name: copy from overview page of sql server in azure portal
- Authentication: Azure Active Directory – Universal with MFA
- Login using the same account which was set as Azure Active Directory Admin in sql server.
- Provide the SQL Database name where user will be created
- So, create contained database user for the managed identity i.e, of Azure Data Factory and Azure Synapse.
Run below script for Azure Data Factory and Azure Synapse:
- Create user command will create user in that SQL database as same name of managed identity of synapse workspace or ADF. Here let’s provide synapse workspace or ADF name only as user.
- Next, we will grant permission to that user for that managed identity to access the database by synapse or ADF.
SQL
CREATE USER <workspace name> FROM EXTERNAL PROVIDER; ALTER ROLE [db_owner] ADD MEMBER <workspace name>;Create Contained user for managed identity of Azure Data Factory & Grant access:
Please note while we test authentication, Managed Identity name will be demoadf9 for Data Factory.
Create Contained user for managed identity of Azure Synapse workspace;
Please note while we test authentication, Managed Identity name will be demosynapsews for Synapse workspace
Test the authentication of Azure SQL database by Azure Data Factory:
To do the test, let’s open data factory and
- go to ‘Manage‘ tab at left side of window and
- select ‘Linked Service‘ and
- then Create new linked service.
- After that at the right hand side, search and select for Azure SQL database and provide the required parameter as shown in below image.
- Now test the connection. it will show connection successful as shown in below image.
Please note Managed Identity name as demoadf9 for which we created contained user and given access permission.
Test the authentication of Azure Synapse Analytics by Azure Synapse:
Let’s open Synapse workspace and
- go to ‘Manage‘ tab at left side of window and
- select ‘Linked Service‘ and
- select Azure Synapse Analytics linked service which is already created as part workspace set up.
- If dedicated SQL pool is not created yet, please one for the dedicated SQL end point.
- Now provide the dedicated SQL pool name as shown in below image & test the connection. it will show connection successful.
Please note Managed Identity name as demosynapsews for which we created contained user and given access permission.
Azure SQL database connection authentication by Synapse workspace:
Please note, here Authentication Type is System Assigned Managed Identity and Managed Identity name is dempsynapsews. This is to explain that synapse workspace connecting Azure Sql Database.
So in this article we understood how Azure Data Factory authenticates Azure SQL Database and Synapse Workspace authenticate Azure Synapse Analytics using Azure Managed Identity.
Thanks for reading; Please feel free to share your query or suggestion in comment section below.
Happy Learning.