In data engineering a common challenge is to securely establish communication between different services. By providing an Azure resource identity in Azure AD and using it to obtain Azure Active Directory (Azure AD) tokens, Managed identities (formerly known as Managed Service Identity) remove the need for developers to manage credentials.
In this blog, we will cover steps to follow to configure Managed indentity when classical SQL DW is created under SQL Server in Azure.
Note: If you have created dedicated SQL pool inside Synapse analytics workspace then you don’t have to follow below steps to configure Managed identity. For dedicated SQL Pool you can use default linked services.
Step 1: New Linked Service
Log in to Synapase analytics workspace from Azure portal and Create new Linked services and select Azure Synapse Analytics (SQL DW)
Step 2: Edit Linked Service:
Enter fully qualified domain name for SQL DW, database name and make sure to select authentication type as Managed Identity
Name of Managed identity will same as your workspace name i.e deepakws in this case.
Step 3: Provision Azure AD admin:
We need to set up Active Directory admin for SQL Server, we recommend using a service account. Read Microsoft KB here
PowerShell code to Set Active Directory admin
$username = "YourAzureUserEmail"
$password = "YourPassword"
$SecPasswd = ConvertTo-SecureString $password -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($username,$SecPasswd)
$subscription = "YourAzureSubsciptionName"
$server = "SQLServerName"
$resourcegroup = "ResourceGroupName"
$adminuser = "ActiveDirectoryAdminUserEmail"
Connect-AzAccount -SubscriptionName $subscription -Credential $myCred
Get-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $resourcegroup -ServerName $server | Format-List
Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $resourcegroup -ServerName $server -DisplayName $adminuser
Step 4: Create contained database user for Managed Identity using SSMS
Log in to SSMS using Azure Active Directory authentication and user having at least the ALTER ANY USER permission
Run following T-SQL (once off per managed identity)
CREATE USER <ManagedIdentityName> FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember db_owner, <ManagedIdentityName>;
Powershell to create Contained Users
# Import the module
Import-Module Az.Sql -Force
# Setup your parameters
$params = @{
'Database' = 'DatabaseName'
'ServerInstance' = 'SQLServerName.database.windows.net'
'username' = 'YourUserName'
'Password' = 'YourPassword'
'OutputSqlErrors' = $true
'Query' = 'CREATE USER ManagedIdentityName FROM EXTERNAL PROVIDER; EXEC sp_addrolemember db_owner, ManagedIdentityName;'
}
Invoke-Sqlcmd @params
