Edit

Tutorial: Migrate SQL Server logins (preview) to Azure SQL in Azure Data Studio

  • Article

You can use Azure Database Migration Service and the Azure SQL Migration extension to assess, get right-sized Azure recommendations and migrate databases from an on-premises SQL Server to Azure SQL. As part of the post-migration tasks, we're introducing a new user experience with an independent workflow you can use to migrate logins (preview) and server roles from your on-premises source SQL Server to the Azure SQL target.

This login migration experience automates manual tasks such as the synchronization of logins with their corresponding user mappings and replicating server/securable permissions and server roles.

Important

Currently, only Azure SQL Managed Instance and SQL Server on Azure Virtual Machines targets are supported.

Completing the database migrations of your on-premises databases to Azure SQL before starting the login migration is recommended. It will ensure that the database-level users have already been migrated to the target; therefore the login migration process will perform the user-login mappings synchronization.

In this tutorial, learn how to migrate a set of different SQL Server logins from an on-premises SQL Server to Azure SQL Managed Instance, by using the Azure SQL Migration extension for Azure Data Studio.

Note

You can use the Azure SQL Migration extension for Azure Data Studio, PowerShell or Azure CLI for starting the login migration process.

In this tutorial, you learn how to:

  • Open the Migrate to Azure SQL wizard in Azure Data Studio
  • Start the SQL Server login migration wizard
  • Select your logins from the source SQL Server instance
  • Select and connect to your Azure SQL target
  • Start your SQL Server login migration and monitor progress to completion

Note

Windows account migrations are supported only for Azure SQL Managed Instance targets.

Prerequisites

Before you begin the tutorial:

  • Download and install Azure Data Studio.

  • Install the Azure SQL Migration extension from Azure Data Studio Marketplace.

  • Create a target instance of Azure SQL Managed Instance or SQL Server on Azure Virtual Machines.

  • The machine in which the client such as Azure Data Studio, PowerShell or Azure CLI runs login migrations should have connectivity to both sources and target SQL servers.

  • Ensure that the login that you use to connect to the source and target SQL Server instance are members of the sysadmin server role.

  • As an optional step. You can migrate your on-premises databases to your selected Azure SQL target using one of the following tutorials:

    Migration scenario Migration mode
    SQL Server to Azure SQL Managed Instance Online / Offline
    SQL Server to SQL Server on an Azure virtual machine Online / Offline

    Important

    If you haven't completed the database migration and the login migration process is started, the migration of logins and server roles will still happen, but login/role mappings won't be performed correctly.

    Nevertheless, the login migration process can be performed at any time, to update the user mapping synchronization for recently migrated databases.

  • For Windows accounts, ensure that the target SQL managed instance has Microsoft Entra read access. This option can be configured via the Azure portal by a user with the Global Administrator role. For more information, see Provision Microsoft Entra admin (SQL Managed Instance).

    Domain federation between local Active Directory Domain Services (AD DS) and Microsoft Entra ID has to be set up by an administrator. This configuration is required so that the on-premises Windows users can be synced with the company Microsoft Entra ID. The login migrations process would then be able to create an external login for the corresponding Microsoft Entra user in the target managed instance.

    In case the domain federation hasn't been set up yet in your Microsoft Entra tenant, the administrator can refer to the following links to get started:

  • Windows account migrations are supported only for Azure SQL Managed Instance targets. The Login Migration wizard will show you a prompt, where you have to enter the Microsoft Entra domain name to convert the Windows users to their Microsoft Entra versions.

    For example, if the Windows user is contoso\username, and the Microsoft Entra domain name is contoso.com, then the converted Microsoft Entra username will be username@contoso.com. For this conversion to happen correctly, the domain federation between the local Active Directory and Microsoft Entra ID should be set up.

    Important

    For large number of logins, we recommend using automation. With PowerShell or Azure CLI you can use the CSVFilePath switch, that allows you to pass a CSV file type as a list of logins to be migrated.

    Bulk login migrations might be time-consuming using Azure Data Studio, as you need to manually select each login to migrate on the login selection screen.

Open the Login Migration wizard in Azure Data Studio

To open the Login Migration wizard:

  1. In Azure Data Studio, go to Connections. Select and connect to your on-premises instance of SQL Server. You also can connect to SQL Server on an Azure virtual machine.

  2. Right-click the server connection and select Manage.

    Screenshot that shows a server connection and the Manage option in Azure Data Studio.

  3. In the server menu under General, select Azure SQL Migration.

    Screenshot that shows the Azure Data Studio server menu.

  4. In the Azure SQL Migration dashboard, select New login migration button to open the login migration wizard.

    Screenshot that shows the Login migration wizard.

Configure login migration settings

  1. In Step 1: Azure SQL target on the New login migration wizard, complete the following steps:

    1. Select your Azure SQL target type and Azure account. Then in the next section, select your Azure subscription, the Azure region or location, and the resource group that contains the target Azure SQL target.

      Screenshot that shows Azure account details.

    2. Use your SQL login username and password in connecting to the target managed instance. Select Connect to verify if the connection to the target is successful. Then, select Next.

      Screenshot that shows Azure SQL Managed Instance connectivity.

  2. In Step 2: Select login(s) to migrate, select the logins that you wish to migrate from the source SQL server to the Azure SQL target. For Windows accounts, you'll be prompted to enter the associated Microsoft Entra domain name. Then select Migrate to start the login migration process.

    Screenshot that shows the source logins details.

Start the login migration process

  1. In Step 3: Migration Status, the login migrations will proceed, along with other steps in the process such as validations, mappings and permissions.

    Screenshot that shows the initial login migration status.

    Screenshot that shows the continuation of the login migration status.

  2. Once the login migration is successfully completed (or if it has failures), the page displays the relevant updates.

    Screenshot that shows the completed login migration status.

Monitor your migration

  1. You can monitor the process for each login by selecting the link under the login's Migration Status.

    Screenshot that shows the details of the migrated logins.

  2. In the dialog that opens, you can monitor individual steps of the process, and selecting any of them will populate Step details with the following relevant details.

    Screenshot that shows details of the ongoing login migration.

The migration details page displays the different stages involved in the login migration process:

Status Description
Migration of logins Migrating logins that have been selected by the user to the target
Migration of server roles All server roles will be migrated from source to target
User-login mappings Synchronization between users of the databases and migrated logins
Login-server role mappings Server role membership of logins and membership between roles will be set in the target
Establish server and object (securable) Level permissions for logins in target
Establish server and object (securable) Level permissions for server roles in target

Post-migration steps

  • Your target Azure SQL should now have the logins you selected to migrate, in addition to all the server roles from the source SQL Server, the associated user mappings, role memberships and permissions copied over.

    You can verify by logging into the target Azure SQL using one of the logins migrated, by entering the same password as it had on the source SQL Server instance.

  • If you have also migrated Windows accounts, make sure to check the option of Microsoft Entra ID - Password while logging into the target managed instance using the same password that the Windows account had on the source SQL Server.

    The username should be in the format of username@contoso.com (the Microsoft Entra domain name provided in Step 2 of the login migration wizard).

Limitations

The following table describes the current status of the Login migration support by Azure SQL target by Login type:

Target Login type Support Status
Azure SQL Database SQL login No
Azure SQL Database Windows account No
Azure SQL Managed Instance SQL login Yes Preview
Azure SQL Managed Instance Windows account Yes Preview
SQL Server on Azure VM SQL login Yes Preview
SQL Server on Azure VM Windows account No

SQL Server on Azure Virtual Machines

  • Windows account migrations aren't supported for this Azure SQL target

  • Only the SQL Server default port (1433) with no option to override is supported in Azure Data Studio. An alternative is to use PowerShell or Azure CLI to complete this type of migration.

  • Only the primary IP address with no option to override is supported in Azure Data Studio. An alternative is to use PowerShell or Azure CLI to complete this type of migration.

Next steps