Support for Azure AD user creation on behalf of Azure AD Applications for Azure SQL

Published 07-27-2020 11:59 AM 4,624 Views

We are announcing a public preview for Azure AD user creation support for Azure SQL Database and Azure Synapse Analytics on behalf of Azure AD Applications (service principals). See Azure Active Directory service principal with Azure SQL.

What support for Azure AD user creation on behalf of Azure AD Applications means?

Azure SQL Database, Azure Synapse Analytics (formerly SQL Data Warehouse), and SQL Managed Instance support the following Azure AD objects: 

  1. Azure AD users (managed, federated and guest) 
  2. Azure AD groups (managed and federated) 
  3. Azure AD applications  

For more information on Azure AD applications, see Application and service principal objects in Azure Active Directory  and Create an Azure service principal with Azure PowerShell. 

Formerly, only SQL Managed Instance supported the creation of those Azure AD object types on behalf of an Azure AD Application (using service principal). Support for this in Azure SQL Database and Azure Synapse Analytics is now in public preview.

This functionality is useful for automated processes where Azure AD objects are created and maintained in Azure SQL Database without human interaction by Azure AD applications. Since service principals could be an Azure AD admin for SQL DB as part of a group or an individual user, automated Azure AD object creation in SQL DB can be executed. This allows for a full automation of a database user creation. This functionality is also supported for system-assigned managed identity and user-assigned managed identity (see the article, What are managed identities for Azure resources?).



To enable this feature, the following steps are required:

1)   Assign a server identity during SQL logical server creation or after the server is created.

      See the PowerShell example below:

  • To create a server identity during the Azure SQL logical server creation, execute the following command:  

         New-AzSqlServer -ResourceGroupName <resource group>
         -Location <Location name> -ServerName <Server name>
         -ServerVersion "12.0" -SqlAdministratorCredentials (Get-Credential)
         (See the New-AzSqlServer command for more details)  


  • For existing Azure SQL logical servers, execute the following command:

         Set-AzSqlServer -ResourceGroupName <resource group>
         -ServerName <Server name>
         (See the  Set-AzSqlServer command for more details)  
         To check if a server identity is assigned to the Azure SQL logical 
         server, execute 
the following  command:
         Get-AzSqlServer -ResourceGroupName <resource group> 
         - ServerName <Server name>
         (See the Get-AzSqlServer command for more details)


2)   Grant the Azure AD “Directory Readers” permission to the server identity
      created above
     (For more information, see Provision Azure AD admin (SQL Managed Instance) 


How to use it

Once steps 1 and 2 are completed, an Azure AD application with the right permissions can create an Azure AD object (user/group or service principal) in Azure SQL DB. For more information, see the step-by-step tutorial doc ( see Tutorial: Create Azure AD users using Azure AD applications ).



Using SMI (System-assigned Managed Identity) set up as an Azure AD admin for SQL DB,
create an Azure AD application as a SQL DB user.



Enable steps 1 and 2 indicated above for the Azure SQL logical server

  • In the example below, the server name is ‘testaadsql’
  • The user database created under this serve is ‘testdb
  • Copy the display name of the application
    • In the example below the app name is ‘myapp
  • Using the Azure portal, assign your SMI (display name) as an Azure AD admin for the Azure SQL logical server  (see the screenshot below).




  • Create Azure AD application user in SQL DB on behalf of the SMI
  • To check that the user ‘myapp’ was created in the database ‘testdb’ you can execute the T-SQL command select * from sys.database_principals.

PowerShell Script

# PS script creating a SQL user myapp from an Azure AD application on behalf of SMI “mytestvm”
#  that is also set as Azure AD admin for SQ DB
# Execute this script from the Azure VM with SMI name ‘mytestvm’   
# Azure AD application - display name  ‘myapp’
# This is the user name that is created in SQL DB ‘testdb’ in the server  ‘testaadsql’

# Metadata service endpoint for SMI, accessible only from within the VM:

$response = Invoke-WebRequest -Uri '' -Method GET -Headers @{Metadata="true"}

$content = $response.Content | ConvertFrom-Json
$AccessToken = $content.access_token

# Specify server name and database name
# For the server name, the server identity must be assigned and "Directory Readers"
# permission granted to the identity

$SQLServerName = "testaadsql"
$DatabaseName = 'testdb'

$conn = New-Object System.Data.SqlClient.SQLConnection
$conn.ConnectionString = "Data Source=$;Initial Catalog=$DatabaseName;Connect Timeout=30"
$conn.AccessToken = $AccessToken


# Create SQL DB user [myapp] in the ‘testdb’ database

$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($ddlstmt, $conn)      
Write-host " "
Write-host "SQL DDL command was executed"
Write-host "results"


For feedback/questions on this preview feature, please reach out to the SQL AAD team at  




Frequent Visitor

Great news @Mirek Sztajno , thanks! A few questions:

  • Is there a way to inspect current ‘server identity’ (as described in prerequisites section) in Azure Portal?
  • Is there a way to set server identity using ARM template?
  • Is there any reason I should NOT set the same identity as both Azure AD admin and ‘server identity’ in SQL Azure?

@arek-avanade ,

#1. There is no portal support currently in sql blade where you see server identity. If you go to AAD blade -> Enterprise applications -> all applications . In drop down select application type = all applications, there if you give server name, you can see the identity. 

#2. There is an ARM API you can use to set this and plug it in template. 

PUT -<subscriptionId>/resourceGroups/<resource_group_name>/pro...

  "identity": {
  "type": "SystemAssigned"
  "location": "<server location like westus>"

#3. These are different identities, you will not be able to use server identity for login as it will not let you create any secret. 



Frequent Visitor

Thanks @AmolAgarwalSQL. Now I understand 'server identity' is just like MSI that I usually assign to my App Services.


I realized there is a serious limitation in the above solution - if server identity needs to be part of Directory Readers role and I want to run all of this from build pipeline, that means my build agent needs to have 'Global administrator' or 'Privileged Role Administrator' role, which may be a blocker in our situation... Such limitation doesn't exist when setting MSI privileges for other resources, and I'm wondering why this had to be done this way in SQL situation.


@arek-avanade SQL needs to talk to AAD to verify the AAD user that you are about to give access too. SQL can't talk to AAD with SPN token itself as AAD does not allow that, so only way for SQL to talk to AAD is to use MSI. And AAD only allows that if MSI has Directory Readers permissions. So, in sense this limitation is coming from AAD team. AAD answer to this is to use 'Privileged Role Administrator' role AFAIK. If there are scenarios that are still blocked, please send Mirek a descriptive email of the scenario and we will involve AAD team and discuss that. 

Frequent Visitor

@AmolAgarwalSQL  I understand, but IMHO the limitation is more bound to how MSI is handled on the SQL side. With other types of Azure resources I can set privileges using MSIs directly in my ARM template, so I'm guessing that the user deploying the template needs to have basically Directory Readers role for this to work. Easy.

However, in SQL case there is a 'man-in-the-middle' (server identity) currently and the user deploying the ARM template needs to have even higher privileges than Directory Readers so that he can give that permission to server identity. Ideally setting access to SQL based on MSIs should not differ much from doing the same for other Azure resources.

Version history
Last update:
‎Nov 09 2020 09:40 AM
Updated by: