How to Retain User Logins & Passwords When Migrating Azure SQL Database via BACPAC

Raj Kadhi 20 Reputation points
2025-02-22T09:15:44.4733333+00:00

I have a production application running on Azure with an Azure SQL Server and a SQL Database inside it. In my application, all users are created at the database level with their passwords.

I need to migrate this database to another Azure tenant by exporting and importing a BACPAC file. After importing, I can see that the users are transferred, but can not log in via existing password. I suspect this is due to SID changes, which cause an issue in login with the same password for a user.

How can I ensure that all users remain functional after migration without requiring them to reset or change their passwords?

Any guidance would be greatly appreciated!

Azure SQL Database
0 comments No comments
{count} votes

Accepted answer
  1. PratikLad 1,830 Reputation points Microsoft External Staff Moderator
    2025-02-27T12:10:52.5866667+00:00

    When restoring a BACPAC file from Azure SQL Database to SQL Server, user passwords may not work. This occurs because, for security reasons, passwords are changed in the background during the export process. This behavior is by design.

    To resolve the issue, you can follow below steps:

    1. Reset User Passwords Manually
    • After restoring the database, log in to SQL Server Management Studio (SSMS) as an admin.
    • Manually reset passwords for affected users using the following command:
    ALTER USER [USERNAME] WITH PASSWORD = 'NEW_PASSWORD';
    
    1. Use the Database Copy Method
    • Instead of exporting and restoring a BACPAC, consider using the database copy method.
    • This method retains user credentials.
    • More details: Azure SQL Database Copy

    Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.

    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Vinodh247 34,741 Reputation points MVP Volunteer Moderator
    2025-02-22T10:04:06.6433333+00:00

    Logins and passwords at the server level are not included because BACPAC only contains database-level objects. Users in azure SQL db are mapped to logins at the server level, their passwords are lost due to SID mismatches.

    Solution 1: Manually Recreate Logins with the Same SIDs

    1. Extract the existing logins with their SIDs before migration: Run the following script on the source Azure SQL Server to retrieve the logins, their SIDs, and hashed passwords:

      SELECT name, sid FROM sys.sql_logins WHERE type_desc = 'SQL_LOGIN';

    2. Manually recreate logins on the destination SQL Server using the same SID: Run this command on the destination Azure SQL Server:

      CREATE LOGIN [UserName] WITH PASSWORD = 'YourPassword', SID = 0xSID_VALUE;

    3. Map the newly created logins to existing database users: After importing the BACPAC file, ensure the logins and database users are properly mapped:

      ALTER USER [UserName] WITH LOGIN = [UserName];

    Solution 2: Use Contained Database Users

    Alternatively, convert your SQL logins into Contained Database Users before migration, which allows passwords to be stored inside the database, avoiding SID mismatches.

    1. Convert logins to contained users on the source database
    2. Enable contained authentication on the destination SQL Server
    3. After migration, users will remain functional as contained users do not rely on server-level logins.

    For long-term maintainability, consider AAD-based authentication so that user credentials and roles are managed centrally, avoiding issues with BACPAC migrations.

    Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.