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.