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.
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.
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'
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'
We can see testuser SID matches with logical master and primary database.
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.
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 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 .
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.