使用多重 Azure Active Directory 身份验证Using multi-factor Azure Active Directory authentication

适用于: 是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 SQL 数据库、Azure SQL 托管实例和 Azure Synapse Analytics 支持使用“Azure Active Directory - 通用且具有 MFA 支持”身份验证从 SQL Server Management Studio (SSMS) 进行连接。Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics support connections from SQL Server Management Studio (SSMS) using Azure Active Directory - Universal with MFA authentication. 本文讨论了各种身份验证选项之间的差异,以及与将 Azure Active Directory (Azure AD) 中的通用身份验证用于 Azure SQL 相关的限制。This article discusses the differences between the various authentication options, and also the limitations associated with using Universal Authentication in Azure Active Directory (Azure AD) for Azure SQL.

下载最新 SSMS - 在客户端计算机上,从下载 SQL Server Management Studio (SSMS) 下载最新版本的 SSMS。Download the latest SSMS - On the client computer, download the latest version of SSMS, from Download SQL Server Management Studio (SSMS).

对于本文中讨论的所有功能,请至少使用 2017 年 7 月的版本 17.2。For all the features discussed in this article, use at least July 2017, version 17.2. 最新连接对话框的外观应类似于下图:The most recent connection dialog box, should look similar to the following image:

1mfa-universal-connect1mfa-universal-connect

身份验证选项Authentication options

Azure AD 有两种非交互式身份验证模型,它们可用于许多不同的应用程序(ADO.NET、JDCB、ODC 等)。There are two non-interactive authentication models for Azure AD, which can be used in many different applications (ADO.NET, JDCB, ODC, and so on). 这两种方法绝对不会产生弹出式对话框:These two methods never result in pop-up dialog boxes:

  • Azure Active Directory - Password
  • Azure Active Directory - Integrated

同时支持 Azure 多重身份验证 (MFA) 的交互式方法是:The interactive method that also supports Azure Multi-Factor Authentication (MFA) is:

  • Azure Active Directory - Universal with MFA

Azure MFA 可满足用户简单登录过程的需求,同时可帮助保护数据访问权限和应用程序。Azure MFA helps safeguard access to data and applications while meeting user demand for a simple sign-in process. 它利用一系列简单的验证选项(电话、短信、含有 PIN 码的智能卡或移动应用通知)提供强身份验证,用户可以根据自己的偏好选择所用的方法。It delivers strong authentication with a range of easy verification options (phone call, text message, smart cards with pin, or mobile app notification), allowing users to choose the method they prefer. 配合使用 Azure AD 和交互式 MFA 时会出现用于验证的弹出式对话框。Interactive MFA with Azure AD can result in a pop-up dialog box for validation.

有关 Azure 多重身份验证的说明,请参阅多重身份验证For a description of Azure Multi-Factor Authentication, see Multi-Factor Authentication. 有关配置步骤,请参阅配置 SQL Server Management Studio 的 Azure SQL 数据库多重身份验证For configuration steps, see Configure Azure SQL Database multi-factor authentication for SQL Server Management Studio.

Azure AD 域名称或租户 ID 参数Azure AD domain name or tenant ID parameter

SSMS 版本 17 开始,以来宾用户身份从其他 Azure Active Directory 导入到当前 Azure AD 的用户在连接时可提供 Azure AD 域名或租户 ID。Beginning with SSMS version 17, users that are imported into the current Azure AD from other Azure Active Directories as guest users, can provide the Azure AD domain name, or tenant ID when they connect. 来宾用户包括从其他 Azure AD、Microsoft 帐户(如 outlook.com、hotmail.com、live.com)或其他帐户(如 gmail.com)邀请的用户。Guest users include users invited from other Azure ADs, Microsoft accounts such as outlook.com, hotmail.com, live.com, or other accounts like gmail.com. 此信息让 Azure Active Directory - Universal with MFA 身份验证能够识别正确的身份验证授权。This information allows Azure Active Directory - Universal with MFA authentication to identify the correct authenticating authority. 此选项也是支持 outlook.com、hotmail.com、live.com 等 Microsoft 帐户 (MSA) 或非 MSA 帐户的必需选项。This option is also required to support Microsoft accounts (MSA) such as outlook.com, hotmail.com, live.com, or non-MSA accounts.

所有要使用通用身份验证进行身份验证的来宾用户必须输入其 Azure AD 域名或租户 ID。All guest users who want to be authenticated using Universal Authentication must enter their Azure AD domain name or tenant ID. 此参数表示 Azure SQL 逻辑服务器当前关联的 Azure AD 域名或租户 ID。This parameter represents the current Azure AD domain name or tenant ID that the Azure SQL logical server is associated with. 例如,如果 SQL 逻辑服务器与 Azure AD 域 contosotest.partner.onmschina.cn(其中用户 joe@contosodev.partner.onmschina.cn 作为从 Azure AD 域 contosodev.partner.onmschina.cn 导入的用户托管)相关联,则用于对此用户进行身份验证的域名需要为 contosotest.partner.onmschina.cnFor example, if the SQL logical server is associated with the Azure AD domain contosotest.partner.onmschina.cn, where user joe@contosodev.partner.onmschina.cn is hosted as an imported user from the Azure AD domain contosodev.partner.onmschina.cn, the domain name required to authenticate this user is contosotest.partner.onmschina.cn. 如果用户是与 SQL 逻辑服务器关联的 Azure AD 的本机用户,并且不是 MSA 帐户,则无需提供域名或租户 ID。When the user is a native user of the Azure AD associated to SQL logical server, and is not an MSA account, no domain name or tenant ID is required. 若要输入参数(从 SSMS 版本 17.2 开始),请执行以下此操作:To enter the parameter (beginning with SSMS version 17.2):

  1. 在 SSMS 中打开一个连接。Open a connection in SSMS. 输入服务器名称,然后选择“Azure Active Directory - 通用且具有 MFA 支持”身份验证。Input your server name, and select Azure Active Directory - Universal with MFA authentication. 添加要用于登录的“用户名”。Add the User name that you want to sign in with.

  2. 选择“选项”框,然后转到“连接属性”选项卡。在“连接到数据库”对话框中,针对你的数据库填写对话框。Select the Options box, and go over to the Connection Properties tab. In the Connect to Database dialog box, complete the dialog box for your database. 选中“AD 域名或租户 ID”框,并提供身份验证机构,如域名 (contosotest.partner.onmschina.cn) 或租户 ID 的 GUID。Check the AD domain name or tenant ID box, and provide authenticating authority, such as the domain name (contosotest.partner.onmschina.cn) or the GUID of the tenant ID.

    mfa-tenant-ssms

如果运行的是 SSMS 18.x 或更高版本,则来宾用户不再需要 AD 域名或租户 ID,因为 18.x 或更高版本会自动识别它。If you are running SSMS 18.x or later, the AD domain name or tenant ID is no longer needed for guest users because 18.x or later automatically recognizes it.

mfa-tenant-ssms

Azure AD 企业到企业支持Azure AD business to business support

重要

公共预览版目前不支持来宾用户在不成为某个组的成员的情况下连接到 Azure SQL 数据库、SQL 托管实例和 Azure Synapse。Support for guest users to connect to Azure SQL Database, SQL Managed Instance, and Azure Synapse without the need to be part of a group is currently in public preview. 有关详细信息,请参阅创建 Azure AD 来宾用户并将其设置为 Azure AD 管理员For more information, see Create Azure AD guest users and set as an Azure AD admin.

作为来宾用户受 Azure AD B2B 方案支持的 Azure AD 用户(请参阅什么是 Azure B2B 协作)只能作为在关联的 Azure AD 中创建并使用给定数据库中的 CREATE USER (Transact-SQL) 语句手动映射的组成员的一部分连接到 SQL 数据库和 Azure Synapse。Azure AD users that are supported for Azure AD B2B scenarios as guest users (see What is Azure B2B collaboration can connect to SQL Database and Azure Synapse only as part of members of a group created in the associated Azure AD, and mapped manually using the CREATE USER (Transact-SQL) statement in a given database. 例如,如果 steve@gmail.com 受邀加入 Azure AD contosotest(具有 Azure Ad 域 contosotest.partner.onmschina.cn),则必须在 Azure AD 中创建包含 steve@gmail.com 成员的 Azure AD 组(如 usergroup)。For example, if steve@gmail.com is invited to Azure AD contosotest (with the Azure AD domain contosotest.partner.onmschina.cn), an Azure AD group, such as usergroup must be created in the Azure AD that contains the steve@gmail.com member. 随后,必须通过执行 Transact-SQL CREATE USER [usergroup] FROM EXTERNAL PROVIDER 语句,通过 Azure AD SQL 域或 Azure AD DBO 为特定数据库(例如,MyDatabase)创建此组。Then, this group must be created for a specific database (for example, MyDatabase) by an Azure AD SQL admin or Azure AD DBO, by executing the Transact-SQL CREATE USER [usergroup] FROM EXTERNAL PROVIDER statement.

创建数据库用户后,用户 steve@gmail.com 随后可以使用 SSMS 身份验证选项 Azure Active Directory – Universal with MFA 登录 MyDatabaseAfter the database user is created, then the user steve@gmail.com can sign into MyDatabase using the SSMS authentication option Azure Active Directory – Universal with MFA. 默认情况下,usergroup 仅具有连接权限。By default, the usergroup has only the connect permission. 任何进一步的数据访问权限需要由具有足够特权的用户在数据库中授予Any further data access will need to be granted in the database by a user with enough privilege.

备注

对于 SSMS 17.x,如果使用 steve@gmail.com 作为来宾用户,则必须选中“AD 域名或租户 ID”框,然后在“连接属性”对话框中添加 AD 域名 contosotest.partner.onmschina.cnFor SSMS 17.x, using steve@gmail.com as a guest user, you must check the AD domain name or tenant ID box and add the AD domain name contosotest.partner.onmschina.cn in the Connection Property dialog box. “Azure Active Directory - 通用且具有 MFA 支持”身份验证仅支持“AD 域名或租户 ID”选项。The AD domain name or tenant ID option is only supported for the Azure Active Directory - Universal with MFA authentication. 否则,该复选框会显示为灰色。Otherwise, the check box it is greyed out.

通用身份验证限制Universal Authentication limitations

  • SSMS 和 SqlPackage.exe 是目前唯一通过 Active Directory 通用身份验证针对 MFA 启用的工具。SSMS and SqlPackage.exe are the only tools currently enabled for MFA through Active Directory Universal Authentication.
  • SSMS 版本 17.2 支持使用具有 MFA 支持的通用身份验证进行多用户并发访问。SSMS version 17.2 supports multi-user concurrent access using Universal Authentication with MFA. 对于 SSMS 版本 17.0 和 17.1,工具将 SSMS 实例限制为使用通用身份验证登录到单个 Azure Active Directory 帐户。For SSMS version 17.0 and 17.1, the tool restricts a login for an instance of SSMS using Universal Authentication to a single Azure Active Directory account. 若要以另一个 Azure AD 帐户登录,则必须使用另一个 SSMS 实例。To sign in as another Azure AD account, you must use another instance of SSMS. 此限制限于 Active Directory 通用身份验证;可以使用 Azure Active Directory - Password 身份验证、Azure Active Directory - Integrated 身份验证或 SQL Server Authentication 登录其他服务器。This restriction is limited to Active Directory Universal Authentication; you can sign into a different server using Azure Active Directory - Password authentication, Azure Active Directory - Integrated authentication, or SQL Server Authentication.
  • 对于对象资源管理器、查询编辑器和查询存储可视化效果,SSMS 支持 Active Directory 通用身份验证。SSMS supports Active Directory Universal Authentication for Object Explorer, Query Editor, and Query Store visualization.
  • SSMS 版本 17.2 为导出/提取/部署数据数据库提供 DacFx 向导支持。SSMS version 17.2 provides DacFx Wizard support for Export/Extract/Deploy Data database. 在特定用户使用通用身份验证通过初始身份验证对话框进行了身份验证之后,DacFx 向导的工作方式与针对所有其他身份验证方法的方式相同。Once a specific user is authenticated through the initial authentication dialog using Universal Authentication, the DacFx Wizard functions the same way it does for all other authentication methods.
  • SSMS 表设计器不支持通用身份验证。The SSMS Table Designer does not support Universal Authentication.
  • 除了必须使用支持的 SSMS 版本,Active Directory 通用身份验证没有其他软件需求。There are no additional software requirements for Active Directory Universal Authentication except that you must use a supported version of SSMS.
  • 有关用于通用身份验证的最新 Active Directory 身份验证库 (ADAL) 版本,请参阅以下链接:Microsoft.IdentityModel.Clients.ActiveDirectorySee the following link for the latest Active Directory Authentication Library (ADAL) version for Universal authentication: Microsoft.IdentityModel.Clients.ActiveDirectory.

后续步骤Next steps