Login failed connecting to new principal after failover

Hello,

I have setup database mirroring on two Windows 2003 R2 x64 servers using SQL Server 2005 SP1 Developer Edition. Our application is connecting to SQL Server using a SQL Server login. The application is using ADO and SQL Native Client to connect to the server. After a failover, our app attempts to reconnect to the database. The reconnect fails with the error:

Cannot open database "db1" requested by the login. The login failed.

The login is not associated with a user in the new principal database. I run sp_change_users_login to reconnect the user and login. sp_change_users_login says that it fixed 1 orphaned user. Our app then reconnects successfully.

I have tried several failovers, and each time I see the same behavior. The association between the login and user gets lost.

The issue is definitely with the login. I tried using sa to connect to the database, and then our app was able to reconnect after a failover.

Is this a known issue with database mirroring? Is it fixed in SP2?

Thanks,

Heather

Hi Heather. How are you creating the sql logins on the mirror server? If you create them with the same SID as they have on the principal server, that should address the issue. In Sql 2005, you specify the SID for a sql login using the new 'create login' syntax, so it would look something like follows:

create login <loginname> with password = <password>, sid = <sid for same login on principal server>,...

HTH,

|||

Thanks, Chad. That resolved the problem.

Heather