Login failures connecting to new principal after SQL Server Database Mirroring failover
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?
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:
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'
- 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
Last Updated: 2007-01-29
About the author
View all my tips