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?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...
>
>|||select name, sid from master..syslogins
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82944D2B-DD3F-44F7-A7D7-495C446474B6@.microsoft.com...[vbcol=seagreen]
> How do I get the SIDs for logins on the primary?
> "Keith Kratochvil" wrote:
>|||Google for sp_help_revlogin. Run this periodically on the source server so y
ou 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: Cann
ot
> open database 'mydb' requested by the login. The login failed. Login faile
d
> 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, the
n 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 usi
ng
> 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?
>
Showing posts with label authentication. Show all posts
Showing posts with label authentication. Show all posts
Friday, March 30, 2012
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?
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...
>
>
|||select name, sid from master..syslogins
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82944D2B-DD3F-44F7-A7D7-495C446474B6@.microsoft.com...[vbcol=seagreen]
> How do I get the SIDs for logins on the primary?
> "Keith Kratochvil" wrote:
|||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?
>
sql
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?
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...
>
>
|||select name, sid from master..syslogins
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82944D2B-DD3F-44F7-A7D7-495C446474B6@.microsoft.com...[vbcol=seagreen]
> How do I get the SIDs for logins on the primary?
> "Keith Kratochvil" wrote:
|||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?
>
sql
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?
>
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?
>
Wednesday, March 28, 2012
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?
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?
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?
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?
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.NE
T
web application. The web appliation uses SQL Authentication to connect to th
e
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?
T
web application. The web appliation uses SQL Authentication to connect to th
e
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?
Friday, March 23, 2012
Minimum password length for SQL Server Authentication and special character – how to set?
Minimum password length for SQL Server Authentication and special
character – how to set?
I need to set that minimum password length for 8 char and password
should include special character in SQL (2000) Server Authentication –
is it possible?
How to set it?
Hello sp,
> Minimum password length for SQL Server Authentication and special
> character – how to set?
> I need to set that minimum password length for 8 char and password
> should include special character in SQL (2000) Server Authentication –
> is it possible?
Nope, this feature isn't supported until SQL 2005.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hello,
Password and user policies are incorporated only from SQL 2005. In SQL 2000
you may need to use the Windows authentication to get that behaviour.
Thanks
Hari
"sp" <kofa@.noemail.noemail> wrote in message
news:%23VakivYcHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Minimum password length for SQL Server Authentication and special
> character - how to set?
> I need to set that minimum password length for 8 char and password should
> include special character in SQL (2000) Server Authentication - is it
> possible?
> How to set it?
character – how to set?
I need to set that minimum password length for 8 char and password
should include special character in SQL (2000) Server Authentication –
is it possible?
How to set it?
Hello sp,
> Minimum password length for SQL Server Authentication and special
> character – how to set?
> I need to set that minimum password length for 8 char and password
> should include special character in SQL (2000) Server Authentication –
> is it possible?
Nope, this feature isn't supported until SQL 2005.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hello,
Password and user policies are incorporated only from SQL 2005. In SQL 2000
you may need to use the Windows authentication to get that behaviour.
Thanks
Hari
"sp" <kofa@.noemail.noemail> wrote in message
news:%23VakivYcHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Minimum password length for SQL Server Authentication and special
> character - how to set?
> I need to set that minimum password length for 8 char and password should
> include special character in SQL (2000) Server Authentication - is it
> possible?
> How to set it?
Wednesday, March 21, 2012
Minimium persmissions needed to Restore a DB
Hi,
I'm trying to set a domain group who connect using Windows authentication,
so that they can restore a database, i have read books online and do not
want to add them to the sysadmin and dbcreator fixed server roles.
I can see that the dbcreator fixed server role has a permission 'RESTORE
DATABASE', is it possible to grant this permission on a single database on
the server alone ?
Thanks in advance
PatNo...you can't assign this for a single database.
-Sue
On Tue, 23 Sep 2003 09:04:42 +0100, "pmcg"
<pat@.nospam.nospam.com> wrote:
>Hi,
>I'm trying to set a domain group who connect using Windows authentication,
>so that they can restore a database, i have read books online and do not
>want to add them to the sysadmin and dbcreator fixed server roles.
>I can see that the dbcreator fixed server role has a permission 'RESTORE
>DATABASE', is it possible to grant this permission on a single database on
>the server alone ?
>
>Thanks in advance
>Pat
>
I'm trying to set a domain group who connect using Windows authentication,
so that they can restore a database, i have read books online and do not
want to add them to the sysadmin and dbcreator fixed server roles.
I can see that the dbcreator fixed server role has a permission 'RESTORE
DATABASE', is it possible to grant this permission on a single database on
the server alone ?
Thanks in advance
PatNo...you can't assign this for a single database.
-Sue
On Tue, 23 Sep 2003 09:04:42 +0100, "pmcg"
<pat@.nospam.nospam.com> wrote:
>Hi,
>I'm trying to set a domain group who connect using Windows authentication,
>so that they can restore a database, i have read books online and do not
>want to add them to the sysadmin and dbcreator fixed server roles.
>I can see that the dbcreator fixed server role has a permission 'RESTORE
>DATABASE', is it possible to grant this permission on a single database on
>the server alone ?
>
>Thanks in advance
>Pat
>
Saturday, February 25, 2012
Migration Assistant For Access Can''t Find SQL Database?
I've installed SQL 2005 Express Addition which created a default database using Windows Authentication -- MachineName/SQLEXPRES. I want to convert some access databases to sql server in the migration assistant, however, when I try to connect the migration assistant to the default database I can't find the database in the drop down box wizard? I'm perplexed? Please shed some light or point me to some articles that will help. Thanks.
It is not practical to use standard Express to migrate Access so see if you can use the one with Management Studio instead, this will give you control of all the steps. Hope this helps.
http://msdn.microsoft.com/vstudio/express/sql/download/
|||
Yes, Management Studio is the one I used. I am able to connect via Management Studio, however, the Migration Assistant can't locate it? Looking for other suggestions? Thanks.
|||Try the eval good for 6 months, you could buy the developer for under $40 online if you have the ram this can help you move Access if all you have is tables and queries. If you have VBA you may need professional help. I have seen Access migration taking six months or more because it lets users add stuff SQL Server will not accept and it uses limited data types.
http://www.microsoft.com/sql/downloads/trial-software.mspx
|||
I'm not sure I understand what you mean by "default database" in this context. SQL Server does not create any default databases when you install it.
In my use of SSMS I find that the drop down box that show the database name does not actually show the list of databases in the server because it has not actually connected to the server yet, so it can not list the databases. You can either specify a new database to be created or specify an existing database to open once you connect, but you need to type the name of the database, it won't populate the list for you.
Regards,
Mike
Subscribe to:
Posts (Atom)