Редагувати

Поділитися через

Microsoft Entra service principals with Azure SQL

  • Стаття

Applies to: Azure SQL Database Azure SQL Managed Instance

Azure SQL resources support programmatic access for applications using service principals and managed identities in Microsoft Entra ID (formerly Azure Active Directory).

Service principals (Microsoft Entra applications) support

This article applies to applications registered in Microsoft Entra ID. Using application credentials to access Azure SQL supports the security principle of Separation of Duties, enabling organizations to configure precise access for each application connecting to their databases. Managed identities, a special form of service principals, are recommended as they're passwordless and eliminate the need for developer-managed credentials.

Microsoft Entra ID further enables advanced authentication scenarios like OAuth 2.0 On-Behalf-Of Flow (OBO). OBO allows applications to request signed-in user credentials, for scenarios when applications themselves shouldn't be given database access without delegated permissions.

For more information on Microsoft Entra applications, see Application and service principal objects in Microsoft Entra ID and Create an Azure service principal with Azure PowerShell.

Microsoft Entra user creation using service principals

Supporting this functionality is useful in Microsoft Entra application automation processes where Microsoft Entra principals are created and maintained in SQL Database or SQL Managed Instance without human interaction. Service principals can be a Microsoft Entra admin for the SQL logical server or managed instance, as part of a group or as a standalone identity. The application can automate Microsoft Entra object creation in SQL Database or SQL Managed Instance, allowing full automation of database user creation.

Enable service principals to create Microsoft Entra users

When using applications to access Azure SQL, creating Microsoft Entra users and logins requires permissions that aren't assigned to service principals or managed identities by default: the ability to read users, groups, and applications in a tenant from Microsoft Graph. These permissions are necessary for the SQL engine to validate the identity specified in CREATE LOGIN or CREATE USER, and pull important information including the identity's Object or Application ID, which is used to create the login or user.

When a Microsoft Entra user executes these commands, Azure SQL's Microsoft application uses delegated permissions to impersonate the signed-in user and queries Microsoft Graph using their permissions. This flow isn't possible with service principals, because an application can't impersonate another application. Instead, the SQL engine tries to use its server identity, which is the primary managed identity assigned to a SQL managed instance, Azure SQL logical server, or Azure Synapse workspace. The server identity must exist and have the Microsoft Graph query permissions or the operations fail.

The following steps explain how to assign a managed identity to the server and assign it the Microsoft Graph permissions to enable service principals to create Microsoft Entra users and logins in the database.

  1. Assign the server identity. The server identity can be a system-assigned or user-assigned managed identity. For more information, see Managed identities in Microsoft Entra for Azure SQL.

    • The following PowerShell command creates a new logical server provisioned with a system-assigned managed identity:
    New-AzSqlServer -ResourceGroupName <resource group> -Location <Location name> -ServerName <Server name> -ServerVersion "12.0" -SqlAdministratorCredentials (Get-Credential) -AssignIdentity

    For more information, see the New-AzSqlServer command, or New-AzSqlInstance command for SQL Managed Instance.

    • For an existing logical server, execute the following command to add a system-assigned managed identity to it:
    Set-AzSqlServer -ResourceGroupName <resource group> -ServerName <Server name> -AssignIdentity

    For more information, see the Set-AzSqlServer command, or Set-AzSqlInstance command for SQL Managed Instance.

    Note

    The server identity can be assigned using REST API and CLI commands as well. For more information, see az sql server create, az sql server update, and Servers - REST API.

  2. Grant the server identity permissions to query Microsoft Graph. This can be done multiple ways: by adding the identity to the Microsoft Entra Directory Readers role, by assigning the identity the individual Microsoft Graph permissions, or by adding the identity to a role-assignable group that has the Directory Readers role:

    • Add server identity to a role-assignable group

      In production environments, it's recommended that a tenant administrator creates a role-assignable group and assigns the Directory Readers role to it. Group owners can then add server identities to the group, inheriting those permissions. This removes the requirement for a Global Administrator or Privileged Roles Administrator to grant permissions to each individual server identity, allowing administrators to delegate permission assignment to owners of the group for this scenario. For more information, see Directory Readers role in Microsoft Entra ID for Azure SQL.

    • Assign Microsoft Graph permissions to server identity

      To assign the individual Microsoft Graph permissions to the server identity, you must have the Microsoft Entra Global Administrator or Privileged Roles Administrator role. This is recommended over assigning the Directory Readers role, because there are permissions included in the role that the server identity doesn't need. Assigning only the individual Microsoft Graph read permissions limits the server identity's permissions within your tenant and maintains the principle of least privilege. For instructions, see Managed identities in Microsoft Entra for Azure SQL.

    • Add server identity to Directory Readers role

      To add the server identity to the Directory Readers role, you must be a member of the Microsoft Entra Global Administrator or Privileged Roles Administrator role. In production environments this option isn't recommended for two reasons: the Directory Reader role gives more permissions than the server identity requires, and the role assignment process still requires administrator approvals for each server identity (unlike using groups). Follow the SQL Managed Instance instructions available in the article Set Microsoft Entra admin (SQL Managed Instance).

Troubleshooting

When troubleshooting, you might encounter the following error:

Msg 33134, Level 16, State 1, Line 1
Principal 'test-user' could not be resolved.
Error message: 'Server identity is not configured. Please follow the steps in "Assign an Azure AD identity to your server and add Directory Reader permission to your identity" (https://aka.ms/sqlaadsetup)'

This error indicates that the server identity hasn't been created or hasn't been assigned Microsoft Graph permissions. Follow the steps to Assign an identity to the logical server and Assign Directory Readers permission to the logical server identity.

Limitations

  • Service principals can't authenticate across tenants' boundaries. Trying to access SQL Database or SQL Managed Instance using a Microsoft Entra application created in a different tenant fails.

  • Az.Sql 2.9.0 module or higher is required to set a Microsoft Entra application as the Microsoft Entra admin for Azure SQL. Ensure you're upgraded to the latest module.

Related content