Link to home
Start Free TrialLog in
Avatar of Amb_Man
Amb_Man

asked on

Remove the need to manually run sp_change_users_login AUTO_FIX after a mirror failover (automatic or forced)

SQL 2005 -

DBx Mirror - After a forced or automatic failure I have to manually run the AUTO_FIX command for user 'y'. The problem is then rectified but in this scenario the application that looks at the DB is 'off-line' until the command is executed. If this occurred in the middle of the evening the member of IT on call would then have to connect to the server to rectify. The user 'y' is present on both servers and has the exact same credentials.

In another scenario - DBy Mirror works perfectly when a failover has occurred. The application that accesses the DB simply goes 'off-line' for a matter of seconds until the failover process has completed. Again all user credentials are correct on both servers and the user simply automatically assumes 'dbo' of the DBy once the mirror is in it's principal state.

I've checked and double checked credentials against the first scenario and how logins were setup and can't find any problems. I've also referred to article 'http://support.microsoft.com/kb/918992/ ' without any joy.

If you require anything to be clarified more specifically Im happy to do so.

Any assistance would be greatly appreciated,

AmbMan

Avatar of Marten Rune
Marten Rune
Flag of Sweden image

Quote: "The user 'y' is present on both servers and has the exact same credentials."

Well the SQL server has a 'SID' for its accounts, this is whats you manually fixes. A better approach is to have the same accounts on both servers. This is whats proposed in http://support.microsoft.com/kb/918992/

Maybe your'e not using it in the correct way.

I'll explain, If Im offending your'e intelligence, I apologise in advance.

My example uses Server A and Server B, Server B is standby for server A. So server B needs the exact same accounts as server A.

The security context for SQL is double, first the account needs to exist in SQL server, than in the databases. The acconts for the databases (and their security level within the database) is replicated in the mirror. But the SQL Server accounts was probably created on both servers. This was wrong, they should have been created on one server (A) and moved to server B by using the CREATE PROCEDURE sp_hexadecimal approach.

To fix this. Run the CREATE PROCEDURE sp_hexadecimal from 'http://support.microsoft.com/kb/918992/ ' on Server A.
AND
run the CREATE PROCEDURE sp_help_revlogin from the same webpage
i e run step 2 from the webpage on Server A.

After running the script run 'EXEC sp_help_revlogin' on Server A
This gives CODE in the results pane for creating accounts on SQL Server B (not the database user accounts, but users for accessing the sql server). Copy this code and run it on server B (But first drop any account that already exists on the SQL server B, and let the code create the account for you, but DONT drop accounts thats not beeing created by the script).

Now to tidy up I would DROP the procedures on Server A, leaving no garbage left on it:
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
Thats it!

Now when you failover the user in the database (the sql SID, username and password) already matches the user on the SQL Server B.

Regards Marten
Avatar of Amb_Man
Amb_Man

ASKER

Thanks for your response Marten - and no you are not offending my intelligence !

This is very similair to what was available within the Kbase article I referred to but your explanation is much more comprehensive and concise.

A couple of questions for you though:

Should I be removing any mirror instances on server B before performing as I don't want to screw up the setup we have in place? Presuming that as long as their is always a dbo in situ then you could remove accounts at will?

Can I just confirm that I should create the sp_hex and sp_help_revlogin on server A against master and then run the results from exec sp_help_revlogin performed on server A on server B? I think this is where the KBase article lets itself down as it appears to refer to running it against server B but from a connnection on server A.

AmbMan.

ASKER CERTIFIED SOLUTION
Avatar of Marten Rune
Marten Rune
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To clarify:
No you don't need to remove the mirrors, but you need to run the Auto_fix command on every mirror after you recreated the SQL Server Accounts with the correct SIDs.

Good luck Marten.
Avatar of Amb_Man

ASKER

Again thanks very much for your prompt response.

I'll give this a go and get back with feedback.
Avatar of Amb_Man

ASKER

Great news - worked like a treat. Really appreciate the assistance.

Couple of observations before I accept anbd award points !!:

Only needed to DROP and CREATE for the login that we perform the AUTO_FIX as all other accounts were 'system' related and are specific for each server.
We have also performed the SID check on the DB Y where I had stated the failover works but rather bizarrely the SID's are different ! Im now concerned that we have rather luckily got this mirror functioning correctly and have not followed the correct procedure.

AmbMan
Glad to be of assistance

//Marten
Avatar of Amb_Man

ASKER

This expert helped to clarify what a kbase article posted within msdn world was trying to explain ! The answer they provided was not clear.