Scenario Background
During a recent investigation, a customer reported that SQL Authentication was failing when connecting to an Azure SQL Database geo-secondary replica.
The customer confirmed the following:
- SQL Authentication worked successfully against the primary replica.
- The geo-secondary database was healthy and synchronized.
- The replicated database user existed in the secondary database.
- Microsoft Entra authentication continued to work successfully.
Because the database was available and replication health was normal, the initial assumption was that the issue might be related to replication or database accessibility.
After reviewing the security configuration, the issue was identified as a mismatch in SQL login configuration between the primary and secondary logical servers.
This scenario highlights an important security consideration when designing Azure SQL Database disaster recovery solutions:
Active Geo-Replication replicates user databases, but server-level objects such as SQL logins stored in the master database are not automatically replicated.
Common Symptoms
You may encounter one or more of the following symptoms:
- SQL Authentication succeeds against the primary replica but fails against the geo-secondary.
- Microsoft Entra authentication continues to work successfully.
- Replication health appears normal and synchronized.
- The database user exists in the secondary database.
- Connection attempts return errors such as:
Login failed for user ‘
‘. Invalid username or password
These symptoms can occur even when the secondary database has successfully replicated and is fully accessible through other authentication methods
Understanding the Architecture
Azure SQL Active Geo-Replication replicates user databases to the secondary region, but it does not automatically replicate server-level objects stored in the logical server’s master database, including SQL logins. Configure Security for Disaster Recovery – Azure SQL Database | Microsoft Learn
Because SQL Authentication depends on a server-level login, a replicated database user alone is not sufficient. The corresponding login must also exist on the secondary logical server and be correctly mapped. Tutorial: Geo-replication & failover in portal – Azure SQL Database | Microsoft Learn
For SQL Authentication to work successfully:
- The SQL login must exist on the target logical server.
- The login SID must match the SID of the corresponding login on the primary server.
- The database user SID must correctly map to the SQL login SID.
The relationship can be summarized as:
Figure 1. SQL Login and Database User Synchronization Behavior in Azure SQL Geo-Replication
Even though the database user is replicated successfully, SQL Authentication can fail because login validation occurs before database access is granted
Understanding the Root Cause
When SQL Authentication is used, Azure SQL first validates the server-level login.
Authentication failures can occur when:
- The SQL login does not exist on the secondary logical server.
- The login exists but has a different SID than the corresponding login on the primary server.
- The login-user mapping is broken because the replicated database user references a different SID.
Microsoft documentation specifically calls out that when using SQL logins and database users, the same logins must be present on the target server as part of geo-replication disaster recovery planning.
For more details: Configure Security for Disaster Recovery – Azure SQL Database | Microsoft Learn
Validate Login Configuration
Start by comparing the SQL logins on both logical servers:
FROM sys.sql_logins
WHERE type_desc = ‘SQL_LOGIN’;
Verify:
- The login exists on both servers.
- The SID matches between the primary and secondary servers.
- The login is not disabled.
Resolution Option 1: Use Contained Database Users
Contained users eliminate the dependency on server-level logins because authentication information is stored inside the user database.
Example:
WITH PASSWORD = ‘StrongPassword’;
Because contained users are part of the user database, they are replicated together with the database and can continue working after failover without additional login configuration.
For more information:
Contained User Access to Contained Databases – SQL Server | Microsoft Learn
Configure Security for Disaster Recovery – Azure SQL Database | Microsoft Learn
Resolution Option 2: Create the Missing Login on the Secondary Server
If SQL logins are required, retrieve the SID from the primary server’s master database:
FROM sys.sql_logins
WHERE name = ‘AppLogin’;
Then create the login on the secondary server’s master database using the same SID:
WITH PASSWORD = ‘StrongPassword’,
SID = 0x010600000000006400000000000000001E104AE44775E14EBED5F8248FEF66EC;
Microsoft also uses this same SID-preservation approach when configuring geo-replication security
Tutorial: Geo-replication & failover in portal – Azure SQL Database | Microsoft Learn
Resolution Option 3: Recreate the Login if the SID Does Not Match
If the login already exists on the secondary server’s master database but uses a different SID, drop and recreate it using the SID from the primary server.
GO
CREATE LOGIN AppLogin
WITH PASSWORD = ‘StrongPassword’,
SID = 0x010600000000006400000000000000001E104AE44775E14EBED5F8248FEF66EC;
GO
This ensures the login is correctly mapped to the replicated database user.
Alternative Validation and Remediation Using PowerShell
In our scenario, the customer was using the Azure Portal Query Editor and was unable to validate the configuration through traditional tools such as SQL Server Management Studio (SSMS) or Azure Data Studio (ADS). PowerShell provides an alternative method to both validate connectivity and apply the required login configuration on the secondary server
In our case, the customer was primarily testing connectivity through the Azure Portal Query Editor and did not always have access to traditional management tools such as SQL Server Management Studio (SSMS) or Azure Data Studio (ADS).
PowerShell can be used both to validate connectivity and to apply the required login configuration on the secondary server.
Step 1: Connect to Azure
Set-AzContext -SubscriptionId “
$token = (Get-AzAccessToken -ResourceUrl “https://database.windows.net”).Token
Step 2: Check whether the login exists on the secondary logical server
Connect to the master database on the secondary server and review the existing SQL logins:
Step 3: Create the Missing Login on the Secondary Server
If the login does not exist on the secondary logical server, connect to the secondary server’s master database and create it using the SID from the primary:
Step 4: Recreate the Login if the SID Does Not Match
If the login already exists but has a different SID than the primary, recreate it using the SID from the primary server
-Query ” DROP LOGIN AppLogin;
CREATE LOGIN AppLogin
WITH PASSWORD = ‘StrongPassword’,
SID = 0x010600000000006400000000000000001E104AE44775E14EBED5F8248FEF66EC;
“
Step 5: Validate connectivity
After creating or correcting the login, test the connection by authenticating with the SQL login against the secondary replica.
This approach is especially useful when administrators cannot use SSMS or Azure Data Studio and need to validate or remediate the configuration directly through PowerShell
-ServerInstance “myserver.database.windows.net” `
-Database “MyDatabase” `
-Username “AppLogin” `
-Password “StrongPassword” `
-Query “SELECT DB_NAME();”
A successful result confirms that SQL Authentication is working and that the login can access the target database.
Key Takeaways
- Active Geo-Replication replicates user databases, not SQL logins stored in the master database.
- A replicated database user alone does not guarantee successful SQL Authentication.
- Always validate login existence and SID consistency across primary and secondary logical servers.
- Contained database users can simplify authentication management in geo-replicated environments.
References
- Configure Security for Disaster Recovery – Azure SQL Database
- Active Geo-Replication – Azure SQL Database | Microsoft Learn