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 SQL ERRORLOG:
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:
Error in SQL ERRORLOG:
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.
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.
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.
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.
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).
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;
EXEC sp_configure 'contained database authentication', 1;
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:
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.
AlwaysOn, SQL Server.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.