T-SQL DDL support for Windows users migration from SQL Server databases to SQL Managed Instance

[Mirek Sztajno]

We are happy to announce a public preview for T-SQL DDL support for Windows domain users migration from SQL Server on-premises databases to SQL managed instance (MI) with Azure AD authentication.  

The migration process for databases from SQL Server on-premises to MI can be achieved in different ways. However, for SQL on-premises databases that contain Windows users/groups, two main mechanisms are proposed: 

• Automated migration using Azure Database Migration Service (DMS): coming soon 
• Manual migration using T-SQL DDL commands: now in public preview
  

Prerequisites 

The following prerequisites apply: 

• The Windows domain used for SQL Server on-premises must be federated with Azure AD 

• The SQL backup/restore mechanism is used to migrate the database from SQL on-premises to MI 
• Azure AD admin must be setup for MI
• The Windows logins for users and groups existing in SQL Server must be created manually in the master database for MI using the new Azure AD notation 
  •  e.g. SQL Server login Domain\UserName, create a login in the master database for MI using the following syntax: 
    CREATE LOGIN [UserName@Domain.com] FROM EXTERNAL PROVIDER 
    For more information on Azure AD login creation see  CREATE LOGIN (Transact-SQL) 

The new DDL syntax support for migration 

Once the database originating from SQL Server on-premises is restored on MI, the following T-SQL DDL commands allow users to remap Windows users and groups to Azure AD users and groups. 

For Windows users with logins: 

• ALTER USER [Domain\UserName] WITH LOGIN= [UserName@Domain.com] 
  • Maps SQL Server on-premises Domain user [Domain\UserName] to Azure AD user [UserName@Domain.com] in MI 

For Windows groups with logins: 

• ALTER USER [Domain\GroupName] WITH LOGIN=[GroupName] 
  • Maps SQL Server on-premises Domain group [Domain\GroupName] to Azure AD group [GroupName] in MI 

For Windows users without logins (contained users):  

• ALTER USER [Domain\UserName] FROM EXTERNAL PROVIDER  
  • Maps SQL Server on-premises Domain user [Domain\UserName] to Azure AD user [UserName@Domain.com] in MI 

For Windows groups w/o logins:  

• ALTER USER [Domain\GroupName]  FROM EXTERNAL PROVIDER  
  • Maps SQL Server on-premises Domain group [Domain\GroupName] to Azure AD group [GroupName] in MI 

In all the above cases [UserName@Domain.com] and [GroupName] must exist in Azure AD. This means that the synchronization between the on-premises Windows Domain and Azure Active Directory has to be done beforehand. 

Results 

Below are the Windows users existing in the database after being migrated to MI and before running the ALTER USER command (upper screenshot) and after running the ALTER USER command (lower screenshot), where the migrated Windows users are changed to the new Azure AD users with their new SIDs (see query results below). 

Permissions and Role-membership migration

All Permissions and database-role memberships will be retained for the migrated database users.
However, if the logins on the SQL Server on-premises had been granted server scoped permissions or roles, those are not retained by these steps as they are contained in the master-database. In order to migrate those, you can either use DMS for the migration, or revert to T-SQL scripts.

For more information on this topic, including a step-by-step tutorial, please see:

Tutorial: Migrating SQL Server on-premises Windows users and groups to Azure SQL Database managed in... and ALTER USER (Transact-SQL).