Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Login failures connecting to new principal after SQL Server Database Mirroring failover


By:   |   Last Updated: 2007-01-29   |   Comments (13)   |   Related Tips: More > Database Mirroring

Problem

I have configured Database Mirroring successfully between 2 SQL Server 2005 instances. My application is connecting to SQL Server using a SQL Server login, and is built using ADO and the SQL Native Client. My connection strings/connection settings specify the correct information, including the appropriate failover partner. I have also created all the same logins on the mirrored server as on the principal server. Upon testing a database failure, the mirror successfully assumes the principal role and everything looks correct on the SQL Server. I can even connect successfully to the mirror using my Windows login. However, the application reconnect fails with the following error:

Cannot open database "<db name>" requested by the login. The login failed.

It appears as if the login is not associated with a user on the new principal (originally the mirror) database. I run sp_change_users_login to synchronize the users and logins for the database, and I get a message saying it fixed multiple orphaned users. My application then reconnects successfully to the new principal server. I have tried multiple failovers, and each time I see the same behavior, i.e. the association between the login and user gets lost. Is there a way to configure the mirroring setup so this is not a problem?

Solution

Yes - This problem occurs because the SIDs (security identifiers) for the SQL Server logins on each server do not match. Although the names for the logins are the same, the login is resolved via the SID. This is not a problem with Windows/Domain user/group logins because the SIDs for these logins are created based on the domain SID for the user/group, and hence will be the same for the same given user/group no matter what SQL Server the user/group is added to.

In order to make the sp_change_users_login synchronization step unnecessary, you will need to create the SQL Server logins on the mirror server not only with the same name, but also with the same SID as on the principal server. This can be accomplished by using the SID specification in the 'CREATE LOGIN' statement when creating the logins on the mirror server. Here is an example:

CREATE LOGIN <loginname> WITH PASSWORD = <password>, SID = <sid for same login on principal server>,...

To retrieve the SID for each login from the principal server query the sys.sql_logins catalog view. Here's an example of a query that will generate an actual 'CREATE LOGIN...' statement for each SQL/Windows login on a given server:

SELECT 
  'create login [' + p.name + '] ' + 
  case when p.type in('U','G') then 'from windows ' else '' end +
  'with ' +
  case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
  'sid = ' + master.sys.fn_varbintohexstr(l.sid) + 
  ', check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end + 
  'check_policy = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
  case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
  else '' end +
  'default_database = ' + p.default_database_name +
  case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p 
LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id 
LEFT JOIN sys.credentials c ON  l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
AND p.name <> 'sa'
Next Steps


Last Updated: 2007-01-29


next webcast button


next tip button



About the author
MSSQLTips author Chad Boyd Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, December 20, 2016 - 2:44:56 PM - Tim Back To Top

Thank you.  This post helped me so much and saved me hours of time.


Thursday, September 08, 2016 - 2:46:16 AM - Bharat Pagi Back To Top

Hi There,

Great post. This is something that I was in search of since couple of days. The above SP and query works very well.

Thanks a lot. Its saved my lots of time.

 

Bharat.

 


Sunday, July 20, 2014 - 3:54:48 AM - Chief Back To Top

 

Hi,

We are using mirroring in SQL 2008. Last time mirror disconnected due to power failure. After power restore all servers up but by mistake we didn’t check the mirror status, after some hours when we checked the mirroring status disconnected. We resolve the issue and now mirroring status is synchronizing. Question is all the data from principle database will be mirrored to other server or mat we will lose any data? Thank you

 


Wednesday, July 03, 2013 - 4:04:51 PM - John Wall Back To Top

Great script.  Little documentation around for this scenario and exactly what I was looking for.

Many thanks.


Tuesday, December 11, 2012 - 3:58:26 PM - RLN Back To Top

How can I remove user access to a nirrored database without making the miror the principal?


Thursday, July 19, 2012 - 3:30:04 PM - Greg Robidoux Back To Top

Rory and benmwood - the code has been updated to make this fix as you mentioned.


Thursday, July 19, 2012 - 3:05:04 PM - Rory Back To Top

As noted by benmwood you have a couple of fields the wrong way around. Otherwise this was awesome, thanks.


Tuesday, July 07, 2009 - 1:53:31 PM - rgagne99 Back To Top

Exec this procedure on the principal to get the correct CREATE Login statements to play into the Mirror with the correct SID and hashed password.

sp_help_revlogin


Sunday, November 16, 2008 - 11:21:57 AM - ggrillo Back To Top

Thank you so much for this information.  I had the exact same problem and didn't understand why when I failedover to the mirror server, the application could not login.  I ran your SELECT statement to obtain the SID, applied it to the mirrored server and everything worked first time after that.  I've been working on this off and on for about 2 weeks and this was the solution. Thanks again!!  Greg


Friday, August 22, 2008 - 9:16:51 AM - ING Back To Top

Hi ,'

thanks for your replay.I know roles and access rights are kept in database level and while backup restore it was ok.

But am taking abt on mirroring failover.

1 : I configured high securiy mirroring setup.( 1 server is primary and 1 server is mirror )

2 : synchronise logins between servers 2 servers as databases are restore from backup primary server.

3 :Now mirroring setup is working fine.

But question is when i failover databases from primary to mirroring server,failover was successful but user complain that they can't access the database.

when i checked in details and i found logins,users were there but acess rights ( like datareader,datawriter,DBO) was missing ( never expected)

what could be the reason for this ?

Br,

ING


Friday, August 22, 2008 - 4:45:13 AM - grobido Back To Top

The database rights such as db_datareader, db_datawriter, db_owner are stored within the database they are not at the server level.  So when you did your backup and restore the permissions are still there.

As far as the logins go, did you script them out of Server1 and apply them to Server2? 

If you did not and you created them manually on both ends the link between the server login and database user will be off.

You will need to run this command for each of the logins/users you can find more about it online or in SQL Server Books Online.

sp_change_users_login

Another thing you could do to check these connections is to run this in your database

sp_helpuser


Friday, August 22, 2008 - 12:38:53 AM - ING Back To Top

Hi, Is there any way to migrate the database access rights,roles,etc (like DATA READER,DATA WRITER,Dtabase Owner) ? what happened is when i failed over from principal to mirroring server on one of our poduction environment under DR drill , logins was there but access rights ( like DATA READER,DATA WRITER,Dtabase Owner) was missing ( as it's very difficult to remember the access rights). Even though same thing happened when i failed over mirroring to oroginal prinicipal server. what could be the reason and How we can migrate/script the DATA READER,DATA WRITER,Dtabase Owner as well as logins, so that if anything goes wrong still we are safe. I tried on sql 2000 but am not sure in 2005. Thanks in advanced.


Tuesday, August 05, 2008 - 1:53:40 AM - benmwood Back To Top

Thanks for the tip. 

One little thing about your create login script - I think you have the Check Policy and Check Expiration values around the wrong way. You are giving the check_expiration attribute the is_policy_checked value and check_policy the is_expiration_checked value if you see what I mean?

Otherwise it was a very useful tip...thanks.


Learn more about SQL Server tools