Mirroring - Have to run sp_change_users_login after failover

I have mirroring set up and working on a database that is used for an ASP.NET
web application. The web appliation uses SQL Authentication to connect to the
database. The mirroring is set up with a witness, and the connection string
includes "Failover Partner=myfailoverserver;".
When I'm running on the primary database, the app works great. When I do a
manual failover, the app crashes with the following error: "Cannot open
database "mydb" requested by the login. The login failed.
Login failed for user 'myuser'".
If I then run "EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser';"
everything works fine again, untill I fail over back tot he primary server.
Then I have to run sp_change_users_login again.
The mirroring auto failover, isn't really auto failover if I have to
manually run this script whenever it fails over.
1) Is there a way to add to the failover script that is run whenever there
is an automatic failover?
2) Do I need to recode my app to just use windows authentication?
3) Are there any other solutions to this problem?Drop the [SQL] logins on your mirror.
When you run the CREATE LOGIN script on that server to create the logins,
make sure that you specify the SID of that particular user that is within
the primary server.
--
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:030929C7-7486-4E19-B8E6-32074DD4E26F@.microsoft.com...
>I have mirroring set up and working on a database that is used for an
>ASP.NET
> web application. The web appliation uses SQL Authentication to connect to
> the
> database. The mirroring is set up with a witness, and the connection
> string
> includes "Failover Partner=myfailoverserver;".
> When I'm running on the primary database, the app works great. When I do a
> manual failover, the app crashes with the following error: "Cannot open
> database "mydb" requested by the login. The login failed.
> Login failed for user 'myuser'".
> If I then run "EXEC sp_change_users_login 'Update_One', 'myuser',
> 'myuser';"
> everything works fine again, untill I fail over back tot he primary
> server.
> Then I have to run sp_change_users_login again.
> The mirroring auto failover, isn't really auto failover if I have to
> manually run this script whenever it fails over.
> 1) Is there a way to add to the failover script that is run whenever there
> is an automatic failover?
> 2) Do I need to recode my app to just use windows authentication?
> 3) Are there any other solutions to this problem?