How to retain user logins & passwords when migrating Azure SQL Server database via BACPAC

Question

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 a mismatch between the logins and users.

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

Answer 1

If I understand your situation correctly, I believe your issue is orphaned users. When this happens the User and it's correlating Login are disconnected from each other, and need to be re-mapped. You can manually do that for each orphaned User via the ALTER USER command like so: ALTER USER <user_name> WITH Login = <login_name>;

Alternatively, you can use sp_change_users_login to tell you which Users are orphaned and to fix them with the Auto_Fix command. (Note this procedure is supposedly going to be removed in a future version of SQL Server, but Microsoft's track record on actually following through with that is slim.)

• Raj Kadhi: yeah this will help if server logins are created for users, but I have not created any logins and these are the contained database users only.
• J.D.: @RajKadhi If there are no Logins created then why would you suspect this? - "I suspect this is due to SID changes, which cause a mismatch between the logins and users.". Have you tried the above anyway (I'm not familiar with working with contained databases)? Note sp_change_users_login will automatically create Logins for the Users that don't have one yet, but you need to supply the password.

Answer 2

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: SQL

Copy

ALTER USER [USERNAME] WITH PASSWORD = 'NEW_PASSWORD';

2. 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