使用 Azure SQL 的 Azure Active Directory 服务主体Azure Active Directory service principal with Azure SQL

适用于: 是Azure SQL 数据库是Azure SQL 托管实例是Azure Synapse Analytics (SQL DW) APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance yes Azure Synapse Analytics (SQL DW)

支持以 Azure AD 应用程序(服务主体)身份在 Azure SQL 数据库 (SQL DB) 和 Azure Synapse Analytics 中创建 Azure Active Directory (Azure AD) 用户的功能当前为公共预览版Support for Azure Active Directory (Azure AD) user creation in Azure SQL Database (SQL DB) and Azure Synapse Analytics on behalf of Azure AD applications (service principals) are currently in public preview.

备注

SQL 托管实例已支持此功能。This functionality is already supported for SQL Managed Instance.

服务主体(Azure AD 应用程序)支持Service principal (Azure AD applications) support

本文适用于集成了 Azure AD 并且属于 Azure AD 注册的应用程序。This article applies to applications that are integrated with Azure AD, and are part of Azure AD registration. 这些应用程序通常需要对 Azure SQL 进行身份验证和授权访问才能执行各种任务。These applications often need authentication and authorization access to Azure SQL to perform various tasks. 公共预览版中的此功能现在允许服务主体在 SQL 数据库和 Azure Synapse 中创建 Azure AD 用户。This feature in public preview now allows service principals to create Azure AD users in SQL Database and Azure Synapse. 有一个限制会阻止代表已删除的 Azure AD 应用程序创建 Azure AD 对象这一功能。There was a limitation preventing Azure AD object creation on behalf of Azure AD applications that was removed.

使用 Azure 门户或 PowerShell 命令注册 Azure AD 应用程序时,会在 Azure AD 租户中创建两个对象:When an Azure AD application is registered using the Azure portal or a PowerShell command, two objects are created in the Azure AD tenant:

  • 应用程序对象An application object
  • 和一个服务主体对象。A service principal object

有关 Azure AD 应用程序的详细信息,请参阅 Azure Active Directory 中的应用程序和服务主体对象使用 Azure PowerShell 创建 Azure 服务主体For more information on Azure AD applications, see Application and service principal objects in Azure Active Directory and Create an Azure service principal with Azure PowerShell.

SQL 数据库、Azure Synapse 和 SQL 托管实例支持以下 Azure AD 对象:SQL Database, Azure Synapse, and SQL Managed Instance support the following Azure AD objects:

  • Azure AD 用户(托管用户、联合用户和来宾用户)Azure AD users (managed, federated, and guest)
  • Azure AD 组(托管组和联合组)Azure AD groups (managed and federated)
  • Azure AD 应用程序Azure AD applications

SQL 数据库和 Azure Synapse 现在支持以 Azure AD 应用程序身份运行 T-SQL 命令 CREATE USER [Azure_AD_Object] FROM EXTERNAL PROVIDERThe T-SQL command CREATE USER [Azure_AD_Object] FROM EXTERNAL PROVIDER on behalf of an Azure AD application is now supported for SQL Database and Azure Synapse.

使用服务主体创建 Azure AD 用户的功能Functionality of Azure AD user creation using service principals

在无需人工交互的 Azure AD 应用程序自动化流程(其中的 Azure AD 对象是在 SQL 数据库和 Azure Synapse 中创建和维护的)中,支持此功能非常有用。Supporting this functionality is useful in Azure AD application automation processes where Azure AD objects are created and maintained in SQL Database and Azure Synapse without human interaction. 服务主体可以是 SQL 逻辑服务器的 Azure AD 管理员(属于组成员或属于单个用户)。Service principals can be an Azure AD admin for the SQL logical server, as part of a group or an individual user. 当以系统管理员身份执行时,应用程序可以在 SQL 数据库和 Azure Synapse 中自动创建 Azure AD 对象,并且不需要任何其他 SQL 权限。The application can automate Azure AD object creation in SQL Database and Azure Synapse when executed as a system administrator, and does not require any additional SQL privileges. 这可以实现完全自动化的数据库用户创建操作。This allows for a full automation of a database user creation. 系统分配的托管标识和用户分配的托管标识也支持此功能。This feature is also supported for system-assigned managed identity and user-assigned managed identity. 有关详细信息,请参阅什么是 Azure 资源的托管标识?For more information, see What are managed identities for Azure resources?

使服务主体能够创建 Azure AD 用户Enable service principals to create Azure AD users

为了能够以 Azure AD 应用程序身份在 SQL 数据库和 Azure Synapse 中创建 Azure AD 对象,需要进行以下设置:To enable an Azure AD object creation in SQL Database and Azure Synapse on behalf of an Azure AD application, the following settings are required:

  1. 分配服务器标识Assign the server identity

    • 对于新的 Azure SQL 逻辑服务器,请执行以下 PowerShell 命令:For a new Azure SQL logical server, execute the following PowerShell command:
    New-AzSqlServer -ResourceGroupName <resource group> -Location <Location name> -ServerName <Server name> -ServerVersion "12.0" -SqlAdministratorCredentials (Get-Credential) -AssignIdentity
    

    有关详细信息,请参阅 New-AzSqlServer 命令。For more information, see the New-AzSqlServer command.

    • 对于现有的 Azure SQL 逻辑服务器,请执行以下命令:For existing Azure SQL Logical servers, execute the following command:
    Set-AzSqlServer -ResourceGroupName <resource group> -ServerName <Server name> -AssignIdentity
    

    有关详细信息,请参阅 Set-AzSqlServer 命令。For more information, see the Set-AzSqlServer command.

    • 若要检查是否为服务器分配了服务器标识,请执行 Get-AzSqlServer 命令。To check if the server identity is assigned to the server, execute the Get-AzSqlServer command.

    备注

    也可以使用 CLI 命令分配服务器标识。Server identity can be assigned using CLI commands as well. 有关详细信息,请参阅 az sql server createaz sql server updateFor more information, see az sql server create and az sql server update.

  2. 向已创建的或分配给服务器的服务器标识授予 Azure AD 目录读取者权限。Grant the Azure AD Directory Readers permission to the server identity created or assigned to the server.

    • 若要授予此权限,请按照以下文章中提供的用于 SQL 托管实例的说明进行操作:预配 Azure AD 管理员(SQL 托管实例)To grant this permission, follow the description used for SQL Managed Instance that is available in the following article: Provision Azure AD admin (SQL Managed Instance)
    • 授予此权限的 Azure AD 用户必须担任 Azure AD 全局管理员特权角色管理员角色。The Azure AD user who is granting this permission must be part of the Azure AD Global Administrator or Privileged Roles Administrator role.

重要

必须按上述顺序执行步骤 1 和步骤 2。Steps 1 and 2 must be executed in the above order. 首先,创建或分配服务器标识,然后授予目录读取者权限。First, create or assign the server identity, followed by granting the Directory Readers permission. 省略这两个步骤中的一个或同时忽略这两个都会导致以 Azure AD 应用程序身份在 Azure SQL 中创建 Azure AD 对象时发生执行错误。Omitting one of these steps, or both will cause an execution error during an Azure AD object creation in Azure SQL on behalf of an Azure AD application. 有关以 Azure AD 应用程序身份创建 Azure AD 用户的分步说明,请参阅教程:使用 Azure AD 应用程序创建 Azure AD 用户For step by step instructions to create an Azure AD user on behalf of an Azure AD application, see Tutorial: Create Azure AD users using Azure AD applications.

公共预览版的故障排除和限制Troubleshooting and limitations for public preview

  • 当以 Azure AD 应用程序身份在 Azure SQL 中创建 Azure AD 对象时,如果不启用服务器标识并授予目录读取者权限,则操作会失败,并出现以下可能的错误。When creating Azure AD objects in Azure SQL on behalf of an Azure AD application without enabling server identity and granting Directory Readers permission, the operation will fail with the following possible errors. 下面的示例错误针对以下文章中介绍的用于创建 SQL 数据库用户 myapp 的 PowerShell 命令执行过程:教程:使用 Azure AD 应用程序创建 Azure AD 用户The example error below is for a PowerShell command execution to create a SQL Database user myapp in the article Tutorial: Create Azure AD users using Azure AD applications.

    备注

    上述错误消息会在此功能正式发布之前进行更改,以明确指出要支持 Azure AD 应用程序还需要满足哪些设置要求。The error messages indicated above will be changed before the feature GA to clearly identify the missing setup requirement for Azure AD application support.

  • 仅支持以 SQL 托管实例的 Azure AD 管理员身份使用 CLI 命令以及 Az.Sql 版本为 2.9.0 或更高的 PowerShell 命令设置 Azure AD 应用程序。Setting the Azure AD application as an Azure AD admin for SQL Managed Instance is only supported using the CLI command, and PowerShell command with Az.Sql 2.9.0 or higher. 有关详细信息,请参阅 az sql mi ad-admin createSet-AzSqlInstanceActiveDirectoryAdministrator 命令。For more information, see the az sql mi ad-admin create and Set-AzSqlInstanceActiveDirectoryAdministrator commands.
    • 如果要使用 SQL 托管实例的 Azure 门户来设置 Azure AD 管理员,一种可能的解决方法是创建一个 Azure AD 组。If you want to use the Azure portal for SQL Managed Instance to set the Azure AD admin, a possible workaround is to create an Azure AD group. 然后,将服务主体(Azure AD 应用程序)添加到此组,并将此组设置为 SQL 托管实例的 Azure AD 管理员。Then add the service principal (Azure AD application) to this group, and set this group as an Azure AD admin for the SQL Managed Instance.
    • 支持使用 Azure 门户、PowerShellCLI 命令将服务主体(Azure AD 应用程序)设置为 SQL 数据库和 Azure Synapse 的 Azure AD 管理员。Setting the service principal (Azure AD application) as an Azure AD admin for SQL Database and Azure Synapse is supported using the Azure portal, PowerShell, and CLI commands.
  • 访问在不同租户中创建的 SQL 数据库或 SQL 托管实例时,无法将 Azure AD 应用程序与另一个 Azure AD 租户中的服务主体结合使用。Using an Azure AD application with service principal from another Azure AD tenant will fail when accessing SQL Database or SQL Managed Instance created in a different tenant. 分配给此应用程序的服务主体必须与 SQL 逻辑服务器或托管实例位于同一租户中。A service principal assigned to this application must be from the same tenant as the SQL logical server or Managed Instance.
  • 使用 PowerShell 将单个 Azure AD 应用程序设置为 Azure SQL 的 Azure AD 管理员时,需要使用 Az.Sql 2.9.0 模块或更高版本。Az.Sql 2.9.0 module or higher is needed when using PowerShell to set up an individual Azure AD application as Azure AD admin for Azure SQL. 确保已升级到最新模块。Ensure you are upgraded to the latest module.

后续步骤Next steps