Mirroring Failover - Have to run sp_change_users_login

I have mirroring configured (with a witness server) on a database that is
being used by an ASP.NET application. The app uses SQL Authentication, and
the conneciton string includes the Failover Partner statement.
Whenever the database fails over, the app throws the following error: Cannot
open database 'mydb' requested by the login. The login failed. Login failed
for user 'myuser'.
Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
the problem, but only until I fail back over the the primary database, then I
have to run the script again. Every time I fail over (in either direction),
the sp_change_users_login needs to be run again before apps connection using
SQL Authentication will work.
Auto failover with mirroring, isn't really auto failover if I have to run
this script every time.
1) Is there a way to insert a custom script into the autofailover process?
2) Do I need to convert all applications using this db to use Windows Auth
only?
3) Are there any other solutions to this problem?... the first one gave me an error. I didn't think it had gone through.|||... the first one gave me an error. I didn't think it had gone through.|||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:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
>I have mirroring configured (with a witness server) on a database that is
> being used by an ASP.NET application. The app uses SQL Authentication, and
> the conneciton string includes the Failover Partner statement.
> Whenever the database fails over, the app throws the following error:
> Cannot
> open database 'mydb' requested by the login. The login failed. Login
> failed
> for user 'myuser'.
> Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
> the problem, but only until I fail back over the the primary database,
> then I
> have to run the script again. Every time I fail over (in either
> direction),
> the sp_change_users_login needs to be run again before apps connection
> using
> SQL Authentication will work.
> Auto failover with mirroring, isn't really auto failover if I have to run
> this script every time.
> 1) Is there a way to insert a custom script into the autofailover process?
> 2) Do I need to convert all applications using this db to use Windows Auth
> only?
> 3) Are there any other solutions to this problem?
>|||How do I get the SIDs for logins on the primary?
"Keith Kratochvil" wrote:
> 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:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
> >I have mirroring configured (with a witness server) on a database that is
> > being used by an ASP.NET application. The app uses SQL Authentication, and
> > the conneciton string includes the Failover Partner statement.
> >
> > Whenever the database fails over, the app throws the following error:
> > Cannot
> > open database 'mydb' requested by the login. The login failed. Login
> > failed
> > for user 'myuser'.
> >
> > Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
> > the problem, but only until I fail back over the the primary database,
> > then I
> > have to run the script again. Every time I fail over (in either
> > direction),
> > the sp_change_users_login needs to be run again before apps connection
> > using
> > SQL Authentication will work.
> >
> > Auto failover with mirroring, isn't really auto failover if I have to run
> > this script every time.
> >
> > 1) Is there a way to insert a custom script into the autofailover process?
> >
> > 2) Do I need to convert all applications using this db to use Windows Auth
> > only?
> >
> > 3) Are there any other solutions to this problem?
> >
>
>|||select name, sid from master..syslogins
--
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82944D2B-DD3F-44F7-A7D7-495C446474B6@.microsoft.com...
> How do I get the SIDs for logins on the primary?
> "Keith Kratochvil" wrote:
>> 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:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
>> >I have mirroring configured (with a witness server) on a database that
>> >is
>> > being used by an ASP.NET application. The app uses SQL Authentication,
>> > and
>> > the conneciton string includes the Failover Partner statement.
>> >
>> > Whenever the database fails over, the app throws the following error:
>> > Cannot
>> > open database 'mydb' requested by the login. The login failed. Login
>> > failed
>> > for user 'myuser'.
>> >
>> > Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser'
>> > fixes
>> > the problem, but only until I fail back over the the primary database,
>> > then I
>> > have to run the script again. Every time I fail over (in either
>> > direction),
>> > the sp_change_users_login needs to be run again before apps connection
>> > using
>> > SQL Authentication will work.
>> >
>> > Auto failover with mirroring, isn't really auto failover if I have to
>> > run
>> > this script every time.
>> >
>> > 1) Is there a way to insert a custom script into the autofailover
>> > process?
>> >
>> > 2) Do I need to convert all applications using this db to use Windows
>> > Auth
>> > only?
>> >
>> > 3) Are there any other solutions to this problem?
>> >
>>|||Google for sp_help_revlogin. Run this periodically on the source server so you have a script
containing the proper SID for each login.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
>I have mirroring configured (with a witness server) on a database that is
> being used by an ASP.NET application. The app uses SQL Authentication, and
> the conneciton string includes the Failover Partner statement.
> Whenever the database fails over, the app throws the following error: Cannot
> open database 'mydb' requested by the login. The login failed. Login failed
> for user 'myuser'.
> Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
> the problem, but only until I fail back over the the primary database, then I
> have to run the script again. Every time I fail over (in either direction),
> the sp_change_users_login needs to be run again before apps connection using
> SQL Authentication will work.
> Auto failover with mirroring, isn't really auto failover if I have to run
> this script every time.
> 1) Is there a way to insert a custom script into the autofailover process?
> 2) Do I need to convert all applications using this db to use Windows Auth
> only?
> 3) Are there any other solutions to this problem?
>