How to map logins or use contained SQL database user to connect to other replicas and map to availability databases

First published on MSDN on Feb 19, 2014

Assume a working AlwaysOn environment with an availability group. Suppose this availability group is failed over to another replica. When the application re-connects to the (new primary) replica with the same SQL authenticated user, using instance name or using listener, there may be login error like below.

This error is mentioned below for reference:

Error in SQLCMD / SSMS:
Login failed for user 'TestSQLLogin1'.

Error in SQL ERRORLOG:

Error: 18456, Severity: 14, State: 5.
Login failed for user 'TestSQLLogin1'. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.1.11]

The State 5 in above mentioned message denotes an invalid user id or that the login does not exist.

If this login is created with the same login id on the replica where the login failures occurred, connectivity may still fail for this login, and the error may change to below.

Error in SQLCMD / SSMS:

Login failed for user 'TestSQLLogin1'.
Cannot open database requested by the login. The login failed.

Error in SQL ERRORLOG:

Error: 18456, Severity: 14, State: 38.
Login failed for user 'TestSQLLogin1'. Reason: Failed to open the explicitly specified database 'ContosoCRM'. [CLIENT: 192.168.1.11]

State 38 in above message denotes database specified in connection string is no longer valid or online. Assuming database is online, then this could be a symptom of orphaned login.

What is causing this issue?

A login is required on all replicas that can transition to the primary role , especially in the case where an availability group listener is defined, so that when an application attempts to re-connect following a failover, authentication is successful at that SQL Server instance. In addition, a login is internally identified in SQL by a Security Identifier (SID) value. This value should be same on all replicas.

To test, run below query on all replicas after changing the name to appropriate login.

SELECT @@SERVERNAME SERVERNAME, name, sid FROM sys.server_principals WHERE name='TestSQLLogin1';

A result like above shows that the sid value is different on both replicas/servers. The login should exist and its sid should be identical at all SQL Servers hosting the availability replica.

How to create login with same SID value on all replicas?

1. Create a SQL login on current primary replica and give it appropriate permissions in the database.

2. On the primary replica, run the script mentioned in section "Method 3: Create a log in script that has a blank password" step 2 of below mentioned KB. This script creates a stored procedure called sp_help_revlogin.

http://support.microsoft.com/kb/918992

How to transfer logins and passwords between instances of SQL Server

3. On the primary replica, run this stored procedure for the SQL login (change parameter value to appropriate login name created in step 1 above). This scripts the CREATE LOGIN statement. However note that it also captures the SID.

EXEC sp_help_revlogin 'TestSQLLogin1';

4. Run this CREATE LOGIN script on all other replicas.

5. To test, failover to each replica, transitioning it to the primary role and then attempt to connect using the login you created.

Can this error also occur for a Windows authenticated user?

This error does not occur for a domain user, since the SID of a domain user is same across replicas. Such a user can directly be created using SSMS. Be sure that the domain user has been added to the replica.

However, this error can also occur for Windows pass-through authentication. The SID of a Windows account cannot be manually specified using CREATE LOGIN. Hence, pass-through authentication cannot be used for such connections. Instead create login using domain user (this SID will automatically be same on all systems) or create login using SQL user (using steps mentioned in this article).

Is there another option that does not involve having to manually create a login on each replica?

Contained databases are a good option. This involves a one-time configuration. Enable contained databases and create a database user with the necessary permissions to execute the function at the secondary.

1. Enable contained databases at the server level at the SQL Server hosting the primary and the secondary.

EXEC sp_configure 'show advanced', 1;
RECONFIGURE;
EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;

2. Enable partial containment on the availability database at the primary:

ALTER DATABASE ContosoCRM SET CONTAINMENT=PARTIAL;

3. Create your SQL database user in your availability database:

USE ContosoCRM;
CREATE USER TestSQLLogin1 WITH PASSWORD ='Password1';

4. Grant that database user the necessary permissions to execute the function:

5. Test your connection – note that it is key that you specify the catalog (database) in which the created user is defined.

Keywords

AlwaysOn, SQL Server.

Author

Vijay Rodrigues