Azure SQL failover group breaks AD authentication for cross-region applications

Cloud Platforms Microsoft Azure
, , , , , , , ,
1

After a failover event in our Azure SQL failover group, applications in the secondary region can’t authenticate using Azure AD. The connection strings use the failover group listener endpoint, and SQL authentication works fine, but AD login fails with “Login failed for user ‘’”.

Setup:

  • Primary SQL server in East US with AD admin configured
  • Secondary SQL server in West US (failover group replica)
  • Failover group listener: myapp-fg.database.windows.net
  • Applications use managed identity for authentication

After failover to West US:


SqlException: Login failed for user '<token-identified principal>'
Error Number: 18456, State: 1

I’ve verified the AD admin is set on both primary and secondary servers. Is there additional AD configuration needed for the secondary server after a failover? The failover group setup seems correct but something breaks with cross-region AD authentication.

2

Did you configure the Azure AD admin on the secondary server independently, or are you assuming it replicates from the primary? AD admin configuration doesn’t automatically replicate through failover groups. You need to explicitly set the AD admin on both servers.

Run this on both primary and secondary:

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "myRG" 
  -ServerName "secondary-server" -DisplayName "DBA Team"
3

Check if your applications are using the correct connection string format for AD authentication with failover groups. The listener endpoint should work, but I’ve seen cases where the ApplicationIntent parameter matters.

Try adding Authentication=Active Directory Managed Identity; explicitly in your connection string:


Server=myapp-fg.database.windows.net;Database=mydb;Authentication=Active Directory Managed Identity;

Also verify that the managed identity has the necessary Azure RBAC roles on both SQL servers, not just database permissions.

4

Solved! The issue was a combination of AD admin configuration timing and failover group setup sequence. Here’s the complete resolution:

Root Cause: The AD admin was configured on both servers, but the secondary server’s AD admin was set AFTER the failover group was created and initial sync completed. This meant the managed identity users that were created in the primary database replicated to secondary with SIDs that the secondary server couldn’t resolve because it didn’t have AD admin configured at that time.

Solution Steps:

  1. Reconfigure AD admin on secondary server (even though it appeared set):
# Remove and re-add to ensure clean configuration
Remove-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "myRG" 
  -ServerName "secondary-sql-server"

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "myRG" 
  -ServerName "secondary-sql-server" -DisplayName "SQL Admins Group"
  1. Recreate managed identity users in primary database (they replicate to secondary):
-- Connect to PRIMARY database as AD admin
DROP USER IF EXISTS [my-app-identity];

GO

CREATE USER [my-app-identity] FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader ADD MEMBER [my-app-identity];

ALTER ROLE db_datawriter ADD MEMBER [my-app-identity];

GO
  1. Verify replication to secondary (connect to secondary database as AD admin):
-- This should show the user without creating it
SELECT name, type_desc, authentication_type_desc

FROM sys.database_principals

WHERE name = 'my-app-identity';
  1. Test authentication after failover:
# Trigger test failover
az sql failover-group set-primary --name myapp-fg \
  --resource-group myRG --server secondary-sql-server

# Test connection from application
# Connection string uses listener endpoint

Critical Configuration Sequence:

  1. AD admin configuration: MUST be set on both primary and secondary servers BEFORE creating failover group or at minimum before creating any AD-authenticated users.

  2. Failover group setup: Create failover group only after AD admin is configured on both servers. This ensures proper SID resolution during replication.

  3. Cross-region authentication: The secondary server needs its own AD admin configuration. It doesn’t inherit from primary. Both servers independently authenticate against Azure AD.

Key Lessons:

  1. AD admin is server-level, not replicated: Each SQL server in a failover group needs its own AD admin configuration. This is separate from database replication.

  2. User creation timing matters: Create AD-authenticated users (managed identities, AD users) AFTER configuring AD admin on ALL servers in the failover group. The SID generated during user creation must be resolvable by all servers.

  3. Failover group setup sequence: Correct order is: (a) Configure AD admin on all servers, (b) Create failover group, (c) Create AD users in primary database, (d) Verify replication to secondary.

  4. Don’t create users directly on secondary: AD users created in the primary database replicate to secondary through the failover group. Creating the same user on secondary will cause conflicts. Always create once in primary.

After following this sequence and recreating the managed identity users, AD authentication worked perfectly after failover. The applications reconnected using the listener endpoint with no code changes needed. The key was ensuring AD admin was properly configured on the secondary server before any users were created in the primary database.

5

I did set the AD admin on both servers initially. I can see it in the Azure Portal under both SQL servers’ Active Directory admin blade. Both show the same admin group.

But maybe the issue is with the managed identity permissions? Do managed identities need to be granted access separately on the secondary server’s database?