We are happy to announce a public previewfor 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 mechanismsare proposed:
Automated migration using Azure Database Migration Service (DMS): coming soon
Manual migration using T-SQL DDL commands: now in public preview
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 fromSQL 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-premisesDomain 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 Serveron-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.
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: