CODEWITHSUNDEEP
sql-servermirroring
mpaul
mpaul

Reputation: 157

MS SQL 2012 mirrored databases - SID mismatch causing causing connection failure on failover

I have a number of SQL Server 2012 mirrored databases where in the event of a failover to the secondary the database connections fail. It is necessary to run alter user on the user/database to get it working, which really limits the usefulness of the automatic failover. I understand this is because the SIDs for login/user don't match on the primary and secondary.

This blog post addresses this very issue, but I'm looking to confirm I accomplish what I need to do without the need to make changes at the table level because permissions are assigned at the table level and there are hundreds of tables.

Let's say the login/user is 'dbuser'.

Step 1: from the primary (get the SID for dbuser):

use master
select name, sid from sys.sql_logins
where name='dbuser'

Step 2: on the secondary (using the SID from step 1):

DROP LOGIN dbuser
CREATE Login dbuser WITH password=[my password], SID=[SID from step 1]

Step 3: failover to secondary

Step 4: for each database on secondary

USE [database]
GO
ALTER USER dbuser with login=dbuser
GO

I'm thinking step 4 should align the user dbuser SID with the newly created login dbuser SID, all of which will now match the SID on the primary, and from this point forward failovers should work without the need to do alter user.

I have seen this statement:

If you drop a Login that has database users mapped to it, the users will be orphaned in SQL Server.

So this is the core question I think: will step 4 un-orphan the user so to speak?

Upvotes: 0

Views: 663

Answers (1)

Ben Thul
Ben Thul

Reputation: 32737

In short, yes, this will work. Though I'd say that step 4 should be unnecessary.

Slightly longer answer: "orphaned user" means "a user whose SID has no login with a matching SID". When you create the login explicitly with a SID, you're guaranteeing the match. I use the same approach with Availability Groups.

Another trick I use is to also grab the hashed password from the primary server and specify that when creating the login in any secondary servers. That makes it easy for me to check that the password matches.

It's been a while since I've dealt with Mirroring, but I seem to recall that you can make a database snapshot to create a readable copy of the mirrored database. I'd encourage you to do that and try to log in with your user; the best assurance that something works is to demonstrate it.

Upvotes: 1

Related Questions