Prerequisites: Synapase Analytics workspace is created along with Data Lake Storage gen2
Step 1: Create new linked Service for a workspace
Log in to Synapase analytics workspace from Azure portal and Create new Linked services and select Azure Synapse Analytics (SQL DW)
Set up Authentication method for linked service as Managed Identity.
Once a linked service is created from Azure portal then it is easy modify/update its properties programmatically using below PowerShell script.
#Input Parameters
$username = "YourAzureUserEmail"
$password = "YourPassword"
$SecPasswd = ConvertTo-SecureString $password -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($username,$SecPasswd)
$subscription = "YourAzureSubsciptionName"
#Login to Azure Account
Connect-AzAccount -SubscriptionName $subscription -Credential $myCred
Import-Module Az.Synapse
Set-AzSynapseLinkedService -WorkspaceName deepakws -Name deepaktest -DefinitionFile "D:\\LinkedService.json"
Updated defination file i.e LinkedService.Json based on your local configuration i.e SQL server name, database name or Linked Service name
{
"name": "[LINKED SERVICE NAME]",
"type": "Microsoft.Synapse/workspaces/linkedservices",
"properties": {
"annotations": [],
"type": "AzureSqlDW",
"typeProperties": {
"connectionString": "Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=[SQL SERVER].database.windows.net;Initial Catalog=[DB_Name]"
},
"connectVia": {
"referenceName": "AutoResolveIntegrationRuntime",
"type": "IntegrationRuntimeReference"
}
}
}
Step 2: Provision Azure AD Admin for SQL Server
$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 3: Create contained database user for Managed Identity
# 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
