An Azure relational database service.
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.