Login failures connecting to new principal after failover using Database Mirroring
Q: 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 mirror server as exist 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 that the login is not associated with a user in 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?
A: Yes. The problem is due to the fact that the SIDs (security identifiers) for the Sql Server Logins on each server do not match, even though the names for the logins are the same. 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'll 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. You do this by using the SID specification in the 'CREATE LOGIN' statement when creating the logins on the mirror server, like follows:
CREATE LOGIN <loginname> WITH PASSWORD = <password>, SID = <sid for same login on principal server>,...
You can retrieve the SID for each login from the principal server by querying 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 'if not exists (select * from sys.server_principals where name = ''' + p.name + ''') ' + char(13) + char(10) + char(9) +
'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'
Chad Boyd
This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.