Managed identities in Microsoft Entra for Azure SQL

Article
08/12/2024

Applies to: Azure SQL Database Azure SQL Managed Instance

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.

To retrieve the current UMI(s) or SMI for Azure SQL Managed instance or Azure SQL Database, see Get or set a managed identity for a logical server or managed instance later in this article.

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.

For more information on providing Directory Readers permissions and its function, see Directory Readers role in Microsoft Entra ID for Azure SQL.
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.
You need a UMI to create a logical server in Azure configured with TDE with customer-managed keys (CMKs). For more information, see Customer-managed transparent data encryption using user-assigned managed identity.
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.

Create a user-assigned managed identity

For information on how to create a UMI, see Manage user-assigned managed identities.

Permissions

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.

Important

Only a Global Administrator or Privileged Role Administrator can grant these permissions.

User.Read.All: Allows access to Microsoft Entra user information.
GroupMember.Read.All: Allows access to Microsoft Entra group information.
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 -Environment China -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.

Screenshot of user-assigned managed identity permissions.

Get or set a managed identity for a logical server or managed instance

To create a server by using a UMI, see the following guide: Create an Azure SQL logical server by using a user-assigned managed identity.

Get the SMI for Azure SQL Database logical server

The Azure portal displays the system-assigned managed identity (SMI) ID in the Properties menu of the Azure SQL Database logical server.

Screenshot of the Azure portal page for an Azure SQL Database logical server. In the Properties menu, the System Assigned Managed Identity is highlighted.

To retrieve the UMI(s) for Azure SQL Managed Instance or Azure SQL Database, use the following PowerShell or Azure CLI examples.
To retrieve the SMI for Azure SQL Managed Instance, use the following PowerShell or Azure CLI examples.

Set a managed identity in the Azure portal

To set the user-managed identity for the Azure SQL Database logical server or Azure SQL Managed Instance in the Azure portal:

Go to your SQL server or SQL managed instance resource.
Under Security, select the Identity setting.
Under User assigned managed identity, select Add.
Select a subscription, and then for Primary identity, select a managed identity for the subscription. Then choose the Select button.

Azure portal screenshot of selecting a user-assigned managed identity when configuring an existing server identity.

Create or set a managed identity by using the Azure CLI

The Azure CLI 2.26.0 (or later) is required to run these commands with a UMI.

Azure SQL Database managed identity using the Azure CLI

To provision a new server with a user-assigned managed identity, use the az sql server create command.
To obtain the managed identities for a logical server, use the az sql server show command.
- For example, to retrieve the user-assigned managed identities of a logical server, look for the principalId of each:
```
az sql server show --resource-group "resourcegroupnamehere" --name "sql-logical-server-name-here" --query identity.userAssignedIdentities
```
- To retrieve the system-assigned managed identity of an Azure SQL Database logical server:
```
az sql server show --resource-group "resourcegroupnamehere" --name "sql-logical-server-name-here" --query identity.principalId
```
To update the UMI's server setting, use the az sql server update command.

Azure SQL Managed Instance managed identity using the Azure CLI

To provision a new managed instance with a UMI, use the az sql mi create command.
To obtain the system-assigned and user-assigned managed identities for managed instances, use the az sql mi show command.
- For example, to retrieve the UMI(s) for a managed instance, look for the principalId of each:
```
az sql mi show --resource-group "resourcegroupnamehere" --name "sql-mi-name-here" --query identity.userAssignedIdentities
```
- To retrieve the SMI of a managed instance:
```
az sql mi show --resource-group "resourcegroupnamehere" --name "sql-mi-name-here" --query identity.principalId
```
To update the UMI's managed instance setting, use the az sql mi update command.

Create or set a managed identity by using PowerShell

Az.Sql module 3.4 or later is required for using PowerShell with a UMI. The latest version of PowerShell is recommended.

Azure SQL Database managed identity using PowerShell

To provision a new server with a UMI, use the New-AzSqlServer command.

To obtain the managed identities for a logical server, use the Get-AzSqlServer command.

For example, to retrieve the UMI(s) of a logical server, look for the principalId of each:

$MI = Get-AzSqlServer -ResourceGroupName "resourcegroupnamehere" -Name "sql-logical-server-name-here"
$MI.Identity.UserAssignedIdentities | ConvertTo-Json

To retrieve the SMI of an Azure SQL Database logical server:

$MI = Get-AzSqlServer -ResourceGroupName "resourcegroupnamehere" -Name "sql-logical-server-name-here"
$MI.Identity.principalId

To update the UMI's server setting, use the Set-AzSqlServer command.

Azure SQL Managed Instance managed identity using PowerShell

To provision a new managed instance with a UMI, use the New-AzSqlInstance command.

To obtain the managed identities for a managed instance, use the Get-AzSqlInstance command.

For example, to retrieve the UMI(s) of a managed instance, look for the principalId of each:

$MI = Get-AzSqlInstance -ResourceGroupName "resourcegroupnamehere" -Name "sql-mi-name-here"
$MI.Identity.UserAssignedIdentities | ConvertTo-Json

To retrieve the SMI of a managed instance:

$MI = Get-AzSqlInstance -ResourceGroupName "resourcegroupnamehere" -Name "sql-mi-name-here"
$MI.Identity.principalId

To update the UMI's managed instance setting, use the Set-AzSqlInstance command.

Create or set a managed identity by using the REST API

To update the UMI settings for the server, you can also use the REST API provisioning script used in Create a logical server by using a user-assigned managed identity or Create a managed instance by using a user-assigned managed identity. Rerun the provisioning command in the guide with the updated user-assigned managed identity property that you want to update.

Create or set a managed identity by using an ARM template

To update the UMI settings for the server, you can also use the Azure Resource Manager template (ARM template) used in Create a logical server by using a user-assigned managed identity or Create a managed instance by using a user-assigned managed identity. Rerun the provisioning command in the guide with the updated user-assigned managed identity property that you want to update.