Microsoft Entra ID (formerly Azure Active Directory) supports two types of managed identities: system-assigned managed identity (SMI) and user-assigned managed identity (UMI). For more information, see Managed identity types.
An SMI is automatically assigned to Azure SQL Managed Instance when it's created. When you're using Microsoft Entra authentication with Azure SQL Database, you must assign an SMI when Azure service principals are used to create Microsoft Entra users in SQL Database.
Previously, only an SMI could be assigned to the Azure SQL Managed Instance or SQL Database server identity. Now, a UMI can be assigned to SQL Managed Instance or SQL Database as the instance or server identity.
In addition to using a UMI and an SMI as the instance or server identity, you can use them to access the database by using the SQL connection string option Authentication=Active Directory Managed Identity. You need to create a SQL user from the managed identity in the target database by using the CREATE USER statement. For more information, see Using Microsoft Entra authentication with SqlClient.
Benefits of using user-assigned managed identities
There are several benefits of using a UMI as a server identity:
Users have the flexibility to create and maintain their own UMIs for a tenant. You can use UMIs as server identities for Azure SQL. A UMI is managed by the user, whereas an SMI is uniquely defined per server and assigned by the system.
In the past, you needed the Microsoft Entra ID Directory Readers role when using an SMI as the server or instance identity. With the introduction of accessing Microsoft Entra ID through Microsoft Graph, users who are concerned with giving high-level permissions such as the Directory Readers role to the SMI or UMI can alternatively give lower-level permissions so that the server or instance identity can access Microsoft Graph.
Users can choose a specific UMI to be the server or instance identity for all databases or managed instances in the tenant. Or they can have multiple UMIs assigned to different servers or instances.
UMIs can be used in different servers to represent different features. For example, a UMI can serve transparent data encryption (TDE) in one server, and a UMI can serve Microsoft Entra authentication in another server.
UMIs are independent from logical servers or managed instances. When a logical server or instance is deleted, the SMI is also deleted. UMIs aren't deleted with the server.
Note
You must enable the instance identity (SMI or UMI) to allow support for Microsoft Entra authentication in SQL Managed Instance. For SQL Database, enabling the server identity is optional and required only if a Microsoft Entra service principal (Microsoft Entra application) oversees creating and managing Microsoft Entra users, groups, or applications in the server. For more information, see Microsoft Entra service principal with Azure SQL.
After the UMI is created, some permissions are needed to allow the UMI to read from Microsoft Graph as the server identity. Grant the following permissions, or give the UMI the Directory Readers role.
These permissions should be granted before you provision a logical server or managed instance. After you grant the permissions to the UMI, they're enabled for all servers or instances that are created with the UMI assigned as a server identity.
Application.Read.ALL: Allows access to Microsoft Entra service principal (application) information.
Grant permissions
The following sample PowerShell script grants the necessary permissions for a managed identity. This sample assigns permissions to the user-assigned managed identity umiservertest.
To run the script, you must sign in as a user with a Global Administrator or Privileged Role Administrator role.
The script grants the User.Read.All, GroupMember.Read.All, and Application.Read.ALL permissions to a managed identity to access Microsoft Graph.
# Script to assign permissions to an existing UMI
# The following required Microsoft Graph permissions will be assigned:
# User.Read.All
# GroupMember.Read.All
# Application.Read.All
Import-Module Microsoft.Graph.Authentication
Import-Module Microsoft.Graph.Applications
$tenantId = "<tenantId>" # Your tenant ID
$MSIName = "<managedIdentity>"; # Name of your managed identity
# Log in as a user with the "Global Administrator" or "Privileged Role Administrator" role
Connect-MgGraph -TenantId $tenantId -Scopes "AppRoleAssignment.ReadWrite.All,Application.Read.All"
# Search for Microsoft Graph
$MSGraphSP = Get-MgServicePrincipal -Filter "DisplayName eq 'Microsoft Graph'";
$MSGraphSP
# Sample Output
# DisplayName Id AppId SignInAudience ServicePrincipalType
# ----------- -- ----- -------------- --------------------
# Microsoft Graph 47d73278-e43c-4cc2-a606-c500b66883ef 00000003-0000-0000-c000-000000000000 AzureADMultipleOrgs Application
$MSI = Get-MgServicePrincipal -Filter "DisplayName eq '$MSIName'"
if($MSI.Count -gt 1)
{
Write-Output "More than 1 principal found with that name, please find your principal and copy its object ID. Replace the above line with the syntax $MSI = Get-MgServicePrincipal -ServicePrincipalId <your_object_id>"
Exit
}
# Get required permissions
$Permissions = @(
"User.Read.All"
"GroupMember.Read.All"
"Application.Read.All"
)
# Find app permissions within Microsoft Graph application
$MSGraphAppRoles = $MSGraphSP.AppRoles | Where-Object {($_.Value -in $Permissions)}
# Assign the managed identity app roles for each permission
foreach($AppRole in $MSGraphAppRoles)
{
$AppRoleAssignment = @{
principalId = $MSI.Id
resourceId = $MSGraphSP.Id
appRoleId = $AppRole.Id
}
New-MgServicePrincipalAppRoleAssignment `
-ServicePrincipalId $AppRoleAssignment.PrincipalId `
-BodyParameter $AppRoleAssignment -Verbose
}
Check permissions for user-assigned managed identity
To check permissions for a UMI, go to the Azure portal. In the Microsoft Entra ID resource, go to Enterprise applications. Select All Applications for Application type, and search for the UMI that was created.
Select the UMI, and go to the Permissions settings under Security.
Get or set a managed identity for a logical server or managed instance
You can't change the server administrator or password, or change the Microsoft Entra admin, by rerunning the provisioning command for the ARM template.
Limitations and known issues
After you create a managed instance, the Microsoft Entra admin page for your managed instance in the Azure portal shows a warning: Managed Instance needs permissions to access Microsoft Entra ID. Click here to grant "Read" permissions to your Managed Instance. If you gave the UMI the appropriate permissions discussed earlier in this article, you can ignore this warning.
If you use an SMI or a UMI as the server or instance identity, deleting the identity will make the server or instance unable to access Microsoft Graph. Microsoft Entra authentication and other functions will fail. To restore Microsoft Entra functionality, assign a new SMI or UMI to the server with appropriate permissions.
To grant permissions to access Microsoft Graph through an SMI or a UMI, you need to use PowerShell. You can't grant these permissions by using the Azure portal.