Login error received after initiating failover between Azure SQL primary server and its geo-replicated secondary:
"The server principal '<username>' is not able to access the database '<db_name>' under the current security context. Cannot open database '<db_name>' requested by the login. The login failed. Login failed for user '<username>'"
This typically means the logins in secondary server are not mapped correctly from primary server. Reasons could be either primary and secondary server SID mismatch or it could be a permissions issue, i.e. did not provide proper permissions to database in primary server.
Steps to handle
Verify and create proper login mapping between primary and secondary. The procedure to map logins is exemplified below. I've configured geo-replication for my P1 database Adventureworks and I've created testlogin.
Step #1
Run below T-SQL in master of primary server to identify the login and check the SID matching.
SELECT [name], [sid]
FROM [sys].[sql_logins]
WHERE [type_desc] = 'SQL_Login'
name sid
testuser 0x010600000000006400000000000000004B1D98FA1758A440A6AC481292BFECAE
azure_test 0x0106000000000064000000000000000099514D212C5CC44AB7A
Step #2
Create testuser for Adventureworks DB in primary server and provide data_reader permission. You can skip this step if you already have the same user with read-only permissions.
CREATE USER testuser
FOR LOGIN testuser
EXEC sp_addrolemember 'db_datareader', 'testuser'
Run below T-SQL on Adventureworks DB to check SID value:
SELECT [name], [sid]
FROM [sys].[database_principals]
WHERE [type_desc] = 'SQL_USER'
name sid
dbo 0x010600000000016400000000000000002B84948B9D83A54182DE3A5602C009E3
testuser 0x010600000000006400000000000000004B1D98FA1758A440A6AC481292BFECAE
We can see testuser SID matches with logical master and primary database.
Step #3
If you have matching SID, execute below T-SQL to map the login in the configured geo-replicated database in secondary server.
Login to secondary server and create login like below in master db:
CREATE LOGIN testuser1
WITH PASSWORD ='****',
SID = 0x010600000000006400000000000000004B1D98FA1758A440A6AC481292BFECAE
The connection to the database should now be successful.
SSMS test connection 1
Note: You might see this error if the login does not exist in the user database. In this case, the error is likely due to an orphaned user. Comparing the users lists between master and user db should reveal a SID mismatch between the login in the master database and the user in the user database. To map an orphaned user to a login which already exists in master, you may run the ALTER USER statement in the user database, specifying the login name:
ALTER USER <user_name> WITH Login = <login_name>;
For more details on how to configure logins and users for geo-replicated servers, please refer to the following article - Configure and manage Azure SQL Database security for geo-restore or failover .
The recommendation to overcome the incorrect mapping is to use contained users:
- With SQL Database, you can always create this type of user account.
- With SQL Managed Instance supporting Azure AD server principals, you can create user accounts to authenticate to the SQL Managed Instance without requiring database users to be created as a contained database user.
With this approach, the user authentication information is stored in each database, and replicated to geo-replicated databases automatically. However, if the same account exists in multiple databases and you are using Azure SQL Authentication, you must keep the passwords synchronized manually. Additionally, if a user has an account in different databases with different passwords, remembering those passwords can become a problem.
Note: To create contained users mapped to Azure AD identities, you must be logged in using an Azure AD account that is an administrator in the database in Azure SQL Database. In SQL Managed Instance, a SQL login with sysadmin permissions can also create an Azure AD login or user.
For more details on contained users, please check Authorize database access to SQL Database and Contained Database Users - Making Your Database Portable .
Microsoft
