Configure and manage Microsoft Entra authentication with Azure SQL

To set the Microsoft Entra admin for your logical server in the Azure portal, follow these steps:

1. In the Azure portal Directories + subscriptions pane, choose the directory that contains your Azure SQL resource as the Current directory.

2. Search for SQL servers and then select the logical server for your database resource to open the SQL server pane.

   

3. On the SQL server pane for your logical server, select Microsoft Entra ID under Settings to open the Microsoft Entra ID pane.

4. On the Microsoft Entra ID pane, select Set admin to open the Microsoft Entra ID pane.

   

5. The Microsoft Entra ID pane shows all users, groups, and applications in your current directory and allows you to search by name, alias, or ID. Find your desired identity for your Microsoft Entra admin and select it, then click Select to close the pane.

6. At the top of the Microsoft Entra ID page for your logical server, select Save.

   

   The Object ID is displayed next to the admin name for Microsoft Entra users and groups. For applications (service principals), the Application ID is displayed.

The process of changing the administrator might take several minutes. Then the new administrator appears in the Microsoft Entra admin field.

To remove the admin, at the top of the Microsoft Entra ID page, select Remove admin, then select Save. Removing the Microsoft Entra admin disables Microsoft Entra authentication for your logical server.

To run PowerShell cmdlets, you need to have Azure PowerShell installed and running. See How to install and configure Azure PowerShell for detailed information.

The following Azure PowerShell cmdlets can be used to set and manage a Microsoft Entra admin for Azure SQL Database and Azure Synapse Analytics:

Use PowerShell command get-help to see more information for each of these commands. For example, get-help Set-AzSqlServerActiveDirectoryAdministrator.

The following script sets a Microsoft Entra administrator group named DBA_Group (sample object ID aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb) for the sample server example-server in a sample resource group named Example-Resource-Group:

$parameters = @{
    ResourceGroupName = "Example-Resource-Group"
    ServerName = "example-server"
    DisplayName = "DBA_Group"
}

Set-AzSqlServerActiveDirectoryAdministrator @parameters

The DisplayName parameter accepts either the Microsoft Entra ID display name or the User Principal Name, such as the following examples: DisplayName="Adrian King" and DisplayName="[email protected]". If you're using a Microsoft Entra group, then only the display name is supported.

The following example uses the optional ObjectID parameter:

$parameters = @{
  ResourceGroupName = "Example-Resource-Group"
  ServerName = "example-server"
  DisplayName = "DBA_Group"
  ObjectId = "aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb"
}
Set-AzSqlServerActiveDirectoryAdministrator @parameters

The following example returns information about the current Microsoft Entra admin for the server:

$parameters = @{
    ResourceGroupName = "Example-Resource-Group"
    ServerName = "example-server"
}

Get-AzSqlServerActiveDirectoryAdministrator @parameters | Format-List

The following example removes a Microsoft Entra administrator:

$parameters = @{
    ResourceGroupName = "Example-Resource-Group"
    ServerName = "example-server"
}

Remove-AzSqlServerActiveDirectoryAdministrator @parameters

You can set a Microsoft Entra admin for Azure SQL Database and Azure Synapse Analytics with the following Azure CLI commands:

For more CLI commands, see az sql server.

You can also use the Server Azure AD Administrator REST APIs to create, update, delete, and get the Microsoft Entra administrator for Azure SQL Database and Azure Synapse Analytics.

To grant your SQL managed instance read permissions to Microsoft Entra ID by using the Azure portal, sign in as a Privileged Role Administrator and follow these steps:

1. In the Azure portal, in the upper-right corner select your account, and then choose Switch directories to confirm which directory is your Current directory. Switch directories, if necessary.

   

2. In the Azure portal Directories + subscriptions pane, choose the directory that contains your managed instance as the Current directory.```

3. Search for SQL managed instances and then select your managed instance to open the SQL managed instance pane. Then, select Microsoft Entra ID under Settings to open the Microsoft Entra ID pane for your instance.

   

4. On the Microsoft Entra admin pane, select Set admin from the navigation bar to open the Microsoft Entra ID pane.

   

5. On the Microsoft Entra ID pane, search for a user, check the box next to the user or group to be an administrator, and then press Select to close the pane and go back to the Microsoft Entra admin page for your managed instance.

   The Microsoft Entra ID pane shows all members and groups within your current directory. Grayed-out users or groups can't be selected because they aren't supported as Microsoft Entra administrators. Select the identity you want to assign as your administrator.

6. From the navigation bar of the Microsoft Entra admin page for your managed instance, select Save to confirm your Microsoft Entra administrator.

   

   After the administrator change operation completes, the new administrator appears in the Microsoft Entra admin field.

   The Object ID is displayed next to the admin name for Microsoft Entra users and groups. For applications (service principals), the Application ID is displayed.

To run PowerShell cmdlets, you need to have Azure PowerShell installed and running. See How to install and configure Azure PowerShell for detailed information.

The following table lists the PowerShell cmdlets you can use to define and manage the Microsoft Entra admin for managed instances:

This example command gets information about a Microsoft Entra administrator for a managed instance named "Sample-Instance" associated with a resource group named "Example-Resource-Group".

$parameters = @{
    ResourceGroupName = "Example-Resource-Group"
    InstanceName = "Sample-Instance"
}

Get-AzSqlInstanceActiveDirectoryAdministrator @parameters

This example command sets the Microsoft Entra administrator to a group named DBAs (with sample object ID aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb) for the SQL Managed Instance named "Sample-Instance". This server is associated with the resource group "Example-Resource-Group".

$parameters = @{
    ResourceGroupName = "Example-Resource-Group"
    InstanceName = "Sample-Instance"
    DisplayName = "DBAs"
    ObjectId = "aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb"
}

Set-AzSqlInstanceActiveDirectoryAdministrator @parameters

This example command removes the Microsoft Entra administrator for the SQL Managed Instance named "Sample-Instance" associated with the resource group "Example-Resource-Group".

$parameters = @{
    ResourceGroupName = "Example-Resource-Group"
    InstanceName = "Sample-Instance"
    Confirm = $true
    PassThru = $true
}

Remove-AzSqlInstanceActiveDirectoryAdministrator @parameters

You can set and manage a Microsoft Entra admin for the SQL Managed Instance by calling the following Azure CLI commands:

For more CLI commands, see az sql mi.

You can use the Managed Instance Administrators REST APIs to create, update, delete, and get the Microsoft Entra administrator for SQL managed instances.

The Microsoft Entra ID page for SQL Managed Instance in the Azure portal displays a convenient banner when the instance isn't assigned the Directory Reader permissions.

1. Select the banner on top of the Microsoft Entra ID page and grant permission to the system-assigned or user-assigned managed identity that represents your instance. Only a Privileged Role Administrator or higher role in your tenant can perform this operation.

   

2. When the operation succeeds, a Success notification shows in the top-right corner:

   

The following PowerShell script adds an identity to the Directory Readers role. This can be used to assign permissions to a managed instance or primary server identity for the logical server (or any Microsoft Entra identity).

# This script grants "Directory Readers" permission to a service principal representing a SQL Managed Instance or logical server.
# It can be executed only by a user who is a member of the **Privileged Roles Administrator** role.

Import-Module Microsoft.Graph.Authentication
$instanceName = "<InstanceName>"        # Enter the name of your managed instance or server
$tenantId = "<TenantId>"                       # Enter your tenant ID

Connect-MgGraph -TenantId $tenantId -Scopes "RoleManagement.ReadWrite.Directory"

# Get Microsoft Entra "Directory Readers" role and create if it doesn't exist
$roleName = "Directory Readers"
$role = Get-MgDirectoryRole -Filter "DisplayName eq '$roleName'"
if ($role -eq $null) {
    # Instantiate an instance of the role template
    $roleTemplate = Get-MgDirectoryRoleTemplate -Filter "DisplayName eq '$roleName'"
    New-MgDirectoryRoleTemplate -RoleTemplateId $roleTemplate.Id
    $role = Get-MgDirectoryRole -Filter "DisplayName eq '$roleName'"
}

# Get service principal for your SQL Managed Instance or logical server
$roleMember = Get-MgServicePrincipal -Filter "DisplayName eq '$instanceName'"
$roleMember.Count
if ($roleMember -eq $null) {
    Write-Output "Error: No service principal with name '$($instanceName)' found, make sure that instanceName parameter was entered correctly."
    exit
}
if (-not ($roleMember.Count -eq 1)) {
    Write-Output "Error: Multiple service principals with name '$($instanceName)'"
    Write-Output $roleMember | Format-List DisplayName, Id, AppId
    exit
}

# Check if service principal is already member of Directory Readers role
$isDirReader = Get-MgDirectoryRoleMember -DirectoryRoleId $role.Id -Filter "Id eq '$($roleMember.Id)'"
if ($isDirReader -eq $null) {
    # Add principal to Directory Readers role
    Write-Output "Adding service principal '$($instanceName)' to 'Directory Readers' role..."
    $body = @{
        "@odata.id"= "https://graph.microsoft.com/v1.0/directoryObjects/{$($roleMember.Id)}"
    }
    New-MgDirectoryRoleMemberByRef -DirectoryRoleId $role.Id -BodyParameter $body
    Write-Output "'$($instanceName)' service principal added to 'Directory Readers' role."
} else {
    Write-Output "Service principal '$($instanceName)' is already member of 'Directory Readers' role."
}

See details for how to assign Azure roles via the Azure CLI here Assign Azure roles using Azure CLI.

Assign Azure roles using the REST API.