solving sql server problems for millions of dbas and developers since 2006

Identify and resolve SQL Server problems BEFORE they happen with SQL diagnostic manager

Top SQL Server Tools

SQL safe backup

SQL compliance manager

SQL Sentry Performance Advisor for SQL Server

SQL diagnostic manager

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

By: Chad Boyd | Read Comments (6) | Print

Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

Related Tips: More

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_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + case when l.is_expiration_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

This proves a great point that when using the latest SQL Server 2005 technologies, be sure to fully test the dependent applications to ensure they are working successfully
As you begin to review database mirroring versus alternative high availability solutions, keep in mind items like this one to ensure the overall solution is working properly
Check out these related tips on MSSQLTips.com:
Special thanks to Chad Boyd from the MSSQLTips community for this tip

Related Tips: More | Become a paid author

Last Update: 1/29/2007

Share:

Free SQL Server Learning

Comments and Feedback:

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

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.

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

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.

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

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 - 9:16:51 AM - ING

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

Sunday, November 16, 2008 - 11:21:57 AM - ggrillo
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

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

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

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis!

It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now.

SQL Server Consultants - What you don't know could be your biggest asset - Guaranteed Results

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Optimizing SQL Server performance can be a daunting task. Or is it?