使用 SQL 配置和管理 Azure Active Directory 身份验证Configure and manage Azure Active Directory authentication with SQL

本文介绍如何创建和填充 Azure AD,然后通过 Azure SQL 数据库托管实例Azure Synapse 使用 Azure AD。This article shows you how to create and populate Azure AD, and then use Azure AD with Azure SQL Database, managed instance, and Azure Synapse. 有关概述,请参阅 Azure Active Directory 身份验证For an overview, see Azure Active Directory Authentication.

备注

本文适用于 Azure SQL 服务器,同时也适用于在 Azure SQL 服务器中创建的 SQL 数据库和 Azure Synapse。This article applies to Azure SQL server, and to both SQL Database and Azure Synapse that are created on the Azure SQL server. 为简单起见,在提到 SQL 数据库和 Azure Synapse 时,本文统称 SQL 数据库。For simplicity, SQL Database is used when referring to both SQL Database and Azure Synapse.

重要

不支持使用 Azure Active Directory 帐户连接到 Azure VM 上运行的 SQL Server。Connecting to SQL Server running on an Azure VM is not supported using an Azure Active Directory account. 请改用域 Active Directory 帐户。Use a domain Active Directory account instead.

创建并填充 Azure ADCreate and populate an Azure AD

创建 Azure AD 并对其填充用户和组。Create an Azure AD 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 AD 的关联方式For details, see How Azure subscriptions are associated with Azure AD.

  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 订阅关联或添加到 Azure Active DirectoryTo learn more about this trusted relationship see How to associate or add an Azure subscription to Azure Active Directory.

为 Azure SQL Server 创建 Azure AD 管理员Create an Azure AD administrator for Azure SQL server

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

将 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 登录名和用户会收到“无法连接到服务器”错误。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 SQL Server 管理员帐户),则无法创建基于 Azure AD 的用户,这是因为他们无权使用 Azure AD 验证建议的数据库用户。Users that are not based on an Azure AD account (including the Azure SQL 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 Active Directory 管理员Provision an Azure Active Directory administrator for your managed instance

重要

若要预配托管实例,则只执行以下步骤。Only follow these steps if you are provisioning a managed instance. 在 Azure AD 中,只能由全局/公司管理员或特权角色管理员执行此操作。This operation can only be executed by Global/Company administrator or a Privileged Role Administrator in Azure AD. 以下步骤介绍在目录中为具有不同权限的用户授予权限的过程。Following steps describe the process of granting permissions for users with different privileges in directory.

备注

对于在正式版推出之前创建的、但正式版推出之后继续运行的 MI 的 Azure AD 管理员而言,现有的行为没有任何功能性变化。For Azure AD admins for MI created prior to GA, but continue operating post GA, there is no functional change to the existing behavior. 有关更多详细信息,请参阅 MI 的新 Azure AD 管理员功能For more information, see the New Azure AD admin functionality for MI section for more details.

托管实例需要权限来读取 Azure AD,以成功完成通过安全组成员资格验证用户身份或创建新用户等任务。Your 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. 为此,需要授予托管实例读取 Azure AD 的权限。For this to work, you need to grant permissions to managed instance to read Azure AD. 有两种方法来完成此操作:从门户和 PowerShell。There are two ways to do it: from Portal and PowerShell. 以下是两种方法的步骤。The following steps both methods.

  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.

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

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

  3. 导航到托管实例,并选择想要用于 Azure AD 集成的托管实例。Navigate to Managed Instance and select one that 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. 如果在 Azure AD 中以全局/公司管理员身份登录,则可以通过 Azure 门户或使用 PowerShell 及以下脚本来完成。If you're logged in as Global/Company administrator in Azure AD, you can do it from the Azure portal or using PowerShell with the script below.

    授予权限 - 门户

    # Gives Azure Active Directory read permission to a Service Principal representing the 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 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'."
    }
    
  5. 成功完成操作后,右上角会显示以下通知:After the operation is successfully completed, the following notification will show up in the top-right corner:

    success

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

    设置管理员

  7. 在“AAD 管理员”页中,搜索某位用户,选择该用户或组作为管理员,然后选择“选择” 。In the AAD 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 Server。Role-based access control (RBAC) applies only to the Azure portal and isn't propagated to SQL Server.

    添加 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.

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

MI 的新 Azure AD 管理员功能New Azure AD admin functionality for MI

下表汇总了 MI 的公共预览版 Azure AD 登录管理员功能,以及正式版随附的新 Azure AD 登录功能。The table below summarizes the functionality for the public preview Azure AD login admin for MI, versus a new functionality delivered with GA for Azure AD logins.

公共预览版中 MI 的 Azure AD 登录管理员Azure AD login admin for MI during public preview MI 的正式版 Azure AD 管理员功能GA functionality for Azure AD admin for MI
行为类似于 SQL 数据库的 Azure AD 管理员,可实现 Azure AD 身份验证,但 Azure AD 管理员无法在 MI 的 master 数据库中创建 Azure AD 或 SQL 登录名。Behaves in a similar way as Azure AD admin for SQL Database, which enables Azure AD authentication, but the Azure AD admin cannot create Azure AD or SQL logins in the master db for MI. Azure AD 管理员拥有 sysadmin 权限,可以在 MI 的 master 数据库中创建 AAD 和 SQL 登录名。Azure AD admin has sysadmin permission and can create AAD and SQL logins in master db for MI.
在 sys.server_principals 视图中不存在Is not present in the sys.server_principals view 在 sys.server_principals 视图中存在Is present in the sys.server_principals view

对于在正式版推出之前创建的、但正式版推出之后仍在运行的 MI 的 Azure AD 管理员而言,最佳做法是使用 Azure 门户中的“删除管理员”和“设置管理员”选项,为相同的 Azure AD 用户或组重置 Azure AD 管理员。As a best practice for existing Azure AD admins for MI created before GA, and still operating post GA, reset the Azure AD admin using the Azure portal "Remove admin" and "Set admin" option for the same Azure AD user or group.

MI 的 Azure AD 登录正式版的已知问题Known issues with the Azure AD login GA for MI

  • 如果某个 Azure AD 登录名在使用 T-SQL 命令 CREATE LOGIN [myaadaccount] FROM EXTERNAL PROVIDER 创建的 MI 的 master 数据库中存在,则无法将此登录名设置为 MI 的 Azure AD 管理员。If an Azure AD login exists in the master database for MI, created using the T-SQL command CREATE LOGIN [myaadaccount] FROM EXTERNAL PROVIDER, it can't be set up as an Azure AD admin for MI. 使用 Azure 门户、PowerShell 或 CLI 命令创建 Azure AD 登录名并将其设置为 Azure AD 管理员时会遇到错误。You'll experience an error setting the login as an Azure AD admin using the Azure portal, PowerShell, or CLI commands to create the Azure AD login.

    • 必须先使用命令 DROP LOGIN [myaadaccount] 删除 master 数据库中的登录名,然后才能以 Azure AD 管理员的身份创建帐户。The login must be dropped in the master database using the command DROP LOGIN [myaadaccount], before the account can be created as an Azure AD admin.
    • DROP LOGIN 成功后,在 Azure 门户中设置 Azure AD 管理员帐户。Set up the Azure AD admin account in the Azure portal after the DROP LOGIN succeeds.
    • 如果无法设置 Azure AD 管理员帐户,请检查托管实例的 master 数据库中的登录名。If you can't set up the Azure AD admin account, check in the master database of the managed instance for the login. 使用以下命令:SELECT * FROM sys.server_principalsUse the following command: SELECT * FROM sys.server_principals
    • 设置 MI 的 Azure AD 管理员会自动在此帐户的 master 数据库中创建登录名。Setting up an Azure AD admin for MI will automatically create a login in the master database for this account. 删除 Azure AD 管理员会自动删除 master 数据库中的登录名。Removing the Azure AD admin will automatically drop the login from the master database.
  • 不支持将单个 Azure AD 来宾用户设置为 MI 的 Azure AD 管理员。Individual Azure AD guest users are not supported as Azure AD admins for MI. 来宾用户必须属于要设置为 Azure AD 管理员的 Azure AD 组。目前,Azure 门户边栏选项卡不会灰显其他 Azure AD 的来宾用户,使用户能够继续进行管理员设置。Guest users must be part of an Azure AD group to be set up as Azure AD admin. Currently, the Azure portal blade doesn't gray out guest users for another Azure AD, allowing users to continue with the admin setup. 将来宾用户保存为 Azure AD 管理员会导致设置失败。Saving guest users as an Azure AD admin will cause the setup to fail.

    • 若要将某个来宾用户设置为 MI 的 Azure AD 管理员,请将该来宾用户包含在 Azure AD 组中,然后将此组设置为 Azure AD 管理员。If you wish to make a guest user an Azure AD admin for MI, include the guest user in an Azure AD group, and set this group as an Azure AD admin.

用于 SQL 的 PowerShell 托管实例PowerShell 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 Database, 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

Cmdlet,用于预配和管理 SQL 托管实例的 Azure AD 管理员:Cmdlets used to provision and manage Azure AD admin for SQL managed instance:

Cmdlet 名称Cmdlet name 说明Description
Set-AzSqlInstanceActiveDirectoryAdministratorSet-AzSqlInstanceActiveDirectoryAdministrator 预配当前订阅中 SQL 托管实例的 Azure AD 管理员。Provisions an Azure AD administrator for 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 SQL managed instance in the current subscription.
Get-AzSqlInstanceActiveDirectoryAdministratorGet-AzSqlInstanceActiveDirectoryAdministrator 返回当前订阅中 SQL 托管实例的 Azure AD 管理员的相关信息。Returns information about an Azure AD administrator for SQL managed instance in the current subscription.

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

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

以下命令为名为 ManagedInstance01 的托管实例预配名为 DBA 的 Azure AD 管理员组。The following command provisions an Azure AD administrator group named DBAs for the 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 的托管实例的 Azure AD 管理员,该实例与资源组 ResourceGroup01 相关联。The following command removes the Azure AD administrator for the managed instance named ManagedInstanceName01 associated with the resource group ResourceGroup01.

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

为 Azure SQL 数据库服务器预配 Azure Active Directory 管理员Provision an Azure Active Directory administrator for your Azure SQL Database server

重要

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

以下两个过程说明如何使用 PowerShell 在 Azure 门户中为 Azure SQL Server 预配 Azure Active Directory 管理员。The following two procedures show you how to provision an Azure Active Directory administrator for your Azure SQL 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 SQL Server,确保为 Azure AD 和 SQL Server 使用相同的订阅。This step links the subscription-associated Active Directory with Azure SQL server making sure that the same subscription is used for both Azure AD and SQL Server. (Azure SQL Server 托管的可能是 Azure SQL 数据库或 Azure Synapse。)(The Azure SQL server can be hosting either Azure SQL Database or Azure Synapse.)

    选择-AD

  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 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 管理员时,此新的管理员名称(用户或组)不能已作为 SQL Server 身份验证用户存在于虚拟 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 SQL 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. 由于这种 SQL Server 身份验证用户不是 Azure AD 的一部分,因此使用 Azure AD 身份验证连接到服务器的任何尝试都会失败。Since such a SQL 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.

用于 Azure SQL 数据库和 Azure Synapse 的 PowerShellPowerShell for Azure 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,用于预配和管理 Azure SQL 数据库和 Azure Synapse 中的 SQL 池的 Azure AD 管理员:Cmdlets used to provision and manage Azure AD admin for Azure SQL Database and SQL Pool in Azure Synpase:

Cmdlet 名称Cmdlet name 说明Description
Set-AzSqlServerActiveDirectoryAdministratorSet-AzSqlServerActiveDirectoryAdministrator 为 Azure SQL Server 或 Azure Synapse 预配 Azure Active Directory 管理员。Provisions an Azure Active Directory administrator for Azure SQL server or Azure Synapse. (必须来自当前订阅)(Must be from the current subscription)
Remove-AzSqlServerActiveDirectoryAdministratorRemove-AzSqlServerActiveDirectoryAdministrator 删除 Azure SQL Server 或 Azure Synapse 的 Azure Active Directory 管理员。Removes an Azure Active Directory administrator for Azure SQL server or Azure Synapse.
Get-AzSqlServerActiveDirectoryAdministratorGet-AzSqlServerActiveDirectoryAdministrator 返回有关当前为 Azure SQL Server 或 Azure Synapse 配置的 Azure Active Directory 管理员的信息。Returns information about an Azure Active Directory administrator currently configured for the Azure SQL server 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 SQL Server 的当前 Azure AD 管理员的信息:The following example returns information about the current Azure AD admin for Azure SQL 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 标识连接到 Azure Synapse 中的 Azure SQL 数据库或 SQL 池,则必须安装以下软件:On all client machines, from which your applications or users connect to Azure SQL Database or SQL pool Azure Synpase using Azure AD identities, you must install the following software:

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

在映射到 Azure AD 标识的数据库中创建包含的数据库用户Create contained database users in your database mapped to Azure AD identities

重要

托管实例现在支持 Azure AD 服务器主体(登录名),因此你可以从 Azure AD 用户、组或应用程序创建登录名。Managed instance now supports Azure AD server principals (logins), which enables you to create logins from Azure AD users, groups, or applications. 可以使用 Azure AD 服务器主体(登录名)向托管实例进行身份验证,无需将数据库用户作为包含的数据库用户创建。Azure AD server principals (logins) provides the ability to authenticate to your managed instance without requiring database users to be created as a contained database user. 有关详细信息,请参阅托管实例概述For more information, see managed instance Overview. 有关创建 Azure AD 服务器主体(登录名)的语法,请参阅 CREATE LOGINFor syntax on creating Azure AD server principals (logins), see CREATE LOGIN.

Azure Active Directory 身份验证要求以包含的数据库用户的身份创建数据库用户。Azure Active Directory authentication requires database users to be created as contained database users. 基于 Azure AD 标识的包含的数据库用户是在 master 数据库中不具有登录名的数据库用户,它映射到与数据库相关联的 Azure AD 目录中的标识。A contained database user based on an Azure AD identity, is a database user that does not have a login in the master database, and which maps to an identity in the Azure AD directory 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. RBAC 角色不会传播到 Azure Synapse 中的 SQL Server、SQL 数据库或 SQL 池。RBAC roles are not propagated to SQL Server, SQL Database, or SQL Pool in Azure Synapse. Azure RBAC 角色用于管理 Azure 资源,不会应用于数据库权限。Azure RBAC roles are used for managing Azure Resources, and do not apply to database permissions. 例如, SQL Server 参与者 角色不会授予连接到 Azure Synapse 中的 SQL 数据库或 SQL 池的访问权限。For example, the SQL Server Contributor role does not grant access to connect to the SQL Database or SQL pool in Azure Synapse. 必须使用 Transact-SQL 语句直接在数据库中授予访问权限。The access permission must be granted directly in the database using Transact-SQL statements.

警告

不支持在 T-SQL CREATE LOGIN 和 CREATE 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,其中会包含特定于 AAD 的错误消息。In these cases, the user will see SQL error 33134, which should contain the AAD-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. 如果是前面的两种情况,则问题通常是由在用户的 AAD 租户中设置的条件访问策略引起的:这些策略阻止用户访问外部提供程序。In the first two cases, the issue is usually caused by Conditional Access policies that are set in the user's AAD tenant: they prevent the user from accessing the external provider. 更新 CA 策略以允许访问应用程序“00000002-0000-0000-c000-000000000000”(AAD 图形 API 的应用程序 ID)应该就能解决此问题。Updating the CA policies to allow access to the application '00000002-0000-0000-c000-000000000000' (the application ID of the AAD 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 SQL Server 的 Azure Active Directory 管理员会阻止所有 Azure AD 身份验证用户连接到服务器。Removing the Azure Active Directory administrator for Azure SQL 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 user database or data warehouse by 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 a 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 后,在“连接到服务器” (或“连接到数据库引擎” )对话框的“身份验证” 框中,选择“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 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. (仅对“通用且具有 MFA 连接” 选项启用“AD 域名或租户 ID” ,其余情况它会处于灰显状态。)(The AD domain name or tenant ID option is only supported for Universal with MFA connection options, otherwise it is greyed out.)

    选择数据库名称

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 DB/DW 进行身份验证。Use this method to authenticate to SQL DB/DW with Azure AD for native or federated Azure AD users. 本机用户是指以显式方式在 Azure AD 中创建并使用用户名和密码进行身份验证的用户,而联合用户则是指其域与 Azure AD 联合的 Windows 用户。A native user is one explicitly created in Azure AD and being authenticated using user name and password, while a federated user is a Windows user whose domain is federated with Azure AD. 当用户需要使用其 Windows 凭据(例如使用远程访问),但其本地计算机未加入域时,可以使用后一方法(用户和密码)。The latter method (using user & password) can be used when a user wants to use their windows credential, but their local machine is not joined with the domain (for example, using a remote access). 在这种情况下,Windows 用户可以指定其域帐户和密码,然后使用联合凭据向 SQL DB/DW 进行身份验证。In this case, a Windows user can indicate their domain account and password and can authenticate to SQL DB/DW using federated credentials.

  1. 启动 Management Studio 或 Data Tools 后,在“连接到服务器” (或“连接到数据库引擎” )对话框的“身份验证” 框中,选择“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 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 the Azure Active Directory or an account from a domain federate with the 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.)

使用 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 标识连接到数据库,数据库连接字符串中的身份验证关键字必须设置为 Active Directory Integrated。To 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 标识连接到数据库,必须将“Authentication”关键字设置为“Active Directory Password”。To connect to a database using integrated authentication and an Azure AD identity, 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

此身份验证方法允许从 Azure Active Directory (AAD) 获取令牌,使中间层服务能够连接到 Azure Synapse 中的 Azure SQL 数据库或 SQL 池。This authentication method allows middle-tier services to connect to Azure SQL Database or SQL pool in Azure Synapse by obtaining a token from Azure Active Directory (AAD). 这样,便可以实现包含基于证书的身份验证的复杂方案。It enables sophisticated scenarios including 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