none
Domain Logins and users when migrating to Azure SQL RRS feed

  • Question

  • I have a feeling that I have something setup incorrectly in my database security.

    I have a small database which is set yo use mixed mode security.  We primarily use Windows domain logins but there are one or legacy services that use Sql Server logins. 

    Almost everyone logs in using MYDOMAIN\Domain Users.  There is a login for MYDOMAIN\Domain Users and a database user for MYDOMAIN\Domain Users and this works in production.

    I am investigating migrating to SQL Azure and have verified (using SSDT and Migration Wizard) that the database is compatible with Azure v12 except for the users and logins. 

    CREATE USER [MYDOMAIN\Domain Users] FOR LOGIN [MYDOMAIN\Domain Users] results in
    SQL70015: A USER or LOGIN name cannot contain a backslash character in the targeted platform.

    (Interestingly "CREATE USER [localOfficeUser] FOR LOGIN [localOfficeUser]" also results in an error: (SQL71501)
    "User: [localOfficeUser] has an unresolved reference to Login [localOfficeUser]"
    which I assume would be resolved if I added the [localOfficeUser] login in Azure before migrating.)

    I have an Azure AD which is sync'd with my domain AD and I aspire to use some form of AD based user security management (either domain or Azure AD logins) after migrating to Azure SQL.

    Is it a mistake to have the users map to the same named login?
    How do I migrate the database without deleting the domain logins?


    Thursday, April 21, 2016 1:10 PM

Answers

  • Here goes the step-by-step practice to migrate on-prem database with Windows Auth to Azure SQL DB with AAD.

    Pre-req:

    -          Federate your windows domain Active Directory and Azure Active Directory. It assures that Windows user accounts in Active Directory are also available in Azure Active Directory.

    -          Assign your account as Active Directory Admin on Azure SQL server using https://portal.auzre.com.

    -          Install the latest SSDT preview from https://msdn.microsoft.com/en-us/library/mt204009.aspx or  SSDT included in Visual Studio 2015 Update 2 which supports AAD auth connection dialog.

    Migration steps:

    1. Import on-prem database to a SQL Server Database Project in Visual Studio.
    2. Under Security Folder, import process will create 2 files per each Windows user account.
      1. CREATE USER
      2. And CREATE LOGIN
    3. Delete or comment out all CREATE LOGIN statements which is not needed with AAD auth.
    4. Modify CREATE USER statements with supported syntax:

    --CREATE USER [MyOrgDomain\UserName] FOR LOGIN [MyOrgDomain\UserName];

    CREATE USER [username@myorgdomain.com] FOR EXTERNAL PROVIDER;

    If users have permissions than update permission statements in Permissions.sql. For instance

    --GRANT CONNECT TO [MyOrgDomain\UserName];

    GRANT CONNECT TO [username@myorgdomain.com];

    1. Set Target platform to Microsoft Azure SQL DatabaseV12 on Project Settings page.
    2. Build
    3. Publish the database by connecting Azure SQL server with AAD administrator account that you have configured in pre-req step. 
    4. Check publish status.

    These steps will migrate DB users with Windows Auth to AAD. As shown below, connection dialog will allow user to login using AAD integrated auth.

    Thursday, April 28, 2016 5:59 PM
  • An old thread, but one that comes up near the top when searching for how to migrate users to Azure SQL.

    The Data Migration Jumpstart team at Microsoft has shared a PowerShell script on Git to migrate SQL and Windows AD users to Azure SQL DB/MI/DW AAD users (it also scripts role membership and some permissions).

    You can find it here;

    https://github.com/microsoft/DataMigrationTeam/tree/master/IP%20and%20Scripts/MoveLogins

     

    Mitch vH

    Saturday, June 1, 2019 9:23 PM

All replies

  • Make sure you have followed all of these steps and you should be able to use the logons from your Federated directory

    https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/

    -------------------

    Peter

    Thursday, April 21, 2016 1:40 PM
  • Hello-

    The syntax CREATE USER [MYDOMAIN\Domain Users] FOR LOGIN [MYDOMAIN\Domain Users] is not supported for Azure SQL DB. As indicated in the previous answer you have to use the syntax indicated in  https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication

    Also as current limitation Azure AD auth for Azure SQL DB does not support MSA accounts ( i.e. outlook.com, hotmail.com, live.com..)
    Feel free to contact SQLAzureADAuth@microsoft.com for further questions.

    Thanks,
    Mirek 

    Mirek Sztajno, Senior Program Manager SQL Server security team

    Friday, April 22, 2016 7:25 PM
  • Here goes the step-by-step practice to migrate on-prem database with Windows Auth to Azure SQL DB with AAD.

    Pre-req:

    -          Federate your windows domain Active Directory and Azure Active Directory. It assures that Windows user accounts in Active Directory are also available in Azure Active Directory.

    -          Assign your account as Active Directory Admin on Azure SQL server using https://portal.auzre.com.

    -          Install the latest SSDT preview from https://msdn.microsoft.com/en-us/library/mt204009.aspx or  SSDT included in Visual Studio 2015 Update 2 which supports AAD auth connection dialog.

    Migration steps:

    1. Import on-prem database to a SQL Server Database Project in Visual Studio.
    2. Under Security Folder, import process will create 2 files per each Windows user account.
      1. CREATE USER
      2. And CREATE LOGIN
    3. Delete or comment out all CREATE LOGIN statements which is not needed with AAD auth.
    4. Modify CREATE USER statements with supported syntax:

    --CREATE USER [MyOrgDomain\UserName] FOR LOGIN [MyOrgDomain\UserName];

    CREATE USER [username@myorgdomain.com] FOR EXTERNAL PROVIDER;

    If users have permissions than update permission statements in Permissions.sql. For instance

    --GRANT CONNECT TO [MyOrgDomain\UserName];

    GRANT CONNECT TO [username@myorgdomain.com];

    1. Set Target platform to Microsoft Azure SQL DatabaseV12 on Project Settings page.
    2. Build
    3. Publish the database by connecting Azure SQL server with AAD administrator account that you have configured in pre-req step. 
    4. Check publish status.

    These steps will migrate DB users with Windows Auth to AAD. As shown below, connection dialog will allow user to login using AAD integrated auth.

    Thursday, April 28, 2016 5:59 PM
  • An old thread, but one that comes up near the top when searching for how to migrate users to Azure SQL.

    The Data Migration Jumpstart team at Microsoft has shared a PowerShell script on Git to migrate SQL and Windows AD users to Azure SQL DB/MI/DW AAD users (it also scripts role membership and some permissions).

    You can find it here;

    https://github.com/microsoft/DataMigrationTeam/tree/master/IP%20and%20Scripts/MoveLogins

     

    Mitch vH

    Saturday, June 1, 2019 9:23 PM