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 functionality in Azure SQL Database is now generally available.
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 Azure AD system-assigned managed identity and user-assigned managed identity that can be created as users in SQL Database on behalf of service principals (see the article, What are managed identities for Azure resources?).
To enable this feature, the following steps are required:
1) Assign a server identity (a system managed 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) -AssignIdentity (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> -AssignIdentity (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)
To check that the user ‘myapp’ was created in the database ‘testdb’ you can execute the T-SQL command select*fromsys.database_principals.
# 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: