Set up managed identity and Microsoft Entra authentication for SQL Server enabled by Azure Arc

Applies to: SQL Server 2025 (17.x)

This article provides step-by-step instructions for setting up and configuring Microsoft Entra ID managed identity for SQL Server enabled by Azure Arc.

For an overview of managed identity with SQL Server, see Managed identity for SQL Server enabled by Azure Arc.

Prerequisites

Before you can use a managed identity with SQL Server enabled by Azure Arc, make sure that you meet the following prerequisites:

• Supported for SQL Server 2025 and later, running on Windows.
• Connect your SQL Server to Azure Arc.
• The latest version of the Azure Extension for SQL Server.

Enable the primary managed identity

If you've installed the Azure Extension for SQL Server to your server, you can enable the primary managed identity for your SQL Server instance directly from the Azure portal. It's also possible to enable the primary managed identity manually by updating the registry, but should be done with extreme caution.

Grant application permissions to the identity

The system-assigned managed identity, which uses the Arc-enabled machine name, must have the following Microsoft Graph application permissions (app roles):

• 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.

You can use PowerShell to grant required permissions to the managed identity. Alternatively, you can create a role-assignable group. After the group is created, assign the Directory Readers role or the User.Read.All, GroupMember.Read.All, and Application.Read.All permissions to the group, and add all system-assigned managed identities for your Azure Arc-enabled machines to the group. We don't recommend using the Directory Readers role in your production environment.

The following PowerShell script grants the required permissions to the managed identity. Make sure this script is run on PowerShell 7.5 or a later version, and has the Microsoft.Graph module 2.28 or later installed.

# Set your Azure tenant and managed identity name
$tenantID = '<Enter-Your-Azure-Tenant-Id>'
$managedIdentityName = '<Enter-Your-Arc-HostMachine-Name>'

# Connect to Microsoft Graph
try {
    Connect-MgGraph -TenantId $tenantID -ErrorAction Stop
    Write-Output "Connected to Microsoft Graph successfully."
}
catch {
    Write-Error "Failed to connect to Microsoft Graph: $_"
    return
}

# Get Microsoft Graph service principal
$graphAppId = '00000003-0000-0000-c000-000000000000'
$graphSP = Get-MgServicePrincipal -Filter "appId eq '$graphAppId'"
if (-not $graphSP) {
    Write-Error "Microsoft Graph service principal not found."
    return
}

# Get the managed identity service principal
$managedIdentity = Get-MgServicePrincipal -Filter "displayName eq '$managedIdentityName'"
if (-not $managedIdentity) {
    Write-Error "Managed identity '$managedIdentityName' not found."
    return
}

# Define roles to assign
$requiredRoles = @(
    "User.Read.All",
    "GroupMember.Read.All",
    "Application.Read.All"
)

# Assign roles using scoped syntax
foreach ($roleValue in $requiredRoles) {
    $appRole = $graphSP.AppRoles | Where-Object {
        $_.Value -eq $roleValue -and $_.AllowedMemberTypes -contains "Application"
    }

    if ($appRole) {
        try {
            New-MgServicePrincipalAppRoleAssignment   -ServicePrincipalId $managedIdentity.Id `
                -PrincipalId $managedIdentity.Id `
                -ResourceId $graphSP.Id `
                -AppRoleId $appRole.Id `
                -ErrorAction Stop

            Write-Output "Successfully assigned role '$roleValue' to '$managedIdentityName'."
        }
        catch {
            Write-Warning "Failed to assign role '$roleValue': $_"
        }
    }
    else {
        Write-Warning "Role '$roleValue' not found in Microsoft Graph AppRoles."
    }
}

Create logins and users

Follow the steps in the Microsoft Entra tutorial to create logins and users for the managed identity.

Related content

• Managed identity for SQL Server enabled by Azure Arc
• Microsoft Entra authentication for SQL Server
• What is managed identities for Azure resources?