创建 Azure AD 来宾用户并将其设置为 Azure AD 管理员Create Azure AD guest users and set as an Azure AD admin

适用于: Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics (SQL DW)

备注

本文目前以公共预览版提供。This article is in public preview.

Azure Active Directory (Azure AD) 中的来宾用户是从其他 Azure Active Directory 或其外部导入当前 Azure AD 的用户。Guest users in Azure Active Directory (Azure AD) are users that have been imported into the current Azure AD from other Azure Active Directories, or outside of it. 例如,来宾用户可以包含来自其他 Azure Active Directory 的用户,或者来自 @outlook.com、@hotmail.com、@live.com或 @gmail.com 等帐户的用户。For example, guest users can include users from other Azure Active Directories, or from accounts like @outlook.com, @hotmail.com, @live.com, or @gmail.com. 本文演示如何创建 Azure AD 来宾用户,并将其设置为 Azure SQL 逻辑服务器的 Azure AD 管理员,而无需让该来宾用户成为 Azure AD 中组的一部分。This article will demonstrate how to create an Azure AD guest user, and set that user as an Azure AD admin for the Azure SQL logical server, without needing to have that guest user be part of a group inside Azure AD.

功能描述Feature description

此功能摆脱了当前限制,即当来宾用户属于 Azure AD 中创建的组的成员时,仅允许他们连接到 Azure SQL 数据库、SQL 托管实例或 Azure Synapse Analytics。This feature lifts the current limitation that only allows guest users to connect to Azure SQL Database, SQL Managed Instance, or Azure Synapse Analytics when they're members of a group created in Azure AD. 在给定数据库中,需要使用 CREATE USER (Transact-SQL) 语句手动将组映射到用户。The group needed to be mapped to a user manually using the CREATE USER (Transact-SQL) statement in a given database. 为包含来宾用户的 Azure AD 组创建数据库用户后,来宾用户就可以使用 Azure Active Directory 通过 MFA 身份验证登录到数据库。Once a database user has been created for the Azure AD group containing the guest user, the guest user can sign into the database using Azure Active Directory with MFA authentication. 作为此公共预览版的一部分,可以创建来宾用户并将其直接连接到 SQL 数据库、SQL 托管实例或 Azure Synapse,而无需先将其添加到 Azure AD 组,再为该 Azure AD 组创建数据库用户。As part of this public preview, guest users can be created and connect directly to SQL Database, SQL Managed Instance, or Azure Synapse without the requirement of adding them to an Azure AD group first, and then creating a database user for that Azure AD group.

作为此功能的一部分,还可以将 Azure AD 来宾用户直接设置为 Azure SQL 逻辑服务器的 AD 管理员。As part of this feature, you also have the ability to set the Azure AD guest user directly as an AD admin for the Azure SQL logical server. 现有功能(来宾用户可以成为 Azure AD 组一部分,然后可将改组设为 Azure SQL 逻辑服务器的 AD 管理员)不受影响。The existing functionality where the guest user can be part of an Azure AD group, and that group can then be set as the Azure AD admin for the Azure SQL logical server is not impacted. 作为 Azure AD 组一部分的数据库中来宾用户也不受此更改的影响。Guest users in the database that are a part of an Azure AD group are also not impacted by this change.

若要详细了解使用 Azure AD 组时对来宾用户的现有支持,请参阅使用 Azure Active Directory 多重身份验证For more information about existing support for guest users using Azure AD groups, see Using multi-factor Azure Active Directory authentication.

先决条件Prerequisite

  • 使用 PowerShell 将来宾用户设为 Azure SQL 逻辑服务器的 Azure AD 管理员时,需要使用 Az.Sql 2.9.0 模块或更高版本。Az.Sql 2.9.0 module or higher is needed when using PowerShell to set a guest user as an Azure AD admin for the Azure SQL logical server.

创建 Azure AD 来宾用户的数据库用户Create database user for Azure AD guest user

按照以下步骤使用 Azure AD 来宾用户创建数据库用户。Follow these steps to create a database user using an Azure AD guest user.

在 SQL 数据库和 Azure Synapse 中创建来宾用户Create guest user in SQL Database and Azure Synapse

  1. 确保已将来宾用户(例如 user1@gmail.com)添加到 Azure AD,并为数据库服务器设置了 Azure AD 管理员。Ensure that the guest user (for example, user1@gmail.com) is already added into your Azure AD and an Azure AD admin has been set for the database server. Azure Active Directory 身份验证需要用到 Azure AD 管理员。Having an Azure AD admin is required for Azure Active Directory authentication.

  2. 以 Azure AD 管理员身份或具有足够 SQL 权限的 Azure AD 用户身份连接到 SQL 数据库以创建用户,并在需要添加来宾用户的数据库上运行以下命令:Connect to the SQL database as the Azure AD admin or an Azure AD user with sufficient SQL permissions to create users, and run the below command on the database where the guest user needs to be added:

    CREATE USER [user1@gmail.com] FROM EXTERNAL PROVIDER
    
  3. 现在应为来宾用户 user1@gmail.com 创建了数据库用户。There should now be a database user created for the guest user user1@gmail.com.

  4. 运行以下命令,验证是否已成功创建数据库用户:Run the below command to verify the database user got created successfully:

    SELECT * FROM sys.database_principals
    
  5. 断开连接,并使用 SQL Server Management Studio (SSMS) 通过身份验证方法“Azure Active Directory - 通用且具有 MFA”以来宾用户 user1@gmail.com 的身份登录到数据库。Disconnect and sign into the database as the guest user user1@gmail.com using SQL Server Management Studio (SSMS) using the authentication method Azure Active Directory - Universal with MFA. 有关详细信息,请参阅使用 Azure Active Directory 多重身份验证For more information, see Using multi-factor Azure Active Directory authentication.

在 SQL 托管实例中创建来宾用户Create guest user in SQL Managed Instance

备注

SQL 托管实例支持 Azure AD 用户以及 Azure AD 包含的数据库用户的登录名。SQL Managed Instance supports logins for Azure AD users, as well as Azure AD contained database users. 以下步骤说明如何在 SQL 托管实例中为 Azure AD 来宾用户创建登录名和用户。The below steps show how to create a login and user for an Azure AD guest user in SQL Managed Instance. 还可以通过使用在 SQL 数据库和 Azure Synapse 中创建来宾用户部分介绍的方法,选择在 SQL 托管实例中创建包含的数据库用户You can also choose to create a contained database user in SQL Managed Instance by using the method in the Create guest user in SQL Database and Azure Synapse section.

  1. 确保已将来宾用户(例如 user1@gmail.com)添加到 Azure AD,并为 SQL 托管实例服务器设置了 Azure AD 管理员。Ensure that the guest user (for example, user1@gmail.com) is already added into your Azure AD and an Azure AD admin has been set for the SQL Managed Instance server. Azure Active Directory 身份验证需要用到 Azure AD 管理员。Having an Azure AD admin is required for Azure Active Directory authentication.

  2. 以 Azure AD 管理员身份或具有足够 SQL 权限的 Azure AD 用户身份连接到 SQL 托管实例服务器以创建用户,并在 master 数据库上运行以下命令来创建来宾用户的登录名:Connect to the SQL Managed Instance server as the Azure AD admin or an Azure AD user with sufficient SQL permissions to create users, and run the following command on the master database to create a login for the guest user:

    CREATE LOGIN [user1@gmail.com] FROM EXTERNAL PROVIDER
    
  3. 现在应为 master 数据库中的来宾用户 user1@gmail.com 创建了登录名。There should now be a login created for the guest user user1@gmail.com in the master database.

  4. 运行以下命令,验证是否已成功创建登录名:Run the below command to verify the login got created successfully:

    SELECT * FROM sys.server_principals
    
  5. 在需要添加来宾用户的数据库上运行以下命令:Run the below command on the database where the guest user needs to be added:

    CREATE USER [user1@gmail.com] FROM LOGIN [user1@gmail.com]
    
  6. 现在应为来宾用户 user1@gmail.com 创建了数据库用户。There should now be a database user created for the guest user user1@gmail.com.

  7. 断开连接,并使用 SQL Server Management Studio (SSMS) 通过身份验证方法“Azure Active Directory - 通用且具有 MFA”以来宾用户 user1@gmail.com 的身份登录到数据库。Disconnect and sign into the database as the guest user user1@gmail.com using SQL Server Management Studio (SSMS) using the authentication method Azure Active Directory - Universal with MFA. 有关详细信息,请参阅使用 Azure Active Directory 多重身份验证For more information, see Using multi-factor Azure Active Directory authentication.

将来宾用户设为 Azure AD 管理员Setting a guest user as an Azure AD admin

按照以下步骤将 Azure AD 来宾用户设为 SQL 逻辑服务器的 Azure AD 管理员。Follow these steps to set an Azure AD guest user as the Azure AD admin for the SQL logical server.

设置 SQL 数据库和 Azure Synapse 的 Azure AD 管理员Set Azure AD admin for SQL Database and Azure Synapse

  1. 确保已将来宾用户(例如 user1@gmail.com)添加到 Azure AD。Ensure that the guest user (for example, user1@gmail.com) is already added into your Azure AD.

  2. 运行以下 PowerShell 命令,将来宾用户添加为 Azure SQL 逻辑服务器的 Azure AD 管理员:Run the following PowerShell command to add the guest user as the Azure AD admin for your Azure SQL logical server:

    • <ResourceGroupName> 替换为包含 Azure SQL 逻辑服务器的 Azure 资源组名称。Replace <ResourceGroupName> with your Azure Resource Group name that contains the Azure SQL logical server.
    • <ServerName> 替换为 Azure SQL 逻辑服务器名称。Replace <ServerName> with your Azure SQL logical server name. 如果服务器名称为 myserver.database.chinacloudapi.cn,请将 <Server Name> 替换为 myserverIf your server name is myserver.database.chinacloudapi.cn, replace <Server Name> with myserver.
    • 请将 <DisplayNameOfGuestUser> 替换为来宾用户名。Replace <DisplayNameOfGuestUser> with your guest user name.
    Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName <ResourceGroupName> -ServerName <ServerName> -DisplayName <DisplayNameOfGuestUser>
    

    还可以使用 Azure CLI 命令 az sql server ad-admin 将来宾用户设为 Azure SQL 逻辑服务器的 Azure AD 管理员。You can also use the Azure CLI command az sql server ad-admin to set the guest user as an Azure AD admin for your Azure SQL logical server.

设置 SQL 托管实例的 Azure AD 管理员Set Azure AD admin for SQL Managed Instance

  1. 确保已将来宾用户(例如 user1@gmail.com)添加到 Azure AD。Ensure that the guest user (for example, user1@gmail.com) is already added into your Azure AD.

  2. 转到 Azure 门户,然后转到 Azure Active Directory 资源。Go to the Azure portal, and go to your Azure Active Directory resource. 在“管理”下转到“用户”窗格 。Under Manage, go to the Users pane. 选择来宾用户,并记录 Object IDSelect your guest user, and record the Object ID.

  3. 运行以下 PowerShell 命令,将来宾用户添加为 SQL 托管实例的 Azure AD 管理员:Run the following PowerShell command to add the guest user as the Azure AD admin for your SQL Managed Instance:

    • <ResourceGroupName> 替换为包含 SQL 托管实例的 Azure 资源组名称。Replace <ResourceGroupName> with your Azure Resource Group name that contains the SQL Managed Instance.
    • <ManagedInstanceName> 替换为 SQL 托管实例名称。Replace <ManagedInstanceName> with your SQL Managed Instance name.
    • 请将 <DisplayNameOfGuestUser> 替换为来宾用户名。Replace <DisplayNameOfGuestUser> with your guest user name.
    • <AADObjectIDOfGuestUser> 替换为之前收集的 Object IDReplace <AADObjectIDOfGuestUser> with the Object ID gathered earlier.
    Set-AzSqlInstanceActiveDirectoryAdministrator -ResourceGroupName <ResourceGroupName> -InstanceName "<ManagedInstanceName>" -DisplayName <DisplayNameOfGuestUser> -ObjectId <AADObjectIDOfGuestUser>
    

    还可以使用 Azure CLI 命令 az sql mi ad-admin 将来宾用户设为 SQL 托管实例的 Azure AD 管理员。You can also use the Azure CLI command az sql mi ad-admin to set the guest user as an Azure AD admin for your SQL Managed Instance.

限制Limitations

Azure 门户一个限制会阻止将 Azure AD 来宾用户选为 SQL 托管实例的 Azure AD 管理员。There is a limitation on the Azure portal that prevents selecting an Azure AD guest user as the Azure AD admin for SQL Managed Instance. 对于 Azure AD 外部的来宾帐户(如 @outlook.com、@hotmail.com、@live.com 或 @gmail.com),AD 管理员选择器会显示这些帐户,但它们是灰显的且不能选择它们 。For guest accounts outside of your Azure AD like @outlook.com, @hotmail.com, @live.com, or @gmail.com, the AD admin selector shows these accounts, but they are grayed out and cannot be selected. 使用上面列出的 PowerShell 或 CLI 命令设置 Azure AD 管理员。或者,可以将包含来宾用户的 Azure AD 组设为 SQL 托管实例的 Azure AD 管理员。Use the above listed PowerShell or CLI commands to set the Azure AD admin. Alternatively, an Azure AD group containing the guest user can be set as the Azure AD admin for the SQL Managed Instance.

此功能正式发布之前,将为 SQL 托管实例启用此功能。This functionality will be enabled for SQL Managed Instance prior to General Availability of this feature.

后续步骤Next steps