We are happy to announce a general availability (GA) for Azure AD server principals (Azure AD logins) for SQL managed instance (MI). This feature allows Azure AD users to create logins in the master database for MI, grant MI server level permissions for these logins and create Azure AD users with logins for individual MI databases.
Additionally, enabling Azure AD logins allow users to execute many MI features supported for SQL logins (see the documentation at the end of this blog).
What is new in the GA release
- New Azure AD admin functionality for MI
- PowerShell and CLI commands to setup Azure AD admin for MI
- Database export/import support for Azure AD users
New Azure AD admin functionality for MI
The table below summarizes the functionality for current (public preview) Azure AD admin for MI versus a new functionality delivered with GA for Azure AD logins
|
Current Azure AD admin for MI (public preview)
|
GA functionality for Azure AD admin for MI
|
|
Behaves in a similar way as Azure AD admin for SQL DB which enables Azure AD authentication, but Azure AD admin for MI cannot create Azure AD or SQL logins in the master db for MI
|
Azure AD admin for MI has a sysadmin permission and can create Azure AD and SQL logins in master db for MI.
For example, create an Azure AD login for an Azure AD user joe@contoso.com using
CREATE LOGIN [joe@contoso.com]
FROM EXTERNAL PROVIDER
where user joe@contoso.com is a member of the Azure AD for the contoso.com domain.
As a sysadmin, Azure AD admin can grant sysadmin permissions (or lower) to other logins for MI. For example:
ALTER SERVER ROLE sysadmin ADD
MEMBER [joe@contoso.com]
Once the Azure AD login is created, an Azure AD user with this login can be created in the MI database. For example:
CREATE USER [joe@contoso.com]
FROM LOGIN [joe@contoso.com]
|
|
Is not present in the sys.server_principals view
|
Is visible in the sys.server_principals view
|
|
Allows individual Azure AD guest users ( see here ) to be setup as Azure AD admin for MI
|
Requires creation of an Azure AD group with guest users as members to setup this group as an Azure AD admin for MI.
|
For more information on GA functionality for Azure AD admin for MI see CREATE LOGIN (Transact-SQL) and CREATE USER (Transact-SQL)
Please note
For Azure AD admins for MI created prior to GA, but continue operating post GA, there is no functional change and the behavior described in the table above (for Azure AD admins in public preview) still applies.
As a good practice for existing Azure AD admins for MI created prior to GA, and still operating post GA, re-set the Azure AD admin using the Azure portal to “Remove admin” and “Set admin” again for the same Azure AD user or group.
Enable PowerShell and CLI commands to setup Azure AD admin
Several PowerShell and CLI cmdlets have been created to manage an Azure AD admin for MI,
such as:
For more details, see Provision an Azure Active Directory administrator for your managed instance
Database export/import support
Database export/import using bacpac file is supported for Azure AD users for MI
using either SSMS V18.4 or SQLPackage.exe V18.4
(Download SQL Server Management Studio and Download and install sqlpackage).
The following configurations are supported using database bacpac file:
- Export/import a database between different MIs within the same Azure AD domain
- Export a database from MI and import to SQL DB within the same Azure AD domain
- Export a database from SQL DB and import to MI within the same Azure AD domain
- Export a database from MI and import to SQL Server (version 2012 or later)
- In this configuration all Azure AD users are created as SQL database principals (users) without logins. The type of these users is SQL (visible as SQL_USER in sys.database_principals).
There permissions and roles remain in the SQL Server database metadata and can be used for impersonation, however, they cannot be used to accessed and logged in to the SQL Server using their credentials.
The migration of a database with Windows users and groups from a SQL Server to MI with Azure AD authentication is supported. For more information on this topic see, T-SQL DDL support for Windows users migration from SQL Server databases to SQL Managed Instance,
Additional references related to the Azure AD logins
CREATE LOGIN (Transact-SQL)
CREATE USER (Transact-SQL)
ALTER USER (Transact-SQL)
Tutorial: Managed instance security in Azure SQL Database using Azure AD server principals (logins)
Tutorial: Migrating SQL Server on-premises Windows users and groups to Azure SQL Database managed in...
Configure and manage Azure Active Directory authentication with SQL
