使用 Azure Active Directory 身份验证进行 SQL 身份验证Use Azure Active Directory Authentication for authentication with SQL

Azure Active Directory 身份验证是使用 Azure Active Directory (Azure AD) 中的标识连接到 Azure SQL 数据库托管实例SQL 数据仓库的一种机制。Azure Active Directory authentication is a mechanism of connecting to Azure SQL Database, Managed Instance, and SQL Data Warehouse by using identities in Azure Active Directory (Azure AD).

Note

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

通过 Azure AD 身份验证,可在一个中心位置中集中管理数据库用户和其他 Azure 服务的标识。With Azure AD authentication, you can centrally manage the identities of database users and other Azure services in one central location. 集中 ID 管理提供一个单一位置来管理数据库用户,并简化权限管理。Central ID management provides a single place to manage database users and simplifies permission management. 包括如下优点:Benefits include the following:

  • 提供一个 SQL Server 身份验证的替代方法。It provides an alternative to SQL Server authentication.
  • 帮助阻止用户标识在数据库服务器之间激增。Helps stop the proliferation of user identities across database servers.
  • 允许在单一位置中轮换密码。Allows password rotation in a single place.
  • 客户可以使用外部 (Azure AD) 组管理数据库权限。Customers can manage database permissions using external (Azure AD) groups.
  • 它可以通过启用集成的 Windows 身份验证和 Azure Active Directory 支持的其他形式的身份验证来消除存储密码。It can eliminate storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Azure Active Directory.
  • Azure AD 身份验证使用包含的数据库用户以数据库级别对标识进行身份验证。Azure AD authentication uses contained database users to authenticate identities at the database level.
  • Azure AD 支持对连接到 SQL 数据库的应用程序进行基于令牌的身份验证。Azure AD supports token-based authentication for applications connecting to SQL Database.
  • Azure AD 身份验证支持对本地 Azure Active Directory 进行 ADFS(域联合)或本机用户/密码身份验证,而无需进行域同步。Azure AD authentication supports ADFS (domain federation) or native user/password authentication for a local Azure Active Directory without domain synchronization.
  • Azure AD 支持从 SQL Server Management Studio 进行连接,后者使用 Active Directory 通用身份验证,其中包括多重身份验证 (MFA)。Azure AD supports connections from SQL Server Management Studio that use Active Directory Universal Authentication, which includes Multi-Factor Authentication (MFA). MFA 包括利用一系列简单的验证选项进行的强身份验证,这些选项包括电话、短信、含有 PIN 码的智能卡或移动应用通知。MFA includes strong authentication with a range of easy verification options - phone call, text message, smart cards with pin, or mobile app notification. 有关详细信息,请参阅 SQL 数据库和 SQL 数据仓库针对 Azure AD MFA 的 SSMS 支持For more information, see SSMS support for Azure AD MFA with SQL Database and SQL Data Warehouse.

Note

不支持使用 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 Active Directory 身份验证的以下过程。The configuration steps include the following procedures to configure and use Azure Active Directory authentication.

  1. 创建并填充 Azure AD。Create and populate Azure AD.
  2. 可选:关联或更改当前与 Azure 订阅关联的 Active Directory。Optional: Associate or change the active directory that is currently associated with your Azure Subscription.
  3. 为 Azure SQL 数据库服务器、托管实例或 Azure SQL 数据仓库创建 Azure Active Directory 管理员。Create an Azure Active Directory administrator for the Azure SQL Database server, the Managed Instance, or the Azure SQL Data Warehouse.
  4. 配置客户端计算机。Configure your client computers.
  5. 在映射到 Azure AD 标识的数据库中创建包含的数据库用户。Create contained database users in your database mapped to Azure AD identities.
  6. 通过使用 Azure AD 标识连接到数据库。Connect to your database by using Azure AD identities.

Note

要了解如何创建和填充 Azure AD,并使用 Azure SQL 数据库、托管实例和 SQL 数据仓库配置 Azure AD,请参阅使用 Azure SQL 数据库配置 Azure ADTo learn how to create and populate Azure AD, and then configure Azure AD with Azure SQL Database, Managed Instance, and SQL Data Warehouse, see Configure Azure AD with Azure SQL Database.

信任体系结构Trust architecture

以下高级别关系图概述了将 Azure AD 身份验证用于 Azure SQL 数据库的解决方案体系结构。The following high-level diagram summarizes the solution architecture of using Azure AD authentication with Azure SQL Database. 相同的概念适用于 SQL 数据仓库。The same concepts apply to SQL Data Warehouse. 若要支持 Azure AD 本机用户密码,只需考虑云部分和 Azure AD/Azure SQL 数据库。To support Azure AD native user password, only the Cloud portion and Azure AD/Azure SQL Database is considered. 若要支持联合身份验证(或 Windows 凭据的用户/密码),需要与 ADFS 块进行通信。To support Federated authentication (or user/password for Windows credentials), the communication with ADFS block is required. 箭头表示通信路径。The arrows indicate communication pathways.

AAD 身份验证关系图

下图表明允许客户端通过提交令牌连接到数据库的联合、信任和托管关系。The following diagram indicates the federation, trust, and hosting relationships that allow a client to connect to a database by submitting a token. 该令牌已由 Azure AD 进行身份验证且受数据库信任。The token is authenticated by an Azure AD, and is trusted by the database. 客户 1 可以代表具有本机用户的 Azure Active Directory 或具有联合用户的 Azure AD。Customer 1 can represent an Azure Active Directory with native users or an Azure AD with federated users. 客户 2 代表包含已导入用户的可行解决方案;在本例中,来自联合 Azure Active Directory 且 ADFS 正与 Azure Active Directory 进行同步。Customer 2 represents a possible solution including imported users; in this example coming from a federated Azure Active Directory with ADFS being synchronized with Azure Active Directory. 请务必了解,使用 Azure AD 身份验证访问数据库需要托管订阅与 Azure AD 相关联。It's important to understand that access to a database using Azure AD authentication requires that the hosting subscription is associated to the Azure AD. 必须使用相同的订阅创建托管 Azure SQL 数据库或 SQL 数据仓库的 SQL Server。The same subscription must be used to create the SQL Server hosting the Azure SQL Database or SQL Data Warehouse.

订阅关系

管理员结构Administrator structure

使用 Azure AD 身份验证时,SQL 数据库服务器和托管实例会有两个管理员帐户:原始的 SQL Server 管理员和 Azure AD 管理员。When using Azure AD authentication, there are two Administrator accounts for the SQL Database server and Managed Instance; the original SQL Server administrator and the Azure AD administrator. 相同的概念适用于 SQL 数据仓库。The same concepts apply to SQL Data Warehouse. 只有基于 Azure AD 帐户的管理员可以在用户数据库中创建第一个 Azure AD 包含的数据库用户。Only the administrator based on an Azure AD account can create the first Azure AD contained database user in a user database. Azure AD 管理员登录名可以是 Azure AD 用户,也可以是 Azure AD 组。The Azure AD administrator login can be an Azure AD user or an Azure AD group. 当管理员为组帐户时,可以由任何组成员使用,为 SQL Server 实例启用多个 Azure AD 管理员。When the administrator is a group account, it can be used by any group member, enabling multiple Azure AD administrators for the SQL Server instance. 以管理员身份使用组帐户时,允许无需更改 SQL 数据库中的用户或权限,便可集中添加和删除 Azure AD 中的组成员,从而提高可管理性。Using group account as an administrator enhances manageability by allowing you to centrally add and remove group members in Azure AD without changing the users or permissions in SQL Database. 无论何时都仅可配置一个 Azure AD 管理员(一个用户或组)。Only one Azure AD administrator (a user or group) can be configured at any time.

管理结构

权限Permissions

若要新建用户,必须具有数据库中的 ALTER ANY USER 权限。To create new users, you must have the ALTER ANY USER permission in the database. ALTER ANY USER 权限可以授予任何数据库用户。The ALTER ANY USER permission can be granted to any database user. ALTER ANY USER 权限还由服务器管理员帐户、具有该数据库的 CONTROL ON DATABASEALTER ON DATABASE 权限的数据库用户以及 db_owner 数据库角色的成员拥有。The ALTER ANY USER permission is also held by the server administrator accounts, and database users with the CONTROL ON DATABASE or ALTER ON DATABASE permission for that database, and by members of the db_owner database role.

若要在 Azure SQL 数据库、托管实例或 SQL 数据仓库中创建一个包含的数据库用户,必须使用 Azure AD 标识连接到数据库或实例。To create a contained database user in Azure SQL Database, Managed Instance, or SQL Data Warehouse, you must connect to the database or instance using an Azure AD identity. 若要创建第一个包含数据库用户,必须通过使用 Azure AD 管理员(也是数据库的所有者)连接到数据库。To create the first contained database user, you must connect to the database by using an Azure AD administrator (who is the owner of the database). 有关详细信息,请参阅使用 SQL 数据库或 SQL 数据仓库配置和管理 Azure Active Directory 身份验证This is demonstrated in Configure and manage Azure Active Directory authentication with SQL Database or SQL Data Warehouse. 只有为 Azure SQL 数据库或 SQL 数据仓库服务器创建 Azure AD 管理员之后,才有可能进行任何 Azure AD 身份验证。Any Azure AD authentication is only possible if the Azure AD admin was created for Azure SQL Database or SQL Data Warehouse server. 如果已从服务器删除 Azure Active Directory 管理员,先前在 SQL Server 内创建的现有 Azure Active Directory 用户便无法再使用其 Azure Active Directory 凭据连接到数据库。If the Azure Active Directory admin was removed from the server, existing Azure Active Directory users created previously inside SQL Server can no longer connect to the database using their Azure Active Directory credentials.

Azure AD 功能和限制Azure AD features and limitations

  • 可以在 Azure SQL Server 或 SQL 数据仓库中预配以下 Azure AD 成员:The following members of Azure AD can be provisioned in Azure SQL server or SQL Data Warehouse:

  • 属于具有 db_owner 服务器角色的组的 Azure AD 用户无法对 Azure SQL 数据库和 Azure SQL 数据仓库使用 CREATE DATABASE SCOPED CREDENTIAL 语法。Azure AD users that are part of a group that has db_owner server role cannot use the CREATE DATABASE SCOPED CREDENTIAL syntax against Azure SQL Database and Azure SQL Data Warehouse. 将出现以下错误:You will see the following error:

    SQL Error [2760] [S0001]: The specified schema name 'user@mydomain.com' either does not exist or you do not have permission to use it.

    请直接将 db_owner 角色授予个体 Azure AD 用户以缓解 CREATE DATABASE SCOPED CREDENTIAL 问题。Grant the db_owner role directly to the individual Azure AD user to mitigate the CREATE DATABASE SCOPED CREDENTIAL issue.

  • 这些系统函数在 Azure AD 主体下执行时,返回 NULL 值:These system functions return NULL values when executed under Azure AD principals:

    • SUSER_ID()
    • SUSER_NAME(<admin ID>)
    • SUSER_SNAME(<admin SID>)
    • SUSER_ID(<admin name>)
    • SUSER_SID(<admin name>)

管理实例Manage Instances

  • Azure AD 服务器主体(登录名)和用户作为托管实例的预览功能受支持。Azure AD server principals (logins) and users are supported as a preview feature for Managed Instances.
  • 托管实例中不支持将映射到 Azure AD 组的 Azure AD 服务器主体(登录名)设置为数据库所有者。Setting Azure AD server principals (logins) mapped to an Azure AD group as database owner is not supported in Managed Instances.
    • 此情况的扩展是当将组添加为 dbcreator 服务器角色的一部分时,此组中的用户可以连接到托管实例并创建新数据库,但将不能访问数据库。An extension of this is that when a group is added as part of the dbcreator server role, users from this group can connect to the Managed Instance and create new databases, but will not be able to access the database. 这是因为新的数据库所有者是 SA,而不是 Azure AD 用户。This is because the new database owner is SA, and not the Azure AD user. 如果将个体用户添加到 dbcreator 服务器角色,则不会出现此问题。This issue does not manifest if the individual user is added to the dbcreator server role.
  • Azure AD 服务器主体(登录名)支持 SQL 代理管理和作业执行。SQL Agent management and jobs execution is supported for Azure AD server principals (logins).
  • Azure AD 服务器主体(登录名)可以执行数据库备份和还原操作。Database backup and restore operations can be executed by Azure AD server principals (logins).
  • 支持审核与 Azure AD 服务器主体(登录名)和身份验证事件相关的所有语句。Auditing of all statements related to Azure AD server principals (logins) and authentication events is supported.
  • 支持为属于 sysadmin 服务器角色成员的 Azure AD 服务器主体(登录名)建立专用管理员连接。Dedicated administrator connection for Azure AD server principals (logins) which are members of sysadmin server role is supported.
    • 可以通过 SQLCMD 实用工具和 SQL Server Management Studio 提供此支持。Supported through SQLCMD Utility and SQL Server Management Studio.
  • 来自 Azure AD 服务器主体(登录名)的登录事件支持登录触发器。Logon triggers are supported for logon events coming from Azure AD server principals (logins).
  • 可以使用 Azure AD 服务器主体(登录名)设置 Service Broker 和数据库邮件。Service Broker and DB mail can be setup using an Azure AD server principal (login).

使用 Azure AD 标识进行连接Connecting using Azure AD identities

Azure Active Directory 身份验证支持使用 Azure AD 标识连接到数据库的以下方法:Azure Active Directory authentication supports the following methods of connecting to a database using Azure AD identities:

  • 使用集成的 Windows 身份验证Using integrated Windows authentication
  • 使用 Azure AD 主体名称和密码Using an Azure AD principal name and a password
  • 使用应用程序令牌身份验证Using Application token authentication

Azure AD 服务器主体(登录名)(公共预览版)支持以下身份验证方法:The following authentication methods are supported for Azure AD server principals (logins) (public preview):

  • Azure Active Directory 密码Azure Active Directory Password
  • 集成式 Azure Active DirectoryAzure Active Directory Integrated
  • 采用了 MFA 的通用 Azure Active DirectoryAzure Active Directory Universal with MFA

其他注意事项Additional considerations

  • 为了增强可管理性,建议将一个专用 Azure AD 组预配为管理员。To enhance manageability, we recommend you provision a dedicated Azure AD group as an administrator.
  • 无论何时都仅可为 Azure SQL 数据库服务器或 Azure SQL 数据仓库配置一个 Azure AD 管理员(一个用户或组)。Only one Azure AD administrator (a user or group) can be configured for an Azure SQL Database server or Azure SQL Data Warehouse at any time.
    • 为托管实例添加 Azure AD 服务器主体(登录名)(公共预览版)允许创建可以添加到 sysadmin 角色的多个 Azure AD 服务器主体(登录名)。The addition of Azure AD server principals (logins) for Managed Instances (public preview) allows the possibility of creating multiple Azure AD server principals (logins) that can be added to the sysadmin role.
  • 只有 SQL Server 的 Azure AD 管理员最初可以使用 Azure Active Directory 帐户连接到 Azure SQL 数据库服务器、托管实例或 Azure SQL 数据仓库。Only an Azure AD administrator for SQL Server can initially connect to the Azure SQL Database server, Managed Instance, or Azure SQL Data Warehouse using an Azure Active Directory account. Active Directory 管理员可以配置后续的 Azure AD 数据库用户。The Active Directory administrator can configure subsequent Azure AD database users.
  • 我们建议将连接超时值设置为 30 秒。We recommend setting the connection timeout to 30 seconds.
  • SQL Server 2016 Management Studio 和 SQL Server Data Tools for Visual Studio 2015(版本 14.0.60311.1(2016 年 4 月)或更高版本)支持 Azure Active Directory 身份验证。SQL Server 2016 Management Studio and SQL Server Data Tools for Visual Studio 2015 (version 14.0.60311.1April 2016 or later) support Azure Active Directory authentication. 用于 SqlServer 的 .NET Framework 数据提供程序(.NET Framework 4.6 或更高版本)支持 Azure AD 身份验证)。(Azure AD authentication is supported by the .NET Framework Data Provider for SqlServer; at least version .NET Framework 4.6). 因此,这些工具和数据层应用程序(DAC 和 .BACPAC)的最新版本可以使用 Azure AD 身份验证。Therefore the newest versions of these tools and data-tier applications (DAC and .BACPAC) can use Azure AD authentication.
  • 从版本 15.0.1 开始,sqlcmd 实用工具bcp 实用工具支持采用了 MFA 的 Active Directory 交互式身份验证。Beginning with version 15.0.1, sqlcmd utility and bcp utility support Active Directory Interactive authentication with MFA.
  • SQL Server Data Tools for Visual Studio 2015 至少需要 2016 年 4 月版的 Data Tools(版本 14.0.60311.1)。SQL Server Data Tools for Visual Studio 2015 requires at least the April 2016 version of the Data Tools (version 14.0.60311.1). 目前,Azure AD 用户不会显示在 SSDT 对象资源管理器中。Currently Azure AD users are not shown in SSDT Object Explorer. 解决方法是在 sys.database_principals 中查看这些用户。As a workaround, view the users in sys.database_principals.  
  • Microsoft JDBC Driver 6.0 for SQL Server 支持 Azure AD 身份验证。Microsoft JDBC Driver 6.0 for SQL Server supports Azure AD authentication. 另外,请参阅设置连接属性Also, see Setting the Connection Properties.
  • PolyBase 无法使用 Azure AD 身份验证进行身份验证。PolyBase cannot authenticate by using Azure AD authentication.  
  • Azure 门户的“导入数据库”和“导出数据库”边栏选项卡支持 SQL 数据库的 Azure AD 身份验证。 Azure AD authentication is supported for SQL Database by the Azure portal Import Database and Export Database blades. PowerShell 命令也支持使用 Azure AD 身份验证的导入和导出。Import and export using Azure AD authentication is also supported from the PowerShell command.
  • SQL 数据库、托管实例和 SQL 数据仓库通过 CLI 支持 Azure AD 身份验证。Azure AD authentication is supported for SQL Database, Managed Instance, and SQL Data Warehouse by use CLI. 有关详细信息,请参阅使用 SQL 数据库或 SQL 数据仓库配置和管理 Azure Active Directory 身份验证SQL Server - az sql serverFor more information, see Configure and manage Azure Active Directory authentication with SQL Database or SQL Data Warehouse and SQL Server - az sql server.

后续步骤Next steps