Logins in secondary server not mapped correctly from primary server

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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 1SSMS test connection 1

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 .

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.