Migrate windows logins from on premises to azure sql

Anashwar N 41 Reputation points
2021-04-19T20:15:42.223+00:00

Hi Team,

I was migrating databases from on premises sq server to Azure SQL database using DMA , migration succeeded , but users on the database is not migrated , DMA shows in behavioural changes it won't be migrated

Can someone provide me the exact steps on how to migrate the users now from on premises to Azure SQL database

Azure SQL Database

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Oury Ba-MSFT 16,636 Reputation points Microsoft Employee
    2021-04-20T00:01:45.9+00:00

    @Anonymous Welcome to Microsoft Q&A and Thank you for posting your question.

    DMA does not move logins to Azure SQL DB. Move SQL logins from the SQL Server source to Azure SQL Database by using Database Migration Service in offline mode. Use the Selected logins pane in the Migration Wizard to migrate logins to your target SQL database.
    You can also migrate Windows users and groups via Database Migration Service by enabling the corresponding toggle on the Database Migration Service Configuration page.
    Alternatively, you can use the PowerShell utility specially designed by Microsoft data migration architects. The utility uses PowerShell to create a Transact-SQL (T-SQL) script to re-create logins and select database users from the source to the target.

    The PowerShell utility automatically maps Windows Server Active Directory accounts to Azure Active Directory (Azure AD) accounts, and it can do a UPN lookup for each login against the source Active Directory instance. The utility scripts custom server and database roles, along with role membership and user permissions. Contained databases are not yet supported, and only a subset of possible SQL Server permissions are scripted. Thanks

  2. Oury Ba-MSFT 16,636 Reputation points Microsoft Employee
    2022-11-18T21:42:39.797+00:00

    Hi @Venkateswara Reddy Bijjam Sorry for the delay in response to your question.

    Windows logins are not supported in Azure SQL Database, create an Azure Active Directory login instead. Manually recreate any SQL logins.
    A PowerShell script can create a T-SQL command script to re-create logins and select database users from on-premises SQL Server to Azure SQL Database. The tool allows automatic mapping of Windows Server Active Directory accounts to Azure AD accounts, along with optionally migrating SQL Server native logins.

    https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/database/sql-server-to-sql-database-overview?view=azuresql

    Regards,
    Oury

    0 comments