SQL Server Logins across Availability Groups and Orphaned Users

Recently I ran into an issue regarding AlwaysOn availability groups and SQL Logins; Anytime I would failover the AG to a secondary node, I would get an orphaned user. I know that orphaned users are caused by a mismatch of SIDs. So the SID from the Primary database for the SQL User isn't the SID from the secondary database.

I had to put my thinking hat on for this one. I knew that I needed to find a way to match SIDs across the Availability Groups for the failover to work without having to fix the orphaned user. And I discovered that by using T-SQL to create the user, you can specify what SID to use (thanks to http://sqlserverpedia.com/wiki/Moving_SQL_Server_Logins_Between_Servers).

So here are the steps to get SIDs uniform across the board:
1. Create the user on the Primary Node (No need to recreate if already created)
2. Run the following query on the primary node:
      SELECT sid FROM sys.server_principals WHERE name = 'SQLUserLogin'
3. Using the SID from the output, create the following query (where SID = the result from step 2):
      CREATE LOGIN [SQLUserLogin] WITH PASSWORD=N'Password', SID = 0x0000000000000000000000000;
4. Run the query from step 3 on all secondary nodes of the Availability Group.
5. You now have to go and specify any properties of the Login on the secondary nodes. Examples are password enforcement and user mappings.
6. You should be good to go!

Note: You cannot use Alter Login to change the SID on an already created user. http://msdn.microsoft.com/en-us/library/ms176060.aspx

Failed to Create Availability Group, SQL Server error 41042 encountered

Wow, it's been close to six months since my last post!!! Where has the time gone?

Anyway, I ran into an issue that no amount of Google-fu seemed to help me with. The issue was "Microsoft SQL Server, Error: 41042". I ran into this problem trying to create an Availability Group using the name of an AG that had been previously deleted, but appears to have not been completely removed. Here's a screengrab for the error:

As stated, this was an AG name for an AG that had existed and was gone from SQL Server.... but hadn't disappeared from the "Failover Cluster Manager." So I deleted the AG's service manually from the Failover Cluster manager.  

This appeared to do the trick, but then I got the error. So what to do????? As I mentioned, I checked google profusely to no avail. And after thinking for a bit, decided that this had to be a registry issue. I did some googling to determine where SQL Server Availability Group Registry keys live, and found the following excerpt from http://msdn.microsoft.com/en-us/library/ff929171.aspx:

In the registry I navigated to HKLM\Cluster\HADRAgNameToIDMap, and found the AG name I was trying to use. I deleted the key, and reran the AG wizard and.... still no luck. So I decided to do this on every node that had been a part of the AG and... Voila!!!! IT WORKED!!!


Now remember, use this at your own risk.