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.
Run below T-SQL in master of primary server to identify the login and check the SID matching.
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.
Run below T-SQL on Adventureworks DB to check SID value:
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:
The connection to the database should now be successful.
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 .