使用 Azure SQL 配置和管理 Azure AD 身份验证Configure and manage Azure AD authentication 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 Active Directory (Azure AD) 实例,然后将 Azure AD 与 Azure SQL 数据库Azure SQL 托管实例Azure Synapse Analytics(以前称为 Azure SQL 数据仓库)配合使用。This article shows you how to create and populate an Azure Active Directory (Azure AD) instance, and then use Azure AD with Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics (formerly Azure SQL Data Warehouse). 有关概述,请参阅 Azure Active Directory 身份验证For an overview, see Azure Active Directory authentication.

Azure AD 身份验证方法Azure AD authentication methods

Azure AD 身份验证支持以下身份验证方法:Azure AD authentication supports the following authentication methods:

创建并填充 Azure AD 实例Create and populate an Azure AD instance

创建 Azure AD 实例并对其填充用户和组。Create an Azure AD instance and populate it with users and groups. Azure AD 可以是初始 Azure AD 托管域。Azure AD can be the initial Azure AD managed domain. Azure AD 也可以是本地 Active Directory 域服务,该服务可以与 Azure AD 联合。Azure AD can also be an on-premises Active Directory Domain Services that is federated with the Azure AD.

有关详细信息,请参阅将本地标识与 Azure Active Directory 集成将自己的域名添加到 Azure ADAzure 现在支持与 Windows Server Active Directory 联合管理 Azure AD 目录使用 Windows PowerShell 管理 Azure AD混合标识所需端口和协议For more information, see Integrating your on-premises identities with Azure Active Directory, Add your own domain name to Azure AD, Azure now supports federation with Windows Server Active Directory, Administering your Azure AD directory, Manage Azure AD using Windows PowerShell, and Hybrid Identity Required Ports and Protocols.

将 Azure 订阅关联或添加到 Azure Active DirectoryAssociate or add an Azure subscription to Azure Active Directory

  1. 通过将目录设为托管数据库的 Azure 订阅的一个受信任目录,将 Azure 订阅关联到 Azure Active Directory。Associate your Azure subscription to Azure Active Directory by making the directory a trusted directory for the Azure subscription hosting the database. 有关详细信息,请参阅将 Azure 订阅关联或添加到 Azure Active Directory 租户For details, see Associate or add an Azure subscription to your Azure Active Directory tenant.

  2. 在 Azure 门户中使用目录切换器切换到与域关联的订阅。Use the directory switcher in the Azure portal to switch to the subscription associated with domain.

    重要

    每个 Azure 订阅都与某个 Azure AD 实例存在信任关系。Every Azure subscription has a trust relationship with an Azure AD instance. 这意味着,此订阅信任该目录对用户、服务和设备执行身份验证。This means that it trusts that directory to authenticate users, services, and devices. 多个订阅可以信任同一个目录,但一个订阅只能信任一个目录。Multiple subscriptions can trust the same directory, but a subscription trusts only one directory. 订阅与目录之间的这种信任关系不同于订阅与 Azure 中所有其他资源(网站、数据库等)之间的关系,在后一种关系中,这些资源更像是订阅的子资源。This trust relationship that a subscription has with a directory is unlike the relationship that a subscription has with all other resources in Azure (websites, databases, and so on), which are more like child resources of a subscription. 如果某个订阅过期,则对该订阅关联的其他那些资源的访问权限也会终止。If a subscription expires, then access to those other resources associated with the subscription also stops. 但是,目录将保留在 Azure 中,并且用户可以将另一个订阅与该目录相关联,并继续管理目录用户。But the directory remains in Azure, and you can associate another subscription with that directory and continue to manage the directory users. 有关资源的详细信息,请参阅了解 Azure 中的资源访问For more information about resources, see Understanding resource access in Azure. 若要详细了解此受信任关系,请参阅如何将 Azure 订阅关联或添加到 Azure Active DirectoryTo learn more about this trusted relationship see How to associate or add an Azure subscription to Azure Active Directory.

SQL 数据库中的服务器的 Azure AD 管理员Azure AD admin with a server in SQL Database

Azure 中的每个服务器(托管 SQL 数据库或 Azure Synapse)开始时只使用单个服务器管理员帐户,即整个服务器的管理员。Each server in Azure (which hosts SQL Database or Azure Synapse) starts with a single server administrator account that is the administrator of the entire server. 创建另一个管理员帐户作为 Azure AD 帐户。Create a second administrator account as an Azure AD account. 此主体在服务器的 master 数据库中作为包含的数据库用户创建。This principal is created as a contained database user in the master database of the server. 管理员帐户是每个用户数据库中 db_owner 角色的成员,并且以 dbo 用户身份输入每个用户数据库 。Administrator accounts are members of the db_owner role in every user database, and enter each user database as the dbo user. 有关管理员帐户的详细信息,请参阅管理数据库和登录名For more information about administrator accounts, see Managing Databases and Logins.

将 Azure Active Directory 与异地复制结合使用时,必须为主服务器和辅助服务器配置 Azure Active Directory 管理员。When using Azure Active Directory with geo-replication, the Azure Active Directory administrator must be configured for both the primary and the secondary servers. 如果服务器没有 Azure Active Directory 管理员,则 Azure Active Directory 登录名和用户会收到“Cannot connect 到服务器”错误。If a server does not have an Azure Active Directory administrator, then Azure Active Directory logins and users receive a Cannot connect to server error.

备注

如果用户使用的不是基于 Azure AD 的帐户(包括服务器管理员帐户),则无法创建基于 Azure AD 的用户,这是因为他们无权使用 Azure AD 验证建议的数据库用户。Users that are not based on an Azure AD account (including the server administrator account) cannot create Azure AD-based users, because they do not have permission to validate proposed database users with the Azure AD.

预配 Azure AD 管理员(SQL 托管实例)Provision Azure AD admin (SQL Managed Instance)

重要

若要预配 Azure SQL 托管实例,则只执行以下步骤。Only follow these steps if you are provisioning an Azure SQL Managed Instance. 在 Azure AD 中,只能由全局/公司管理员或特权角色管理员执行此操作。This operation can only be executed by Global/Company administrator or a Privileged Role Administrator in Azure AD.

SQL 托管实例需要权限来读取 Azure AD,以成功完成通过安全组成员资格验证用户身份或创建新用户等任务。Your SQL Managed Instance needs permissions to read Azure AD to successfully accomplish tasks such as authentication of users through security group membership or creation of new users. 为此,需要授予 SQL 托管实例读取 Azure AD 的权限。For this to work, you need to grant the SQL Managed Instance permission to read Azure AD. 可以使用 Azure 门户或 PowerShell 执行此操作。You can do this using the Azure portal or PowerShell.

Azure 门户Azure portal

若要使用 Azure 门户授予 SQL 托管实例 Azure AD 读取权限,请在 Azure AD 中以全局/公司管理员身份登录,然后执行以下步骤:To grant your SQL Managed Instance Azure AD read permission using the Azure portal, log in as Global/Company administrator in Azure AD and follow these steps:

  1. Azure 门户右上角,从可能的 Active Directory 下拉列表中选择你的连接。In the Azure portal, in the upper-right corner, select your connection from a drop-down list of possible Active Directories.

  2. 选择正确的 Active Directory 作为默认的 Azure AD。Choose the correct Active Directory as the default Azure AD.

    此步骤将与 Active Directory 关联的订阅链接到 SQL 托管实例,确保为 Azure AD 实例和 SQL 托管实例使用相同的订阅。This step links the subscription associated with Active Directory to the SQL Managed Instance, making sure that the same subscription is used for both the Azure AD instance and the SQL Managed Instance.

  3. 导航到要用于 Azure AD 集成的 SQL 托管实例。Navigate to the SQL Managed Instance you want to use for Azure AD integration.

    aad

  4. 选择“Active Directory 管理员”页顶部的横幅,并为当前用户授予权限。Select the banner on top of the Active Directory admin page and grant permission to the current user.

    授予权限 - 门户

  5. 成功完成操作后,右上角会显示以下通知:After the operation succeeds, the following notification will show up in the top-right corner:

    success

  6. 现在即可为 SQL 托管实例选择 Azure AD 管理员。Now you can choose your Azure AD admin for your SQL Managed Instance. 为此,请在“Active Directory 管理员”页上选择“设置管理员”命令。For that, on the Active Directory admin page, select Set admin command.

    设置管理员

  7. 在“Azure AD 管理员”页中,搜索某位用户,选择该用户或组作为管理员,然后选择“选择”。On the Azure AD admin page, search for a user, select the user or group to be an administrator, and then select Select.

    “Active Directory 管理员”页会显示 Active Directory 的所有成员和组。The Active Directory admin page shows all members and groups of your Active Directory. 若用户或组为灰显,则无法选择,因为不支持它们作为 Azure AD 管理员。Users or groups that are grayed out can't be selected because they aren't supported as Azure AD administrators. 请参阅 Azure AD 功能和限制中受支持的管理员列表。See the list of supported admins in Azure AD Features and Limitations. 基于角色的访问控制 (RBAC) 仅适用于 Azure 门户,不会传播到 SQL 数据库、SQL 托管实例或 Azure Synapse。Role-based access control (RBAC) applies only to the Azure portal and isn't propagated to SQL Database, SQL Managed Instance, or Azure Synapse.

    添加 Azure Active Directory 管理员

  8. 在“Active Directory 管理员”页顶部,选择“保存”。At the top of the Active Directory admin page, select Save.

    保存

    更改管理员的过程可能需要几分钟时间。The process of changing the administrator may take several minutes. 然后,新管理员将出现在“Active Directory 管理员”框中。Then the new administrator appears in the Active Directory admin box.

在为 SQL 托管实例预配 Azure AD 管理员之后,即可开始使用 CREATE LOGIN 语法创建 Azure AD 服务器主体(登录名)。After provisioning an Azure AD admin for your SQL Managed Instance, you can begin to create Azure AD server principals (logins) with the CREATE LOGIN syntax. 有关详细信息,请参阅 SQL 托管实例概述For more information, see SQL Managed Instance overview.

提示

之后如需删除管理员,请在“Active Directory 管理员”页顶部,选择“删除管理员”,然后选择“保存”。To later remove an Admin, at the top of the Active Directory admin page, select Remove admin, and then select Save.

PowerShellPowerShell

若要使用 PowerShell 向 SQL 托管实例授予 Azure AD 读取权限,请运行以下脚本:To grant your SQL Managed Instance Azure AD read permission by using the PowerShell, run this script:

# Gives Azure Active Directory read permission to a Service Principal representing the SQL Managed Instance.
# Can be executed only by a "Company Administrator", "Global Administrator", or "Privileged Role Administrator" type of user.

$aadTenant = "<YourTenantId>" # Enter your tenant ID
$managedInstanceName = "MyManagedInstance"

# Get Azure AD role "Directory Users" and create if it doesn't exist
$roleName = "Directory Readers"
$role = Get-AzureADDirectoryRole | Where-Object {$_.displayName -eq $roleName}
if ($role -eq $null) {
    # Instantiate an instance of the role template
    $roleTemplate = Get-AzureADDirectoryRoleTemplate | Where-Object {$_.displayName -eq $roleName}
    Enable-AzureADDirectoryRole -RoleTemplateId $roleTemplate.ObjectId
    $role = Get-AzureADDirectoryRole | Where-Object {$_.displayName -eq $roleName}
}

# Get service principal for your SQL Managed Instance
$roleMember = Get-AzureADServicePrincipal -SearchString $managedInstanceName
$roleMember.Count
if ($roleMember -eq $null) {
    Write-Output "Error: No Service Principals with name '$    ($managedInstanceName)', make sure that managedInstanceName parameter was     entered correctly."
    exit
}
if (-not ($roleMember.Count -eq 1)) {
    Write-Output "Error: More than one service principal with name pattern '$    ($managedInstanceName)'"
    Write-Output "Dumping selected service principals...."
    $roleMember
    exit
}

# Check if service principal is already member of readers role
$allDirReaders = Get-AzureADDirectoryRoleMember -ObjectId $role.ObjectId
$selDirReader = $allDirReaders | where{$_.ObjectId -match     $roleMember.ObjectId}

if ($selDirReader -eq $null) {
    # Add principal to readers role
    Write-Output "Adding service principal '$($managedInstanceName)' to     'Directory Readers' role'..."
    Add-AzureADDirectoryRoleMember -ObjectId $role.ObjectId -RefObjectId     $roleMember.ObjectId
    Write-Output "'$($managedInstanceName)' service principal added to     'Directory Readers' role'..."

    #Write-Output "Dumping service principal '$($managedInstanceName)':"
    #$allDirReaders = Get-AzureADDirectoryRoleMember -ObjectId $role.ObjectId
    #$allDirReaders | where{$_.ObjectId -match $roleMember.ObjectId}
}
else {
    Write-Output "Service principal '$($managedInstanceName)' is already     member of 'Directory Readers' role'."
}

用于 SQL 托管实例的 PowerShellPowerShell for SQL Managed Instance

若要运行 PowerShell cmdlet,需要已安装并运行 Azure PowerShell。To run PowerShell cmdlets, you need to have Azure PowerShell installed and running. 有关详细信息,请参阅 如何安装和配置 Azure PowerShellFor detailed information, see How to install and configure Azure PowerShell.

重要

PowerShell Azure 资源管理器 (RM) 模块仍受 Azure SQL 托管实例的支持,但所有未来的开发都是针对 Az.Sql 模块的。The PowerShell Azure Resource Manager (RM) module is still supported by Azure SQL Managed Instance, but all future development is for the Az.Sql module. AzureRM 模块至少在 2020 年 12 月之前将继续接收 bug 修补程序。The AzureRM module will continue to receive bug fixes until at least December 2020. Az 模块和 AzureRm 模块中的命令参数大体上是相同的。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical. 若要详细了解其兼容性,请参阅新 Azure PowerShell Az 模块简介For more about their compatibility, see Introducing the new Azure PowerShell Az module.

若要预配 Azure AD 管理员,请执行以下 Azure PowerShell 命令:To provision an Azure AD admin, execute the following Azure PowerShell commands:

  • Connect-AzAccount -Environment AzureChinaCloudConnect-AzAccount -Environment AzureChinaCloud
  • Select-AzSubscriptionSelect-AzSubscription

下表列出了用于预配和管理 SQL 托管实例的 Azure AD 管理员的 cmdlet:The cmdlets used to provision and manage Azure AD admin for your SQL Managed Instance are listed in the following table:

Cmdlet 名称Cmdlet name 说明Description
Set-AzSqlInstanceActiveDirectoryAdministratorSet-AzSqlInstanceActiveDirectoryAdministrator 预配当前订阅中 SQL 托管实例的 Azure AD 管理员。Provisions an Azure AD administrator for the SQL Managed Instance in the current subscription. (必须来自当前订阅)(Must be from the current subscription)
Remove-AzSqlInstanceActiveDirectoryAdministratorRemove-AzSqlInstanceActiveDirectoryAdministrator 删除当前订阅中 SQL 托管实例的 Azure AD 管理员。Removes an Azure AD administrator for the SQL Managed Instance in the current subscription.
Get-AzSqlInstanceActiveDirectoryAdministratorGet-AzSqlInstanceActiveDirectoryAdministrator 返回当前订阅中 SQL 托管实例的 Azure AD 管理员的相关信息。Returns information about an Azure AD administrator for the SQL Managed Instance in the current subscription.

以下命令获取名为 ManagedInstance01 的 SQL 托管实例的 Azure AD 管理员的相关信息,该实例与名为 ResourceGroup01 的资源组相关联。The following command gets information about an Azure AD administrator for a SQL Managed Instance named ManagedInstance01 that is associated with a resource group named ResourceGroup01.

Get-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName "ResourceGroup01" -InstanceName "ManagedInstance01"

以下命令为名为 ManagedInstance01 的 SQL 托管实例预配名为 DBA 的 Azure AD 管理员组。The following command provisions an Azure AD administrator group named DBAs for the SQL Managed Instance named ManagedInstance01. 此服务器与资源组 ResourceGroup01 相关联。This server is associated with resource group ResourceGroup01.

Set-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName "ResourceGroup01" -InstanceName "ManagedInstance01" -DisplayName "DBAs" -ObjectId "40b79501-b343-44ed-9ce7-da4c8cc7353b"

以下命令删除名为 ManagedInstanceName01 的 SQL 托管实例的 Azure AD 管理员,该实例与资源组 ResourceGroup01 相关联。The following command removes the Azure AD administrator for the SQL Managed Instance named ManagedInstanceName01 associated with the resource group ResourceGroup01.

Remove-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName "ResourceGroup01" -InstanceName "ManagedInstanceName01" -Confirm -PassThru

预配 Azure AD 管理员(SQL 数据库)Provision Azure AD admin (SQL Database)

重要

只有在预配 SQL 数据库或 Azure Synapse 的服务器的情况下,才执行以下步骤。Only follow these steps if you are provisioning a server for SQL Database or Azure Synapse.

以下两个过程说明如何使用 PowerShell 在 Azure 门户中为服务器预配 Azure Active Directory 管理员。The following two procedures show you how to provision an Azure Active Directory administrator for your server in the Azure portal and by using PowerShell.

Azure 门户Azure portal

  1. Azure 门户右上角,选择你的连接,以下拉可能的 Active Directory 列表。In the Azure portal, in the upper-right corner, select your connection to drop down a list of possible Active Directories. 选择正确的 Active Directory 作为默认的 Azure AD。Choose the correct Active Directory as the default Azure AD. 此步骤将与订阅关联的 Active Directory 链接到服务器,确保为 Azure AD 和服务器使用相同的订阅。This step links the subscription-associated Active Directory with server making sure that the same subscription is used for both Azure AD and the server.

  2. 搜索并选择“SQL 服务器”。Search for and select SQL server.

    搜索并选择“SQL 服务器”

    备注

    在此页面上,在选择“SQL 服务器”前,可选择名称旁边的星号标记以收藏该类别,并将“SQL 服务器”添加到左侧导航栏。On this page, before you select SQL servers, you can select the star next to the name to favorite the category and add SQL servers to the left navigation bar.

  3. 在“SQL Server”页上,选择“Active Directory 管理员”。On the SQL Server page, select Active Directory admin.

  4. 在“Active Directory 管理员”页中,选择“设置管理员”。In the Active Directory admin page, select Set admin.

    SQL Server 的“设置 Active Directory 管理员”

  5. 在“添加管理员”页中,搜索某位用户,选择该用户或组作为管理员,然后选择“选择”。In the Add admin page, search for a user, select the user or group to be an administrator, and then select Select. (“Active Directory 管理员”页会显示 Active Directory 的所有成员和组。(The Active Directory admin page shows all members and groups of your Active Directory. 若用户或组为灰显,则无法选择,因为不支持它们作为 Azure AD 管理员。Users or groups that are grayed out cannot be selected because they are not supported as Azure AD administrators. (请参阅将 Azure Active Directory 身份验证与使用 SQL 数据库或 Azure Synapse 进行身份验证结合使用的“Azure AD 功能和限制”部分中支持的管理员列表。)基于角色的访问控制 (RBAC) 仅适用于该门户,不会传播到 SQL Server。(See the list of supported admins in the Azure AD Features and Limitations section of Use Azure Active Directory Authentication for authentication with SQL Database or Azure Synapse.) Role-based access control (RBAC) applies only to the portal and is not propagated to SQL Server.

    选择 Azure Active Directory 管理员

  6. 在“Active Directory 管理员”页顶部,选择“保存”。At the top of the Active Directory admin page, select SAVE.

    保存管理员

更改管理员的过程可能需要几分钟时间。The process of changing the administrator may take several minutes. 然后,新管理员出现在“Active Directory 管理员” 框中。Then the new administrator appears in the Active Directory admin box.

备注

设置 Azure AD 管理员时,此新的管理员名称(用户或组)不能已作为服务器身份验证用户存在于虚拟 master 数据库中。When setting up the Azure AD admin, the new admin name (user or group) cannot already be present in the virtual master database as a server authentication user. 如果存在,Azure AD 管理员设置将失败;将回滚其创建,并指示此管理员(名称)已存在。If present, the Azure AD admin setup will fail; rolling back its creation and indicating that such an admin (name) already exists. 由于这种服务器身份验证用户不是 Azure AD 的一部分,因此使用 Azure AD 身份验证连接到服务器的任何尝试都会失败。Since such a server authentication user is not part of the Azure AD, any effort to connect to the server using Azure AD authentication fails.

之后如需删除管理员,请在“Active Directory 管理员”页顶部,选择“删除管理员”,然后选择“保存”。To later remove an Admin, at the top of the Active Directory admin page, select Remove admin, and then select Save.

用于 SQL 数据库和 Azure Synapse 的 PowerShellPowerShell for SQL Database and Azure Synapse

若要运行 PowerShell cmdlet,需要已安装并运行 Azure PowerShell。To run PowerShell cmdlets, you need to have Azure PowerShell installed and running. 有关详细信息,请参阅 如何安装和配置 Azure PowerShellFor detailed information, see How to install and configure Azure PowerShell. 若要预配 Azure AD 管理员,请执行以下 Azure PowerShell 命令:To provision an Azure AD admin, execute the following Azure PowerShell commands:

  • Connect-AzAccount -Environment AzureChinaCloudConnect-AzAccount -Environment AzureChinaCloud
  • Select-AzSubscriptionSelect-AzSubscription

Cmdlet,用于预配和管理 SQL 数据库和 Azure Synapse 的 Azure AD 管理员:Cmdlets used to provision and manage Azure AD admin for SQL Database and Azure Synapse:

Cmdlet 名称Cmdlet name 说明Description
Set-AzSqlServerActiveDirectoryAdministratorSet-AzSqlServerActiveDirectoryAdministrator 为托管 SQL 数据库或 Azure Synapse 的服务器预配 Azure Active Directory 管理员。Provisions an Azure Active Directory administrator for the server hosting SQL Database or Azure Synapse. (必须来自当前订阅)(Must be from the current subscription)
Remove-AzSqlServerActiveDirectoryAdministratorRemove-AzSqlServerActiveDirectoryAdministrator 为托管 SQL 数据库或 Azure Synapse 的服务器删除 Azure Active Directory 管理员。Removes an Azure Active Directory administrator for the server hosting SQL Database or Azure Synapse.
Get-AzSqlServerActiveDirectoryAdministratorGet-AzSqlServerActiveDirectoryAdministrator 返回有关当前为托管 SQL 数据库或 Azure Synapse 的服务器配置的 Azure Active Directory 管理员的信息。Returns information about an Azure Active Directory administrator currently configured for the server hosting SQL Database or Azure Synapse.

使用 PowerShell 命令 get-help 查看其中每个命令的详细信息。Use PowerShell command get-help to see more information for each of these commands. 例如,get-help Set-AzSqlServerActiveDirectoryAdministratorFor example, get-help Set-AzSqlServerActiveDirectoryAdministrator.

以下脚本为名为 Group-23 的资源组中的 demo_server 服务器预配名为 DBA_Group 的 Azure AD 管理员组(对象 ID 40b79501-b343-44ed-9ce7-da4c8cc7353f) :The following script provisions an Azure AD administrator group named DBA_Group (object ID 40b79501-b343-44ed-9ce7-da4c8cc7353f) for the demo_server server in a resource group named Group-23:

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server" -DisplayName "DBA_Group"

DisplayName 输入参数接受 Azure AD 显示名称或用户主体名称。The DisplayName input parameter accepts either the Azure AD display name or the User Principal Name. 例如,DisplayName="John Smith"DisplayName="johns@contoso.com"For example, DisplayName="John Smith" and DisplayName="johns@contoso.com". 对于 Azure AD 组,只支持 Azure AD 显示名称。For Azure AD groups only the Azure AD display name is supported.

备注

Azure PowerShell 命令 Set-AzSqlServerActiveDirectoryAdministrator 不会阻止你为不受支持的用户预配 Azure AD 管理员。The Azure PowerShell command Set-AzSqlServerActiveDirectoryAdministrator does not prevent you from provisioning Azure AD admins for unsupported users. 可以预配不受支持的用户,但其无法连接到数据库。An unsupported user can be provisioned, but can not connect to a database.

以下示例使用可选的 ObjectIDThe following example uses the optional ObjectID:

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server" `
    -DisplayName "DBA_Group" -ObjectId "40b79501-b343-44ed-9ce7-da4c8cc7353f"

备注

DisplayName 不唯一时,需要使用 Azure AD ObjectIDThe Azure AD ObjectID is required when the DisplayName is not unique. 若要检索 ObjectIDDisplayName 值,请使用 Azure 经典门户的 Active Directory 部分,并查看用户或组的属性。To retrieve the ObjectID and DisplayName values, use the Active Directory section of Azure Classic Portal, and view the properties of a user or group.

以下示例返回有关服务器的当前 Azure AD 管理员的信息:The following example returns information about the current Azure AD admin for the server:

Get-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server" | Format-List

以下示例删除一个 Azure AD 管理员:The following example removes an Azure AD administrator:

Remove-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server"

备注

也可以使用 REST API 预配 Azure Active Directory 管理员。You can also provision an Azure Active Directory Administrator by using the REST APIs. 有关详细信息,请参阅 Azure SQL 数据库的 Azure SQL 数据库操作的 Service Management REST API 参考和操作For more information, see Service Management REST API Reference and Operations for Azure SQL Database Operations for Azure SQL Database

配置客户端计算机Configure your client computers

在应用程序或用户要使用 Azure AD 标识连接到 SQL 数据库或 Azure Synapse 的所有客户端计算机上,必须安装以下软件:On all client machines, from which your applications or users connect to SQL Database or Azure Synapse using Azure AD identities, you must install the following software:

可以通过以下操作来满足这些要求:You can meet these requirements by:

创建映射到 Azure AD 标识的包含的用户Create contained users mapped to Azure AD identities

由于 SQL 托管实例支持 Azure AD 服务器主体(登录名),因此不需要使用包含的数据库用户。Because SQL Managed Instance supports Azure AD server principals (logins), using contained database users is not required. 通过 Azure AD 服务器主体(登录名)可以从 Azure AD 用户、组或应用程序创建登录名。Azure AD server principals (logins) enable you to create logins from Azure AD users, groups, or applications. 这意味着可以通过使用 Azure AD 服务器登录名(而不是包含的数据库用户)对 SQL 托管实例进行身份验证。This means that you can authenticate with your SQL Managed Instance by using the Azure AD server login rather than a contained database user. 有关详细信息,请参阅 SQL 托管实例概述For more information, see SQL Managed Instance overview. 有关创建 Azure AD 服务器主体(登录名)的语法,请参阅 CREATE LOGINFor syntax on creating Azure AD server principals (logins), see CREATE LOGIN.

但是,将 Azure Active Directory 身份验证与 SQL 数据库和 Azure Synapse 结合使用时,需要使用基于 Azure AD 标识的包含的数据库用户。However, using Azure Active Directory authentication with SQL Database and Azure Synapse requires using contained database users based on an Azure AD identity. 包含的数据库用户在 master 数据库中没有登录名,且映射到与数据库关联的 Azure AD 目录中的标识。A contained database user does not have a login in the master database, and maps to an identity in Azure AD that is associated with the database. Azure AD 标识可以是单独的用户帐户,也可以是组。The Azure AD identity can be either an individual user account or a group. 有关包含的数据库用户的详细信息,请参阅包含的数据库用户 - 使你的数据库可移植For more information about contained database users, see Contained Database Users- Making Your Database Portable.

备注

不能使用 Azure 门户创建数据库用户(管理员除外)。Database users (with the exception of administrators) cannot be created using the Azure portal. Azure 角色不会传播到 SQL 数据库中的数据库、SQL 托管实例或 Azure Synapse。Azure roles are not propagated to the database in SQL Database, the SQL Managed Instance, or Azure Synapse. Azure 角色用于管理 Azure 资源,不适用于数据库权限。Azure roles are used for managing Azure Resources, and do not apply to database permissions. 例如,“SQL Server 参与者”角色不会授予连接到 SQL 数据库、SQL 托管实例或 Azure Synapse 中的数据库的访问权限。For example, the SQL Server Contributor role does not grant access to connect to the database in SQL Database, the SQL Managed Instance, or Azure Synapse. 必须使用 Transact-SQL 语句直接在数据库中授予访问权限。The access permission must be granted directly in the database using Transact-SQL statements.

警告

不支持在 T-SQL 的 CREATE LOGINCREATE USER 语句中将特殊字符(例如冒号 : 或与号 &)用作用户名的一部分。Special characters like colon : or ampersand & when included as user names in the T-SQL CREATE LOGIN and CREATE USER statements are not supported.

若要创建基于 Azure AD 的包含的数据库用户(而不是拥有数据库的服务器管理员),请以至少具有 ALTER ANY USER 权限的用户身份使用 Azure AD 标识连接到数据库。To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. 然后,使用以下 Transact-SQL 语法:Then use the following Transact-SQL syntax:

CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;

Azure_AD_principal_name 可以是 Azure AD 用户的用户主体名称,也可以是 Azure AD 组的显示名称。Azure_AD_principal_name can be the user principal name of an Azure AD user or the display name for an Azure AD group.

示例: 若要创建代表 Azure AD 联合或托管域用户的包含的数据库用户:Examples: To create a contained database user representing an Azure AD federated or managed domain user:

CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
CREATE USER [alice@fabrikam.partner.onmschina.cn] FROM EXTERNAL PROVIDER;

若要创建代表 Azure AD 或联合域组的包含的数据库用户,请提供安全组的显示名称:To create a contained database user representing an Azure AD or federated domain group, provide the display name of a security group:

CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;

若要创建代表可使用 Azure AD 令牌连接的应用程序的包含的数据库用户:To create a contained database user representing an application that connects using an Azure AD token:

CREATE USER [appName] FROM EXTERNAL PROVIDER;

备注

此命令要求 SQL 代表已登录用户访问 Azure AD(“外部提供程序”)。This command requires that SQL access Azure AD (the "external provider") on behalf of the logged-in user. 有时候,会出现导致 Azure AD 将异常返回给 SQL 的情况。Sometimes, circumstances will arise that cause Azure AD to return an exception back to SQL. 在这些情况下,用户会看到 SQL 错误 33134,其中会包含特定于 Azure AD 的错误消息。In these cases, the user will see SQL error 33134, which should contain the Azure AD-specific error message. 大多数情况下,错误会指出访问被拒绝、用户必须注册 MFA 才能访问资源,或者在第一方应用程序之间进行的访问必须通过预授权进行处理。Most of the time, the error will say that access is denied, or that the user must enroll in MFA to access the resource, or that access between first-party applications must be handled via preauthorization. 如果是前面的两种情况,则问题通常是由在用户的 Azure AD 租户中设置的条件访问策略引起的:这些策略阻止用户访问外部提供程序。In the first two cases, the issue is usually caused by Conditional Access policies that are set in the user's Azure AD tenant: they prevent the user from accessing the external provider. 更新 CA 策略以允许访问应用程序“00000002-0000-0000-c000-000000000000”(Azure AD 图形 API 的应用程序 ID)应该就能解决此问题。Updating the CA policies to allow access to the application '00000002-0000-0000-c000-000000000000' (the application ID of the Azure AD Graph API) should resolve the issue. 如果错误指出在第一方应用程序之间进行的访问必须通过预授权进行处理,则出现问题是因为用户以服务主体的身份登录。In the case that the error says access between first-party applications must be handled via preauthorization, the issue is because the user is signed in as a service principal. 如果改由用户执行命令,则命名应该会成功。The command should succeed if it is executed by a user instead.

提示

除了与 Azure 订阅关联的 Azure Active Directory 以外,无法从 Azure Active Directory 直接创建用户。You cannot directly create a user from an Azure Active Directory other than the Azure Active Directory that is associated with your Azure subscription. 但是,可将关联的 Active Directory 中导入的用户(称为外部用户)的其他 Active Directory 成员添加到租户 Active Directory 中的 Active Directory 组。However, members of other Active Directories that are imported users in the associated Active Directory (known as external users) can be added to an Active Directory group in the tenant Active Directory. 通过创建该 AD 组的包含数据库用户,来自外部 Active Directory 的用户可以访问 SQL 数据库。By creating a contained database user for that AD group, the users from the external Active Directory can gain access to SQL Database.

有关基于 Azure Active Directory 标识创建包含的数据库用户的详细信息,请参阅 CREATE USER (Transact-SQL)For more information about creating contained database users based on Azure Active Directory identities, see CREATE USER (Transact-SQL).

备注

删除服务器的 Azure Active Directory 管理员会阻止所有 Azure AD 身份验证用户连接到服务器。Removing the Azure Active Directory administrator for the server prevents any Azure AD authentication user from connecting to the server. 必要时,SQL 数据库管理员可以手动删除无法使用的 Azure AD 用户。If necessary, unusable Azure AD users can be dropped manually by a SQL Database administrator.

备注

如果收到 Connection Timeout Expired 消息,则可能需要将连接字符串的 TransparentNetworkIPResolution 参数设置为 false。If you receive a Connection Timeout Expired, you may need to set the TransparentNetworkIPResolution parameter of the connection string to false. 有关详细信息,请参阅 .NET Framework 4.6.1 的连接超时问题 - TransparentNetworkIPResolutionFor more information, see Connection timeout issue with .NET Framework 4.6.1 - TransparentNetworkIPResolution.

创建数据库用户时,该用户会收到 CONNECT 权限,并能够以 PUBLIC 角色的成员身份连接到该数据库。When you create a database user, that user receives the CONNECT permission and can connect to that database as a member of the PUBLIC role. 最初,仅供用户使用的权限是授予 PUBLIC 角色的任何权限,或者授予其所属任何 Azure AD 组的任何权限。Initially the only permissions available to the user are any permissions granted to the PUBLIC role, or any permissions granted to any Azure AD groups that they are a member of. 预配基于 Azure AD 的包含的数据库用户后,可以授予用户其他权限,方法与向任何其他类型的用户授予权限相同。Once you provision an Azure AD-based contained database user, you can grant the user additional permissions, the same way as you grant permission to any other type of user. 通常,将权限授予数据库角色,并将用户添加到角色。Typically grant permissions to database roles, and add users to roles. 有关详细信息,请参阅 数据库引擎权限基础知识For more information, see Database Engine Permission Basics. 有关特殊 SQL 数据库角色的详细信息,请参阅在 Azure SQL 数据库中管理数据库和登录名For more information about special SQL Database roles, see Managing Databases and Logins in Azure SQL Database. 如果将联合域用户帐户作为外部用户导入到托管域,则此用户必须使用托管域标识。A federated domain user account that is imported into a managed domain as an external user, must use the managed domain identity.

备注

Azure AD 用户在数据库元数据中均标记为类型 E (EXTERNAL_USER),而组则标记为类型 X (EXTERNAL_GROUPS)。Azure AD users are marked in the database metadata with type E (EXTERNAL_USER) and for groups with type X (EXTERNAL_GROUPS). 有关详细信息,请参阅 sys.database_principalsFor more information, see sys.database_principals.

使用 SSMS 或 SSDT 连接到数据库Connect to the database using SSMS or SSDT

若要确认 Azure AD 管理员已正确设置,请使用 Azure AD 管理员帐户连接到 master 数据库。To confirm the Azure AD administrator is properly set up, connect to the master database using the Azure AD administrator account. 若要预配基于 Azure AD 的包含的数据库用户(而不是拥有数据库的服务器管理员),请使用具有数据库访问权限的 Azure AD 标识连接到数据库。To provision an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity that has access to the database.

重要

SQL Server 2016 Management Studio 和 Visual Studio 2015 中的 SQL Server Data Tools 支持 Azure Active Directory 身份验证。Support for Azure Active Directory authentication is available with SQL Server 2016 Management Studio and SQL Server Data Tools in Visual Studio 2015. 2016 年 8 月版 SSMS 也包括对 Active Directory 通用身份验证的支持,这样管理员就能要求用户使用手机、短信、带 PIN 码的智能卡或移动应用通知进行多重身份验证。The August 2016 release of SSMS also includes support for Active Directory Universal Authentication, which allows administrators to require Multi-Factor Authentication using a phone call, text message, smart cards with pin, or mobile app notification.

使用 Azure AD 标识通过 SSMS 或 SSDT 进行连接Using an Azure AD identity to connect using SSMS or SSDT

以下过程说明如何使用 SQL Server Management Studio 或 SQL Server 数据库工具连接到具有 Azure AD 标识的 SQL 数据库。The following procedures show you how to connect to SQL Database with an Azure AD identity using SQL Server Management Studio or SQL Server Database Tools.

Active Directory 集成身份验证Active Directory integrated authentication

如果从联合域使用 Azure Active Directory 凭据登录到 Windows,则使用此方法。Use this method if you are logged in to Windows using your Azure Active Directory credentials from a federated domain.

  1. 启动 Management Studio 或 Data Tools 后,在“连接到服务器”(或“连接到数据库引擎”)对话框的“身份验证”框中,选择“Azure Active Directory - 集成”。Start Management Studio or Data Tools and in the Connect to Server (or Connect to Database Engine) dialog box, in the Authentication box, select Azure Active Directory - Integrated. 由于会为连接提供现有凭据,因此无需密码,也无法输入密码。No password is needed or can be entered because your existing credentials will be presented for the connection.

    选择 AD 集成身份验证

  2. 选择“选项”按钮,在“连接属性”页上的“连接到数据库”框中,键入所要连接的用户数据库的名称。Select the Options button, and on the Connection Properties page, in the Connect to database box, type the name of the user database you want to connect to. 有关详细信息,请参阅 Azure AD 多重身份验证一文,其中介绍了 SSMS 17.x 和 18.x 的连接属性之间的差异。For more information, see the article Multi-factor Azure AD auth on the differences between the Connection Properties for SSMS 17.x and 18.x.

    选择数据库名称

Active Directory 密码身份验证Active Directory password authentication

在使用 Azure AD 托管域与 Azure AD 主体名称连接时使用此方法。Use this method when connecting with an Azure AD principal name using the Azure AD managed domain. 还可以在某些情况下(例如,在进行远程工作时)将其用于联合帐户,无需访问域。You can also use it for federated accounts without access to the domain, for example, when working remotely.

使用此方法能够以 Azure AD 仅限云的标识用户身份或者以使用 Azure AD 混合标识的用户身份,向 SQL 数据库或 SQL 托管实例中的数据库进行身份验证。Use this method to authenticate to the database in SQL Database or the SQL Managed Instance with Azure AD cloud-only identity users, or those who use Azure AD hybrid identities. 此方法支持想要使用其 Windows 凭据,但其本地计算机未加入域(例如,使用远程访问)的用户。This method supports users who want to use their Windows credential, but their local machine is not joined with the domain (for example, using remote access). 在这种情况下,Windows 用户可以指定其域帐户和密码,然后可以向 SQL 数据库、SQL 托管实例或 Azure Synapse 中的数据库进行身份验证。In this case, a Windows user can indicate their domain account and password, and can authenticate to the database in SQL Database, the SQL Managed Instance, or Azure Synapse.

  1. 启动 Management Studio 或 Data Tools 后,在“连接到服务器”(或“连接到数据库引擎”)对话框的“身份验证”框中,选择“Azure Active Directory - 密码”。Start Management Studio or Data Tools and in the Connect to Server (or Connect to Database Engine) dialog box, in the Authentication box, select Azure Active Directory - Password.

  2. 在“用户名”框中,以 username@domain.com 格式键入 Azure Active Directory 用户名。In the User name box, type your Azure Active Directory user name in the format username@domain.com. 用户名必须是来自 Azure Active Directory 的帐户或来自与 Azure Active Directory 联合的域的帐户。User names must be an account from Azure Active Directory or an account from a federated domain with Azure Active Directory.

  3. 在“密码”框中,为 Azure Active Directory 帐户或联合域帐户键入用户密码。In the Password box, type your user password for the Azure Active Directory account or federated domain account.

    选择 AD 密码身份验证

  4. 选择“选项”按钮,在“连接属性”页上的“连接到数据库”框中,键入所要连接的用户数据库的名称。Select the Options button, and on the Connection Properties page, in the Connect to database box, type the name of the user database you want to connect to. (请参阅前一选项的图。)(See the graphic in the previous option.)

Active Directory 交互式身份验证Active Directory interactive authentication

通过此方法可以在使用或不使用多重身份验证 (MFA) 的情况下,使用以交互方式请求的密码进行交互式身份验证。Use this method for interactive authentication with or without Multi-Factor Authentication (MFA), with password being requested interactively. 使用此方法能够以 Azure AD 仅限云的标识用户身份或者以使用 Azure AD 混合标识的用户身份,向 SQL 数据库、SQL 托管实例和 Azure Synapse 中的数据库进行身份验证。This method can be used to authenticate to the database in SQL Database, the SQL Managed Instance, and Azure Synapse for Azure AD cloud-only identity users, or those who use Azure AD hybrid identities.

有关详细信息,请参阅将 Azure AD 多重身份验证用于 SQL 数据库和 Azure Synapse(SSMS 对 MFA 的支持)For more information, see Using multi-factor Azure AD authentication with SQL Database and Azure Synapse (SSMS support for MFA).

使用 Azure AD 标识从客户端应用程序进行连接Using an Azure AD identity to connect from a client application

以下过程说明如何使用 Azure AD 标识从客户端应用程序连接到 SQL 数据库。The following procedures show you how to connect to a SQL Database with an Azure AD identity from a client application.

Active Directory 集成身份验证Active Directory integrated authentication

若要使用集成的 Windows 身份验证,域的 Active Directory 必须与 Azure Active Directory 联合。To use integrated Windows authentication, your domain's Active Directory must be federated with Azure Active Directory.

连接到数据库的客户端应用程序(或服务)必须运行在已使用用户的域凭据加入域的计算机上。Your client application (or a service) connecting to the database must be running on a domain-joined machine under a user's domain credentials.

若要使用集成的身份验证和 Azure AD 标识连接到数据库,必须将数据库连接字符串中的 Authentication 关键字设置为 Active Directory IntegratedTo connect to a database using integrated authentication and an Azure AD identity, the Authentication keyword in the database connection string must be set to Active Directory Integrated. 下面的 C# 代码示例使用 ADO.NET。The following C# code sample uses ADO .NET.

string ConnectionString = @"Data Source=n9lxnyuzhv.database.chinacloudapi.cn; Authentication=Active Directory Integrated; Initial Catalog=testdb;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

不支持使用连接字符串关键字 Integrated Security=True 连接到 Azure SQL 数据库。The connection string keyword Integrated Security=True is not supported for connecting to Azure SQL Database. 进行 ODBC 连接时,需要删除空格,并将“Authentication”设置为“ActiveDirectoryIntegrated”。When making an ODBC connection, you will need to remove spaces and set Authentication to 'ActiveDirectoryIntegrated'.

Active Directory 密码身份验证Active Directory password authentication

若要使用 Azure AD 仅限云的标识用户帐户或那些使用 Azure AD 混合标识的用户帐户连接到数据库,必须将 Authentication 关键字设置为 Active Directory PasswordTo connect to a database using Azure AD cloud-only identity user accounts, or those who use Azure AD hybrid identities, the Authentication keyword must be set to Active Directory Password. 连接字符串必须包含“User ID/UID”和“Password/PWD”关键字和值。The connection string must contain User ID/UID and Password/PWD keywords and values. 下面的 C# 代码示例使用 ADO.NET。The following C# code sample uses ADO .NET.

string ConnectionString =
@"Data Source=n9lxnyuzhv.database.chinacloudapi.cn; Authentication=Active Directory Password; Initial Catalog=testdb;  UID=bob@contoso.partner.onmschina.cn; PWD=MyPassWord!";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

通过 Azure AD 身份验证 GitHub 演示中提供的演示代码示例,了解有关 Azure AD 身份验证方法的详细信息。Learn more about Azure AD authentication methods using the demo code samples available at Azure AD Authentication GitHub Demo.

Azure AD 令牌Azure AD token

此身份验证方法允许中间层服务通过获取 JSON Web 令牌 (JWT) 来连接到 SQL 数据库中的数据库、SQL 托管实例,或通过从 Azure AD 获取令牌来连接到 Azure Synapse。This authentication method allows middle-tier services to obtain JSON Web Tokens (JWT) to connect to the database in SQL Database, the SQL Managed Instance, or Azure Synapse by obtaining a token from Azure AD. 此方法可实现各种应用程序方案,包括使用基于证书的身份验证的服务标识、服务主体和应用程序。This method enables various application scenarios including service identities, service principals, and applications using certificate-based authentication. 必须完成四个基本步骤才能使用 Azure AD 令牌身份验证:You must complete four basic steps to use Azure AD token authentication:

  1. 在 Azure Active Directory 中注册应用程序,并获取代码的客户端 ID。Register your application with Azure Active Directory and get the client ID for your code.
  2. 创建代表应用程序的数据库用户。Create a database user representing the application. (此前已在步骤 6 中完成。)(Completed earlier in step 6.)
  3. 在运行应用程序的客户端计算机上创建证书。Create a certificate on the client computer runs the application.
  4. 为应用程序添加用作密钥的证书。Add the certificate as a key for your application.

示例连接字符串:Sample connection string:

string ConnectionString =@"Data Source=n9lxnyuzhv.database.chinacloudapi.cn; Initial Catalog=testdb;"
SqlConnection conn = new SqlConnection(ConnectionString);
conn.AccessToken = "Your JWT token"
conn.Open();

有关详细信息,请参阅 SQL Server 安全性博客For more information, see SQL Server Security Blog. 有关添加证书的详细信息,请参阅 Azure Active Directory 中基于证书的身份验证入门For information about adding a certificate, see Get started with certificate-based authentication in Azure Active Directory.

sqlcmdsqlcmd

以下语句使用版本 13.1 的 sqlcmd 进行连接,该版本可从 下载中心下载。The following statements, connect using version 13.1 of sqlcmd, which is available from the Download Center.

备注

-Gsqlcmd 命令不适用于系统标识,它需要用户主体登录名。sqlcmd with the -G command does not work with system identities, and requires a user principal login.

sqlcmd -S Target_DB_or_DW.testsrv.database.chinacloudapi.cn  -G  
sqlcmd -S Target_DB_or_DW.testsrv.database.chinacloudapi.cn -U bob@contoso.com -P MyAADPassword -G -l 30

后续步骤Next steps