This article is contributed. See the original author and article here.

We are announcing public preview of a new capability that enables creation of Azure AD guest users directly as database users and setting Azure AD guest users as Active Directory admin for SQL for Azure SQL Database, Managed Instance and Synapse Analytics, without the requirement of adding them to an Azure AD group first.

This is applicable to:

• Azure SQL Database
• Azure SQL Managed Instance
• Synapse Analytics (formerly SQL DW)

 

 

What are Guest Users and how are they supported in Azure SQL

Guest users in Azure AD are users that have been imported into the current Azure Active Directory from other Azure Active Directories, or outside of it. 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.

Previously,  guest users could connect to SQL Database (SQL DB), Managed Instance (MI) and Synapse Analytics (formerly SQL DW) only as part of members of a group created in current Azure AD that was then mapped manually using the Transact-SQL CREATE USER and CREATE LOGIN statements in a given Similarly, to make a guest user the Active Directory Admin for the server, the guest user had to be added to an Azure AD group and the group would then have to be set as the Active Directory Admin.

 

 

What functionality does the Public Preview offer

This public preview extends previous functionality by allowing Azure AD guest users to be directly added as database users, without the requirement of adding them to an Azure AD group first and then creating a database user for that Azure AD group.  Additionally, this enables Azure AD guest user to be set directly as Active Directory admin for SQL DB, MI and DW without being part of an Azure AD group.

Example

Consider user1@outlook.com is a guest user and belongs to the Azure AD group ‘external_group’ in the current Azure AD tenant.  
Previously, we had to create this group as a database user using the T-SQL command below, allowing the guest user to connect to the database as user1@outlook.com
        create user [external_group] from external provider

With this preview, the guest user can now be directly created as a database user using the T-SQL command below:
        create user [user1@outlook.com] from external provider

In the same way, the guest user can now be directly added as the Active Directory Admin for the database server using the PowerShell command below (or equivalent CLI command):

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName <ResourceGroupName> -ServerName <ServerName> -DisplayName ‘user1@outlook.com’ 

 

Note – This works for all types of guest users, namely:

• Guest users invited from other Azure AD tenants
• Microsoft accounts such as outlook.com, hotmail.com, live.com
• Other accounts like gmail.com

 

Notes

This new capability does not impact existing functionality, rather it allows greater flexibility in managing guest users in SQL DB/MI/DW. Guest users can continue to be part of an Azure AD group in order to be added as a database user and/or Active Directory admin for the server.

 

Please refer our documentation for more details and for the PowerShell/T-SQL commands to be used for adding a guest user as a database user and as Active Directory Admin.

 

For feedback/questions on this preview, please reach out to the SQL AAD team at SQLAADFeedback@Microsoft.com

 

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.