Azure SQL - Create SQL User From Managed Identity
Add a SQL user from a Managed Identity without granting additional Entra ID permissions
When using Managed Identities in Azure, a common requirement is creating a SQL user for your app's Managed Identity in your Azure SQL Database from some CI/CD pipeline. Sometimes apps need to talk to a database - who knew?
According to the documentation, this requires that you grant the SQL Server identity additional permissions, which in turn can only be granted by either a Global Administrator or Privileged Role Administrator.
Once the SQL Server identity has these permissions, one can execute the following query to add the application's identity to the database:
-- Create the user with details retrieved from Entra ID
CREATE USER [ManagedIdentityName] FROM EXTERNAL PROVIDER
-- Assign roles to the new user
ALTER ROLE db_datareader ADD MEMBER [ManagedIdentityName]
ALTER ROLE db_datawriter ADD MEMBER [ManagedIdentityName]
Common strategies for enabling this at scale are:
Create a single User Assigned Managed Identity with the required permissions & associate it to all SQL Servers
Create a dedicated Entra ID group for SQL identities & grant it the Directory Readers role
There exists, however, an alternative approach which doesn't require any of the above pre-configuration.
Given the Client ID of your app's Managed Identity, you can use the following PowerShell to convert the client ID into the SQL Server's User SID format:
$managedIdentitySid = "0x" + [System.BitConverter]::ToString(([guid]$managedIdentityClientId).ToByteArray()).Replace("-", "")
And then add the user to the SQL Database as follows:
-- Create the user using its SID - does not need to talk to Entra ID
CREATE USER [ManagedIdentityName] WITH SID = <managedIdentitySid>, TYPE = E;
-- Assign roles to the new user
ALTER ROLE db_datareader ADD MEMBER [ManagedIdentityName]
ALTER ROLE db_datawriter ADD MEMBER [ManagedIdentityName]
Here is an example of an Azure Pipeline using the above approach:
- task: AzureCLI@2
displayName: 'Get Managed Identity Client ID'
inputs:
azureSubscription: ${{ parameters.serviceConnection }}
scriptType: 'pscore'
scriptLocation: 'inlineScript'
inlineScript: |
$clientId = az identity show --name $(managedIdentityName) --resource-group $(resourceGroupName) --query clientId -o tsv
echo "##vso[task.setvariable variable=managedIdentityClientId]$clientId"
- powershell: |
$clientId = '$(managedIdentityClientId)'
$sid = "0x" + [System.BitConverter]::ToString(([guid]$clientId).ToByteArray()).Replace("-", "")
Write-Host "##vso[task.setvariable variable=managedIdentitySid]$sid"
displayName: Convert Managed Identity Client ID to SID
# The Azure DevOps pipeline Service Connection (Azure AD Service Principal) needs to be a SQL Server Administrator (AAD Group) or Database Owner
# The SqlAzureDacpacDeployment@1 task can only run on windows agents
- task: SqlAzureDacpacDeployment@1
displayName: 'Add User + Roles for Managed Identity'
inputs:
azureSubscription: ${{ parameters.serviceConnection }}
AuthenticationType: 'servicePrincipal'
ServerName: '$(sqlServerName).database.windows.net'
DatabaseName: '$(sqlDatabaseName)'
TaskNameSelector: 'InlineSqlTask'
SqlInline: |
IF NOT EXISTS (SELECT [name] FROM [sys].[database_principals] WHERE [name] = '$(managedIdentityName)')
CREATE USER [$(managedIdentityName)] WITH SID = $(managedIdentitySid), TYPE = E;
GO
ALTER ROLE [db_datareader] ADD MEMBER [$(managedIdentityName)];
ALTER ROLE [db_datawriter] ADD MEMBER [$(managedIdentityName)];
GO
IpDetectionMethod: 'AutoDetect'
I can't take credit for the PowerShell script, I found it by happenstance in this StackOverflow answer by Thomas Vercoutre. Thomas you legend! ๐