Have you ever meticulously restored an Azure SQL database from a .bacpac file, checked the job status, and breathed a sigh of relief when you saw "Success," only to be met with a wave of authentication failures and reports of orphaned users? It's a confusing and frustrating scenario. The portal says the operation was successful, the database is online, but your SQL logins and contained users are completely broken.
This isn't a random glitch or a failed job that was misreported. It's a common situation that stems from the fundamental design of how Azure SQL Database handles users and logins during a .bacpac import. This article will demystify this behavior, explaining why it happens, why it's not a bug, and how a different authentication method sidesteps the problem entirely.
--------------------------------------------------------------------------------
1. Takeaway 1: The "Successful" Restore That Secretly Breaks Your SQL Logins
The core of the problem is this: even when an Azure SQL Database import from a .bacpac file completes successfully, the connection between server-level SQL logins and the user principals inside the restored database is often severed. The same issue also affects contained database users—users whose identities are managed entirely within the database itself, not at the server level.
The technical cause for both is a Security Identifier (SID) mismatch. In the SQL Server world, a server-level login (in the master database) is mapped to a database-level user via a SID. When you export a database to a .bacpac file, the user information and its SID are included. However, when you import that .bacpac to a new or existing server, the SIDs of the logins on that target server's master database are generated independently.
This creates an "orphaned user"—a user principal that exists inside your newly restored database, but its SID no longer matches the SID of its corresponding login on the server. The link is broken, and authentication fails, even though the restore operation itself was technically a success.
2. Takeaway 2: This Isn't a Bug, It's by Design
This SID mismatch behavior is not an error; it is the expected and designed behavior for Azure SQL Database (PaaS) when using .bacpac files.
The reasoning is rooted in portability. A .bacpac file is designed to be a self-contained, portable representation of a database schema and its data, intentionally excluding server-level objects like logins or their specific SIDs. This design ensures a .bacpac can be restored to any server—whether for setting up a new development environment, migrating between tenants, or recovering to a new server in a disaster recovery scenario—without being dependent on the original server's specific security configuration.
Microsoft's position on this is clear:
We can confirm that this is expected in Azure SQL PaaS. After a DacFx import, you must manually resolve user-to-login mappings if you need them. There is no supported way to preserve or synchronize SIDs automatically during .bacpac import.
Because this is by design, manual, user-side remediation is required after every restore involving SQL logins and contained users. For orphaned users mapped to logins, the fix is to re-establish the broken link by running the ALTER USER ... WITH LOGIN = ... T-SQL statement. This command re-maps the user's SID within the database to the new SID of the corresponding login in the target server's master database, effectively repairing the broken link.
3. Takeaway 3: Why Entra ID Users Are the Exception to the Rule
Here is where the behavior becomes even more interesting. While SQL logins and contained users consistently break upon restore, users authenticated via Microsoft Entra ID (formerly Azure Active Directory) are completely unaffected. Their access remains intact.
This happens because Entra ID users are not authenticated using the traditional SQL Server SID mechanism. Their identity is tied to an Azure AD object ID, which is stored within the database. When an Entra ID user connects to the Azure SQL database, the service resolves their identity directly against the Azure AD tenant at runtime.
This authentication flow completely bypasses the server-level SID-matching process that causes SQL-based users to become orphaned. The identity verification happens with a separate, robust cloud service, making the mapping independent of the specific SQL server hosting the database. This design makes Entra ID authentication a far more resilient and portable option for Azure SQL databases that are frequently moved or restored.
--------------------------------------------------------------------------------
Conclusion
Understanding the nuances of Azure SQL restores is critical for any database administrator. The three key takeaways are straightforward: a "successful" .bacpac restore can and will break access for SQL logins and contained users due to SID mismatches; this behavior is an intentional design choice to ensure database portability; and critically, Entra ID users are immune to this problem because of their distinct authentication mechanism.
This knowledge moves the issue from being a mysterious bug to a predictable operational step that requires planning. Knowing this, how might you rethink your user authentication strategy for critical databases in Azure?
