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

Question

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

Raj Kadhi 20

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!

Accepted answer

1 additional answer

Your answer

Answer 1

PratikLad 1,830 Microsoft External Staff Moderator

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:

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';

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.

PratikLad 1,830 Reputation points Microsoft External Staff Moderator

2025-02-28T09:36:43.9533333+00:00

Hi Raj Kadhi ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.

In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Raj Kadhi 20 Reputation points

2025-02-28T17:29:58.0866667+00:00

@PratikLad yes, the first is definitely a solution, but not the one I want, as it requires users to reset their password and I check the link you mentioned (step-2), it won't work for the different tenants, and also I don't want to reset the password as I mentioned.

I appreciate your support. Let me know if you have any other solutions that can work with this scenario.
Raj Kadhi 20 Reputation points

2025-02-28T17:32:41.2266667+00:00

@PratikLad Thank you for providing your valuable solutions, but it is not working with the scenario I mentioned, the key thing is I don't want my users to reset their password and link you mentioned is also not having a solution for different tenants.

I appreciate your support but let me know if you have any other solutions for this.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2025-02-28T23:04:10.7633333+00:00
Raj, I tested the solution in the blog post suggested by Pratik, https://techcommunity.microsoft.com/blog/azuredbsupport/how-to-copy-azure-sql-database-to-a-different-subscription-and-different-tenant/3965985 and believe or not - it worked! I was able to copy the database that way, and when the database had reached the destination I was able to connect with a contained used, using the same password.

And to be clear, the path I followed was steps 1 to 9 in the beginning of the post.

A few key points in the post that are important, as I found out when I tested some variations:

The firewall for both servers must permit your current IP-address.

The same username and password must be good on both servers and have sufficient permissions.

On the other hand, the blog post says that the user must have the same SID on both servers. But I found that this is not needed.
PratikLad 1,830 Reputation points Microsoft External Staff Moderator

2025-03-03T10:16:51.4066667+00:00

Hi Raj Kadhi ,

As @Erland Sommarskog tried the document I have provided, and it is working for him can you please check from your end also and let us know if you are facing same issue.
Raj Kadhi 20 Reputation points

2025-03-03T17:30:35.3166667+00:00

@PratikLad @Erland Sommarskog Thank you for your inputs, I have tried the solution you mentioned and it worked indeed even for the different tenant, It will help me a lot and live users won't require to reset their password.

Once again, thank you for all the inputs and help.

Answer 2

Vinodh247 34,741 MVP Volunteer Moderator

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

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';
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;
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.

Convert logins to contained users on the source database
Enable contained authentication on the destination SQL Server
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.

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2025-02-22T10:10:43.3466667+00:00
Vinodh, Raj said this:

all users are created at the database level with their passwords.

So they are contained users, and thus there cannot be any SID mismatch.

I just tested this myself. I ran

CREATE USER MyUser WITH PASSWORD = 'MyVerySecretPassword'

in a database on one tenant. I created a BACPAC, which I imported on another tenant. And, indeed, I cannot log in with MyUser on the new tenant with MyVerySecretPassword.
Raj Kadhi 20 Reputation points

2025-02-22T13:08:15.2933333+00:00

@Erland Sommarskog Yes, you are correct, it won't work.
@Vinodh247 Users are created on database level only so they are contained users, not using server logins, however I tried to Enable contained authentication on the destination SQL Server but it is not allowing me to do even with admin user on the Azure. Can you help me with the steps/procedure to do so?
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2025-02-22T14:17:23.6733333+00:00

however I tried to Enable contained authentication on the destination SQL Server

Hm, where would do you do that? I would expect that to be on by default in Azure SQL Server.

So I don't think this is the issue. I created a new contained SQL user in the copy on the tenant, and that user was able to log in.

I also went to "Diagnose and solve problems" and that way I was able to find the exact reason for the login failure for the user that I had created in the original tenant. And the reason was that the password was not correct.

So it seems that the password hashes are not brought over with the BACPAC, or there is something that prevents from matching. So, unless Vinodh or someone else has a trick up the sleeve, I suspect that you have no choice but to set new passwords for the users.

Or consider to move to Entra authentication. ,
Raj Kadhi 20 Reputation points

2025-02-24T05:46:13.7366667+00:00

@Erland Sommarskog Thank you the detailed investigation, I am facing the same issue and could not found any solution for this.
@Vinodh247 Can you take a look and check if you have something that can work?

Share via

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

1 additional answer

Your answer