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 application. Show all posts
Showing posts with label application. 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?
>
Mirroring error filling up event log
Hi,
I'm getting this error in the application event log roughly every second:
Database Mirroring login attempt failed with error: 'Connection handshake
failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
This is in the event log of the primary mirror sever. The setup is 2
identical sql servers (SP1), in the same domain, using the same domain
account for all services. I've confirmed the sql service accounts are local
admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
anyone shed some light on this?
Thanks in advance.
Any help here Microsoft?
"sqlboy2000" wrote:
> Hi,
> I'm getting this error in the application event log roughly every second:
> Database Mirroring login attempt failed with error: 'Connection handshake
> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
> This is in the event log of the primary mirror sever. The setup is 2
> identical sql servers (SP1), in the same domain, using the same domain
> account for all services. I've confirmed the sql service accounts are local
> admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
> anyone shed some light on this?
> Thanks in advance.
|||SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
covers, and this message is logged by SSB as a result of an attempt to
connect by a mirroring session. You should either set up security correctly
or disable the mirroring session from the mentioned host (the IP is in the
error log, but apparently the IP was truncated in your post).
Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
the Setting Up Database Mirroring topic.
Don Vilen
This posting is provided "AS IS" with no warranties, and confers no rights
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...[vbcol=seagreen]
> Any help here Microsoft?
> "sqlboy2000" wrote:
|||Security was setup correctly, this post wasn't accurate.
I'm going to post the actual problem now that I understand what's happening.
"Don Vilen [MSFT]" wrote:
> SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
> covers, and this message is logged by SSB as a result of an attempt to
> connect by a mirroring session. You should either set up security correctly
> or disable the mirroring session from the mentioned host (the IP is in the
> error log, but apparently the IP was truncated in your post).
> Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
> the Setting Up Database Mirroring topic.
> --
> Don Vilen
> This posting is provided "AS IS" with no warranties, and confers no rights
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
>
I'm getting this error in the application event log roughly every second:
Database Mirroring login attempt failed with error: 'Connection handshake
failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
This is in the event log of the primary mirror sever. The setup is 2
identical sql servers (SP1), in the same domain, using the same domain
account for all services. I've confirmed the sql service accounts are local
admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
anyone shed some light on this?
Thanks in advance.
Any help here Microsoft?
"sqlboy2000" wrote:
> Hi,
> I'm getting this error in the application event log roughly every second:
> Database Mirroring login attempt failed with error: 'Connection handshake
> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
> This is in the event log of the primary mirror sever. The setup is 2
> identical sql servers (SP1), in the same domain, using the same domain
> account for all services. I've confirmed the sql service accounts are local
> admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
> anyone shed some light on this?
> Thanks in advance.
|||SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
covers, and this message is logged by SSB as a result of an attempt to
connect by a mirroring session. You should either set up security correctly
or disable the mirroring session from the mentioned host (the IP is in the
error log, but apparently the IP was truncated in your post).
Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
the Setting Up Database Mirroring topic.
Don Vilen
This posting is provided "AS IS" with no warranties, and confers no rights
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...[vbcol=seagreen]
> Any help here Microsoft?
> "sqlboy2000" wrote:
|||Security was setup correctly, this post wasn't accurate.
I'm going to post the actual problem now that I understand what's happening.
"Don Vilen [MSFT]" wrote:
> SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
> covers, and this message is logged by SSB as a result of an attempt to
> connect by a mirroring session. You should either set up security correctly
> or disable the mirroring session from the mentioned host (the IP is in the
> error log, but apparently the IP was truncated in your post).
> Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
> the Setting Up Database Mirroring topic.
> --
> Don Vilen
> This posting is provided "AS IS" with no warranties, and confers no rights
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
>
Mirroring error filling up event log
Hi,
I'm getting this error in the application event log roughly every second:
Database Mirroring login attempt failed with error: 'Connection handshake
failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
This is in the event log of the primary mirror sever. The setup is 2
identical sql servers (SP1), in the same domain, using the same domain
account for all services. I've confirmed the sql service accounts are local
admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
anyone shed some light on this?
Thanks in advance.Any help here Microsoft?
"sqlboy2000" wrote:
> Hi,
> I'm getting this error in the application event log roughly every second:
> Database Mirroring login attempt failed with error: 'Connection handshake
> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
> This is in the event log of the primary mirror sever. The setup is 2
> identical sql servers (SP1), in the same domain, using the same domain
> account for all services. I've confirmed the sql service accounts are local
> admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
> anyone shed some light on this?
> Thanks in advance.|||SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
covers, and this message is logged by SSB as a result of an attempt to
connect by a mirroring session. You should either set up security correctly
or disable the mirroring session from the mentioned host (the IP is in the
error log, but apparently the IP was truncated in your post).
Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
the Setting Up Database Mirroring topic.
--
Don Vilen
This posting is provided "AS IS" with no warranties, and confers no rights
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
> Any help here Microsoft?
> "sqlboy2000" wrote:
>> Hi,
>> I'm getting this error in the application event log roughly every second:
>> Database Mirroring login attempt failed with error: 'Connection handshake
>> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt
>> failed).
>> This is in the event log of the primary mirror sever. The setup is 2
>> identical sql servers (SP1), in the same domain, using the same domain
>> account for all services. I've confirmed the sql service accounts are
>> local
>> admins on both boxes. So I'm not sure what 'login attempt' is failing.
>> Can
>> anyone shed some light on this?
>> Thanks in advance.|||Security was setup correctly, this post wasn't accurate.
I'm going to post the actual problem now that I understand what's happening.
"Don Vilen [MSFT]" wrote:
> SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
> covers, and this message is logged by SSB as a result of an attempt to
> connect by a mirroring session. You should either set up security correctly
> or disable the mirroring session from the mentioned host (the IP is in the
> error log, but apparently the IP was truncated in your post).
> Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
> the Setting Up Database Mirroring topic.
> --
> Don Vilen
> This posting is provided "AS IS" with no warranties, and confers no rights
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
> > Any help here Microsoft?
> >
> > "sqlboy2000" wrote:
> >
> >> Hi,
> >> I'm getting this error in the application event log roughly every second:
> >>
> >> Database Mirroring login attempt failed with error: 'Connection handshake
> >> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt
> >> failed).
> >>
> >> This is in the event log of the primary mirror sever. The setup is 2
> >> identical sql servers (SP1), in the same domain, using the same domain
> >> account for all services. I've confirmed the sql service accounts are
> >> local
> >> admins on both boxes. So I'm not sure what 'login attempt' is failing.
> >> Can
> >> anyone shed some light on this?
> >>
> >> Thanks in advance.
>
I'm getting this error in the application event log roughly every second:
Database Mirroring login attempt failed with error: 'Connection handshake
failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
This is in the event log of the primary mirror sever. The setup is 2
identical sql servers (SP1), in the same domain, using the same domain
account for all services. I've confirmed the sql service accounts are local
admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
anyone shed some light on this?
Thanks in advance.Any help here Microsoft?
"sqlboy2000" wrote:
> Hi,
> I'm getting this error in the application event log roughly every second:
> Database Mirroring login attempt failed with error: 'Connection handshake
> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
> This is in the event log of the primary mirror sever. The setup is 2
> identical sql servers (SP1), in the same domain, using the same domain
> account for all services. I've confirmed the sql service accounts are local
> admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
> anyone shed some light on this?
> Thanks in advance.|||SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
covers, and this message is logged by SSB as a result of an attempt to
connect by a mirroring session. You should either set up security correctly
or disable the mirroring session from the mentioned host (the IP is in the
error log, but apparently the IP was truncated in your post).
Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
the Setting Up Database Mirroring topic.
--
Don Vilen
This posting is provided "AS IS" with no warranties, and confers no rights
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
> Any help here Microsoft?
> "sqlboy2000" wrote:
>> Hi,
>> I'm getting this error in the application event log roughly every second:
>> Database Mirroring login attempt failed with error: 'Connection handshake
>> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt
>> failed).
>> This is in the event log of the primary mirror sever. The setup is 2
>> identical sql servers (SP1), in the same domain, using the same domain
>> account for all services. I've confirmed the sql service accounts are
>> local
>> admins on both boxes. So I'm not sure what 'login attempt' is failing.
>> Can
>> anyone shed some light on this?
>> Thanks in advance.|||Security was setup correctly, this post wasn't accurate.
I'm going to post the actual problem now that I understand what's happening.
"Don Vilen [MSFT]" wrote:
> SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
> covers, and this message is logged by SSB as a result of an attempt to
> connect by a mirroring session. You should either set up security correctly
> or disable the mirroring session from the mentioned host (the IP is in the
> error log, but apparently the IP was truncated in your post).
> Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
> the Setting Up Database Mirroring topic.
> --
> Don Vilen
> This posting is provided "AS IS" with no warranties, and confers no rights
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
> > Any help here Microsoft?
> >
> > "sqlboy2000" wrote:
> >
> >> Hi,
> >> I'm getting this error in the application event log roughly every second:
> >>
> >> Database Mirroring login attempt failed with error: 'Connection handshake
> >> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt
> >> failed).
> >>
> >> This is in the event log of the primary mirror sever. The setup is 2
> >> identical sql servers (SP1), in the same domain, using the same domain
> >> account for all services. I've confirmed the sql service accounts are
> >> local
> >> admins on both boxes. So I'm not sure what 'login attempt' is failing.
> >> Can
> >> anyone shed some light on this?
> >>
> >> Thanks in advance.
>
Mirroring and orphaned users
Hi there!
There is still a problem with mirrored (mapped) SQL Users. If you mirror a database where an application connects with an sql user, the mapping (login / user) will be lost on the mirror server. After a failover occurs, it it not possible to log onto the new principal database because the database use will be an orphaned user and has to be remapped to the login (using sp_change_users_login 'update_one', 'user', 'user').
Is there any chance to do it in a system trigger? What is the firing event after the failover occurs? I've tried something like following, but it doesn't fire.
alter TRIGGER map_orphaned_users
ON ALL SERVER
FOR ALTER_DATABASE
AS
execute sp_change_users_login 'update_one', 'easyris_41', 'easyris_41';
Someone an idea how to automate these usermapping after failover on the (new) principal server?
Based on:
http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx#EDBAC
you can be set up to be notified (or run a job or particular sp) when the server becomes the principal.
Thanks,
mark
|||Hmm, maybe thats a workaround.
But in my mind it doesn't confirm with the requirement of high availability with automatic failover. Furthermore, clients application could run into another exception (Login fails or something like that), what has to be adapted in applications what are designed for high avilibility.
Otherway all the stuff works with domain users. So it seems like a real bug. I can't see any conceptional reason for this behavior. Hope that will be fixed in SP2...
Torsten
|||
OK, I am going to try to write a bit.
1. I completely agree that from a customer's point of view, if when using sql logins, if the database fails over and the clients cannot get to the database (for whatever reason. Here it is a mixed mapping from login on a different server to a user in a "new" database ), then the system is not "highly available." So, we haven't made it easy to use mirroring in certain scenarios. I may try to create a script that solves this problem this weekend.
2. On the other hand, this problem has been here at least since 7.0. In log shipping, backup/restore, and attach/detach this problem of moving the database to a different server where the meta data in master and msdb are different on different servers. The problem is that we have made moving a database to a different server so much easier that the problems that were small earlier are becoming bigger.
So, this really isn't a bug IMHO, but it is unexpected behavior (which is still bad from a customer's point of view).
Thanks,
Mark
|||I am just starting to look into mirroring and had run across this same problem. I have not had a chance to look into this but supposedly there is a task in SSIS that allows you to transfer logins from one server to another.|||
Hi guys,
I've asked MS about using sqlusers on a mirrored database and got this reply from Hugo Nunes, support Engineer at Microsoft.
In order to avoid orphaned users on the mirror database you’ll have to copy the logins corresponding to the database users, to the standby server. The copy must be made before restoring the database on the standby server. You’ll also need to copy the user with his SID. The Transfer Logins Task from SSIS performs that task for you as long you set the CopySids property to true.
It works.
|||
yes. but it doesn't solve the issue of default database for a login,
which leads to the same issue - login does not work.
|||Hi to All,
We are currently having issues like that before, the fix that we do is adding a short dynamic script that will remove orphaned user by executing sp_change_users_login 'update_one', '<user1>','<user1>' on the mirrored database everytime a switch occur.
hope this helps!
regards,
|||Hi Anders,
Did you ever got this to work? Do you know of any documents that walk you thought on how to do this? I am new with SSIS.
Thanks
Rick
|||Hi Rick,
Yes, it's been up and running for almost a year now.
Im sorry i dont have a walk through for you but the SSIS bit is pretty straight forward.
These documents should get you started.
Transfer Logins Task
http://msdn2.microsoft.com/en-us/library/ms137870.aspx
Set Task Properties
http://msdn2.microsoft.com/en-us/library/ms139733.aspx
/Anders
Mirroring and orphaned users
Hi there!
There is still a problem with mirrored (mapped) SQL Users. If you mirror a database where an application connects with an sql user, the mapping (login / user) will be lost on the mirror server. After a failover occurs, it it not possible to log onto the new principal database because the database use will be an orphaned user and has to be remapped to the login (using sp_change_users_login 'update_one', 'user', 'user').
Is there any chance to do it in a system trigger? What is the firing event after the failover occurs? I've tried something like following, but it doesn't fire.
alter TRIGGER map_orphaned_users
ON ALL SERVER
FOR ALTER_DATABASE
AS
execute sp_change_users_login 'update_one', 'easyris_41', 'easyris_41';
Someone an idea how to automate these usermapping after failover on the (new) principal server?
Based on:
http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx#EDBAC
you can be set up to be notified (or run a job or particular sp) when the server becomes the principal.
Thanks,
mark
|||Hmm, maybe thats a workaround.
But in my mind it doesn't confirm with the requirement of high availability with automatic failover. Furthermore, clients application could run into another exception (Login fails or something like that), what has to be adapted in applications what are designed for high avilibility.
Otherway all the stuff works with domain users. So it seems like a real bug. I can't see any conceptional reason for this behavior. Hope that will be fixed in SP2...
Torsten
|||
OK, I am going to try to write a bit.
1. I completely agree that from a customer's point of view, if when using sql logins, if the database fails over and the clients cannot get to the database (for whatever reason. Here it is a mixed mapping from login on a different server to a user in a "new" database ), then the system is not "highly available." So, we haven't made it easy to use mirroring in certain scenarios. I may try to create a script that solves this problem this weekend.
2. On the other hand, this problem has been here at least since 7.0. In log shipping, backup/restore, and attach/detach this problem of moving the database to a different server where the meta data in master and msdb are different on different servers. The problem is that we have made moving a database to a different server so much easier that the problems that were small earlier are becoming bigger.
So, this really isn't a bug IMHO, but it is unexpected behavior (which is still bad from a customer's point of view).
Thanks,
Mark
|||I am just starting to look into mirroring and had run across this same problem. I have not had a chance to look into this but supposedly there is a task in SSIS that allows you to transfer logins from one server to another.|||
Hi guys,
I've asked MS about using sqlusers on a mirrored database and got this reply from Hugo Nunes, support Engineer at Microsoft.
In order to avoid orphaned users on the mirror database you’ll have to copy the logins corresponding to the database users, to the standby server. The copy must be made before restoring the database on the standby server. You’ll also need to copy the user with his SID. The Transfer Logins Task from SSIS performs that task for you as long you set the CopySids property to true.
It works.
|||
yes. but it doesn't solve the issue of default database for a login,
which leads to the same issue - login does not work.
|||Hi to All,
We are currently having issues like that before, the fix that we do is adding a short dynamic script that will remove orphaned user by executing sp_change_users_login 'update_one', '<user1>','<user1>' on the mirrored database everytime a switch occur.
hope this helps!
regards,
|||Hi Anders,
Did you ever got this to work? Do you know of any documents that walk you thought on how to do this? I am new with SSIS.
Thanks
Rick
|||Hi Rick,
Yes, it's been up and running for almost a year now.
Im sorry i dont have a walk through for you but the SSIS bit is pretty straight forward.
These documents should get you started.
Transfer Logins Task
http://msdn2.microsoft.com/en-us/library/ms137870.aspx
Set Task Properties
http://msdn2.microsoft.com/en-us/library/ms139733.aspx
/Anders
Mirroring and orphaned users
Hi there!
There is still a problem with mirrored (mapped) SQL Users. If you mirror a database where an application connects with an sql user, the mapping (login / user) will be lost on the mirror server. After a failover occurs, it it not possible to log onto the new principal database because the database use will be an orphaned user and has to be remapped to the login (using sp_change_users_login 'update_one', 'user', 'user').
Is there any chance to do it in a system trigger? What is the firing event after the failover occurs? I've tried something like following, but it doesn't fire.
alter TRIGGER map_orphaned_users
ON ALL SERVER
FOR ALTER_DATABASE
AS
execute sp_change_users_login 'update_one', 'easyris_41', 'easyris_41';
Someone an idea how to automate these usermapping after failover on the (new) principal server?
Based on:
http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx#EDBAC
you can be set up to be notified (or run a job or particular sp) when the server becomes the principal.
Thanks,
mark
|||Hmm, maybe thats a workaround.
But in my mind it doesn't confirm with the requirement of high availability with automatic failover. Furthermore, clients application could run into another exception (Login fails or something like that), what has to be adapted in applications what are designed for high avilibility.
Otherway all the stuff works with domain users. So it seems like a real bug. I can't see any conceptional reason for this behavior. Hope that will be fixed in SP2...
Torsten
|||
OK, I am going to try to write a bit.
1. I completely agree that from a customer's point of view, if when using sql logins, if the database fails over and the clients cannot get to the database (for whatever reason. Here it is a mixed mapping from login on a different server to a user in a "new" database ), then the system is not "highly available." So, we haven't made it easy to use mirroring in certain scenarios. I may try to create a script that solves this problem this weekend.
2. On the other hand, this problem has been here at least since 7.0. In log shipping, backup/restore, and attach/detach this problem of moving the database to a different server where the meta data in master and msdb are different on different servers. The problem is that we have made moving a database to a different server so much easier that the problems that were small earlier are becoming bigger.
So, this really isn't a bug IMHO, but it is unexpected behavior (which is still bad from a customer's point of view).
Thanks,
Mark
|||I am just starting to look into mirroring and had run across this same problem. I have not had a chance to look into this but supposedly there is a task in SSIS that allows you to transfer logins from one server to another.|||
Hi guys,
I've asked MS about using sqlusers on a mirrored database and got this reply from Hugo Nunes, support Engineer at Microsoft.
In order to avoid orphaned users on the mirror database you’ll have to copy the logins corresponding to the database users, to the standby server. The copy must be made before restoring the database on the standby server. You’ll also need to copy the user with his SID. The Transfer Logins Task from SSIS performs that task for you as long you set the CopySids property to true.
It works.
|||
yes. but it doesn't solve the issue of default database for a login,
which leads to the same issue - login does not work.
|||Hi to All,
We are currently having issues like that before, the fix that we do is adding a short dynamic script that will remove orphaned user by executing sp_change_users_login 'update_one', '<user1>','<user1>' on the mirrored database everytime a switch occur.
hope this helps!
regards,
|||Hi Anders,
Did you ever got this to work? Do you know of any documents that walk you thought on how to do this? I am new with SSIS.
Thanks
Rick
|||Hi Rick,
Yes, it's been up and running for almost a year now.
Im sorry i dont have a walk through for you but the SSIS bit is pretty straight forward.
These documents should get you started.
Transfer Logins Task
http://msdn2.microsoft.com/en-us/library/ms137870.aspx
Set Task Properties
http://msdn2.microsoft.com/en-us/library/ms139733.aspx
/Anders
Mirroring and orphaned users
Hi there!
There is still a problem with mirrored (mapped) SQL Users. If you mirror a database where an application connects with an sql user, the mapping (login / user) will be lost on the mirror server. After a failover occurs, it it not possible to log onto the new principal database because the database use will be an orphaned user and has to be remapped to the login (using sp_change_users_login 'update_one', 'user', 'user').
Is there any chance to do it in a system trigger? What is the firing event after the failover occurs? I've tried something like following, but it doesn't fire.
alter TRIGGER map_orphaned_users
ON ALL SERVER
FOR ALTER_DATABASE
AS
execute sp_change_users_login 'update_one', 'easyris_41', 'easyris_41';
Someone an idea how to automate these usermapping after failover on the (new) principal server?
Based on:
http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx#EDBAC
you can be set up to be notified (or run a job or particular sp) when the server becomes the principal.
Thanks,
mark
|||Hmm, maybe thats a workaround.
But in my mind it doesn't confirm with the requirement of high availability with automatic failover. Furthermore, clients application could run into another exception (Login fails or something like that), what has to be adapted in applications what are designed for high avilibility.
Otherway all the stuff works with domain users. So it seems like a real bug. I can't see any conceptional reason for this behavior. Hope that will be fixed in SP2...
Torsten
|||
OK, I am going to try to write a bit.
1. I completely agree that from a customer's point of view, if when using sql logins, if the database fails over and the clients cannot get to the database (for whatever reason. Here it is a mixed mapping from login on a different server to a user in a "new" database ), then the system is not "highly available." So, we haven't made it easy to use mirroring in certain scenarios. I may try to create a script that solves this problem this weekend.
2. On the other hand, this problem has been here at least since 7.0. In log shipping, backup/restore, and attach/detach this problem of moving the database to a different server where the meta data in master and msdb are different on different servers. The problem is that we have made moving a database to a different server so much easier that the problems that were small earlier are becoming bigger.
So, this really isn't a bug IMHO, but it is unexpected behavior (which is still bad from a customer's point of view).
Thanks,
Mark
|||I am just starting to look into mirroring and had run across this same problem. I have not had a chance to look into this but supposedly there is a task in SSIS that allows you to transfer logins from one server to another.|||
Hi guys,
I've asked MS about using sqlusers on a mirrored database and got this reply from Hugo Nunes, support Engineer at Microsoft.
In order to avoid orphaned users on the mirror database you’ll have to copy the logins corresponding to the database users, to the standby server. The copy must be made before restoring the database on the standby server. You’ll also need to copy the user with his SID. The Transfer Logins Task from SSIS performs that task for you as long you set the CopySids property to true.
It works.
|||
yes. but it doesn't solve the issue of default database for a login,
which leads to the same issue - login does not work.
|||Hi to All,
We are currently having issues like that before, the fix that we do is adding a short dynamic script that will remove orphaned user by executing sp_change_users_login 'update_one', '<user1>','<user1>' on the mirrored database everytime a switch occur.
hope this helps!
regards,
|||Hi Anders,
Did you ever got this to work? Do you know of any documents that walk you thought on how to do this? I am new with SSIS.
Thanks
Rick
|||Hi Rick,
Yes, it's been up and running for almost a year now.
Im sorry i dont have a walk through for you but the SSIS bit is pretty straight forward.
These documents should get you started.
Transfer Logins Task
http://msdn2.microsoft.com/en-us/library/ms137870.aspx
Set Task Properties
http://msdn2.microsoft.com/en-us/library/ms139733.aspx
/Anders
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?
Mirroring
We are moving our asp application from SQL2k to SQL2k5. I implemented
database mirroring for my critical databases, which is setup & running. I
modified the connection string in the Global.asa to point to my new servers.
When both SQL servers are up & running I connect to the primary database.
However, when the server hosting the primary database is stopped, I get an
error regarding the connection string.
Is this an application error that needs to be resolved? What do I tell the
developers to do?
Is this a problem with the SQL connection string? Does anyone have working
examples?
Also, I am using a witness server.
Does anyone have any test procedures/scripts/scenarios to insure mirroring
is working as it should. I thought I could stop SQL on a server run a test,
restart SQL & then check the databases.
I'm ok at server & database admin but poor at database programming. And
brand new to SQL2k5 mirroring.
Thanks"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> We are moving our asp application from SQL2k to SQL2k5. I implemented
> database mirroring for my critical databases, which is setup & running. I
> modified the connection string in the Global.asa to point to my new
> servers.
> When both SQL servers are up & running I connect to the primary database.
> However, when the server hosting the primary database is stopped, I get an
> error regarding the connection string.
> Is this an application error that needs to be resolved? What do I tell the
> developers to do?
> Is this a problem with the SQL connection string? Does anyone have working
> examples?
> Also, I am using a witness server.
> Does anyone have any test procedures/scripts/scenarios to insure mirroring
> is working as it should. I thought I could stop SQL on a server run a
> test,
> restart SQL & then check the databases.
> I'm ok at server & database admin but poor at database programming. And
> brand new to SQL2k5 mirroring.
>
Does your connection string specify both the principal and the mirror? If
not, failover will work, but startup when the principal is down will not.
See:
System.Data.SqlConnection.ConnectionString Property
Failover Partner
N/A
The name of the failover partner server where database mirroring is
configured.
The Failover Partner keyword is not supported by .NET Framework version 1.0
or 1.1.
http://msdn2.microsoft.com/en-us/li...tionstring.aspx
David|||"David Browne" wrote:
>
> "BigSam" <BigSam@.discussions.microsoft.com> wrote in message
> news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> Does your connection string specify both the principal and the mirror? If
> not, failover will work, but startup when the principal is down will not.
> See:
> System.Data.SqlConnection.ConnectionString Property
> Failover Partner
> N/A
> The name of the failover partner server where database mirroring is
> configured.
> The Failover Partner keyword is not supported by .NET Framework version 1.
0
> or 1.1.
> http://msdn2.microsoft.com/en-us/li...tionstring.aspx
>
> David
> This is the connection string set in the Global.asa:
Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
Also, we're not using asp.net - just plain old ASP.|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:E89D67CA-4BA6-4A87-B053-F3F10BC1D3AC@.microsoft.com...
>
> "David Browne" wrote:
>
> Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
> Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
> Also, we're not using asp.net - just plain old ASP.
>
The OleDb provider you're using isn't mirroring-aware. The SQLNCLI the new
SqlServer Native Client provider, if it works correctly with your
application. See:
Using ADO with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130978.aspx
If you stick with SQLOLEDB, then you can use DNS or BigIP to manually switch
the traffic between servers, or just change your config and boune the site.
David|||Thanks for your reply.
Yesterday & was able to determine that without mirroring I could connect to
each server with the SQL login. However, with mirroring enabled I could only
connect to SQL1 with the SQL login. I began to suspect the SID & found this
article: http://support.microsoft.com/default.aspx/kb/918992/
"Magas" wrote:
[vbcol=seagreen]
> If you use SQL Server login to connect to the mirror database - make sure
> logins on both mirror servers have the same SID. The easiest way to do it
-
> copy login from one server to another using SSIS package.
>
> "BigSam" wrote:
>
database mirroring for my critical databases, which is setup & running. I
modified the connection string in the Global.asa to point to my new servers.
When both SQL servers are up & running I connect to the primary database.
However, when the server hosting the primary database is stopped, I get an
error regarding the connection string.
Is this an application error that needs to be resolved? What do I tell the
developers to do?
Is this a problem with the SQL connection string? Does anyone have working
examples?
Also, I am using a witness server.
Does anyone have any test procedures/scripts/scenarios to insure mirroring
is working as it should. I thought I could stop SQL on a server run a test,
restart SQL & then check the databases.
I'm ok at server & database admin but poor at database programming. And
brand new to SQL2k5 mirroring.
Thanks"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> We are moving our asp application from SQL2k to SQL2k5. I implemented
> database mirroring for my critical databases, which is setup & running. I
> modified the connection string in the Global.asa to point to my new
> servers.
> When both SQL servers are up & running I connect to the primary database.
> However, when the server hosting the primary database is stopped, I get an
> error regarding the connection string.
> Is this an application error that needs to be resolved? What do I tell the
> developers to do?
> Is this a problem with the SQL connection string? Does anyone have working
> examples?
> Also, I am using a witness server.
> Does anyone have any test procedures/scripts/scenarios to insure mirroring
> is working as it should. I thought I could stop SQL on a server run a
> test,
> restart SQL & then check the databases.
> I'm ok at server & database admin but poor at database programming. And
> brand new to SQL2k5 mirroring.
>
Does your connection string specify both the principal and the mirror? If
not, failover will work, but startup when the principal is down will not.
See:
System.Data.SqlConnection.ConnectionString Property
Failover Partner
N/A
The name of the failover partner server where database mirroring is
configured.
The Failover Partner keyword is not supported by .NET Framework version 1.0
or 1.1.
http://msdn2.microsoft.com/en-us/li...tionstring.aspx
David|||"David Browne" wrote:
>
> "BigSam" <BigSam@.discussions.microsoft.com> wrote in message
> news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> Does your connection string specify both the principal and the mirror? If
> not, failover will work, but startup when the principal is down will not.
> See:
> System.Data.SqlConnection.ConnectionString Property
> Failover Partner
> N/A
> The name of the failover partner server where database mirroring is
> configured.
> The Failover Partner keyword is not supported by .NET Framework version 1.
0
> or 1.1.
> http://msdn2.microsoft.com/en-us/li...tionstring.aspx
>
> David
> This is the connection string set in the Global.asa:
Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
Also, we're not using asp.net - just plain old ASP.|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:E89D67CA-4BA6-4A87-B053-F3F10BC1D3AC@.microsoft.com...
>
> "David Browne" wrote:
>
> Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
> Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
> Also, we're not using asp.net - just plain old ASP.
>
The OleDb provider you're using isn't mirroring-aware. The SQLNCLI the new
SqlServer Native Client provider, if it works correctly with your
application. See:
Using ADO with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130978.aspx
If you stick with SQLOLEDB, then you can use DNS or BigIP to manually switch
the traffic between servers, or just change your config and boune the site.
David|||Thanks for your reply.
Yesterday & was able to determine that without mirroring I could connect to
each server with the SQL login. However, with mirroring enabled I could only
connect to SQL1 with the SQL login. I began to suspect the SID & found this
article: http://support.microsoft.com/default.aspx/kb/918992/
"Magas" wrote:
[vbcol=seagreen]
> If you use SQL Server login to connect to the mirror database - make sure
> logins on both mirror servers have the same SID. The easiest way to do it
-
> copy login from one server to another using SSIS package.
>
> "BigSam" wrote:
>
Mirroring
We are moving our asp application from SQL2k to SQL2k5. I implemented
database mirroring for my critical databases, which is setup & running. I
modified the connection string in the Global.asa to point to my new servers.
When both SQL servers are up & running I connect to the primary database.
However, when the server hosting the primary database is stopped, I get an
error regarding the connection string.
Is this an application error that needs to be resolved? What do I tell the
developers to do?
Is this a problem with the SQL connection string? Does anyone have working
examples?
Also, I am using a witness server.
Does anyone have any test procedures/scripts/scenarios to insure mirroring
is working as it should. I thought I could stop SQL on a server run a test,
restart SQL & then check the databases.
I'm ok at server & database admin but poor at database programming. And
brand new to SQL2k5 mirroring.
Thanks
"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> We are moving our asp application from SQL2k to SQL2k5. I implemented
> database mirroring for my critical databases, which is setup & running. I
> modified the connection string in the Global.asa to point to my new
> servers.
> When both SQL servers are up & running I connect to the primary database.
> However, when the server hosting the primary database is stopped, I get an
> error regarding the connection string.
> Is this an application error that needs to be resolved? What do I tell the
> developers to do?
> Is this a problem with the SQL connection string? Does anyone have working
> examples?
> Also, I am using a witness server.
> Does anyone have any test procedures/scripts/scenarios to insure mirroring
> is working as it should. I thought I could stop SQL on a server run a
> test,
> restart SQL & then check the databases.
> I'm ok at server & database admin but poor at database programming. And
> brand new to SQL2k5 mirroring.
>
Does your connection string specify both the principal and the mirror? If
not, failover will work, but startup when the principal is down will not.
See:
System.Data.SqlConnection.ConnectionString Property
Failover Partner
N/A
The name of the failover partner server where database mirroring is
configured.
The Failover Partner keyword is not supported by .NET Framework version 1.0
or 1.1.
[url]http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstri ng.aspx[/url]
David
|||"David Browne" wrote:
>
> "BigSam" <BigSam@.discussions.microsoft.com> wrote in message
> news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> Does your connection string specify both the principal and the mirror? If
> not, failover will work, but startup when the principal is down will not.
> See:
> System.Data.SqlConnection.ConnectionString Property
> Failover Partner
> N/A
> The name of the failover partner server where database mirroring is
> configured.
> The Failover Partner keyword is not supported by .NET Framework version 1.0
> or 1.1.
> [url]http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstri ng.aspx[/url]
>
> David
> This is the connection string set in the Global.asa:
Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
Also, we're not using asp.net - just plain old ASP.
|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:E89D67CA-4BA6-4A87-B053-F3F10BC1D3AC@.microsoft.com...
>
> "David Browne" wrote:
> Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
> Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
> Also, we're not using asp.net - just plain old ASP.
>
The OleDb provider you're using isn't mirroring-aware. The SQLNCLI the new
SqlServer Native Client provider, if it works correctly with your
application. See:
Using ADO with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130978.aspx
If you stick with SQLOLEDB, then you can use DNS or BigIP to manually switch
the traffic between servers, or just change your config and boune the site.
David
|||Thanks for your reply.
Yesterday & was able to determine that without mirroring I could connect to
each server with the SQL login. However, with mirroring enabled I could only
connect to SQL1 with the SQL login. I began to suspect the SID & found this
article: http://support.microsoft.com/default.aspx/kb/918992/
"Magas" wrote:
[vbcol=seagreen]
> If you use SQL Server login to connect to the mirror database - make sure
> logins on both mirror servers have the same SID. The easiest way to do it -
> copy login from one server to another using SSIS package.
>
> "BigSam" wrote:
sql
database mirroring for my critical databases, which is setup & running. I
modified the connection string in the Global.asa to point to my new servers.
When both SQL servers are up & running I connect to the primary database.
However, when the server hosting the primary database is stopped, I get an
error regarding the connection string.
Is this an application error that needs to be resolved? What do I tell the
developers to do?
Is this a problem with the SQL connection string? Does anyone have working
examples?
Also, I am using a witness server.
Does anyone have any test procedures/scripts/scenarios to insure mirroring
is working as it should. I thought I could stop SQL on a server run a test,
restart SQL & then check the databases.
I'm ok at server & database admin but poor at database programming. And
brand new to SQL2k5 mirroring.
Thanks
"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> We are moving our asp application from SQL2k to SQL2k5. I implemented
> database mirroring for my critical databases, which is setup & running. I
> modified the connection string in the Global.asa to point to my new
> servers.
> When both SQL servers are up & running I connect to the primary database.
> However, when the server hosting the primary database is stopped, I get an
> error regarding the connection string.
> Is this an application error that needs to be resolved? What do I tell the
> developers to do?
> Is this a problem with the SQL connection string? Does anyone have working
> examples?
> Also, I am using a witness server.
> Does anyone have any test procedures/scripts/scenarios to insure mirroring
> is working as it should. I thought I could stop SQL on a server run a
> test,
> restart SQL & then check the databases.
> I'm ok at server & database admin but poor at database programming. And
> brand new to SQL2k5 mirroring.
>
Does your connection string specify both the principal and the mirror? If
not, failover will work, but startup when the principal is down will not.
See:
System.Data.SqlConnection.ConnectionString Property
Failover Partner
N/A
The name of the failover partner server where database mirroring is
configured.
The Failover Partner keyword is not supported by .NET Framework version 1.0
or 1.1.
[url]http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstri ng.aspx[/url]
David
|||"David Browne" wrote:
>
> "BigSam" <BigSam@.discussions.microsoft.com> wrote in message
> news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> Does your connection string specify both the principal and the mirror? If
> not, failover will work, but startup when the principal is down will not.
> See:
> System.Data.SqlConnection.ConnectionString Property
> Failover Partner
> N/A
> The name of the failover partner server where database mirroring is
> configured.
> The Failover Partner keyword is not supported by .NET Framework version 1.0
> or 1.1.
> [url]http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstri ng.aspx[/url]
>
> David
> This is the connection string set in the Global.asa:
Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
Also, we're not using asp.net - just plain old ASP.
|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:E89D67CA-4BA6-4A87-B053-F3F10BC1D3AC@.microsoft.com...
>
> "David Browne" wrote:
> Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
> Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
> Also, we're not using asp.net - just plain old ASP.
>
The OleDb provider you're using isn't mirroring-aware. The SQLNCLI the new
SqlServer Native Client provider, if it works correctly with your
application. See:
Using ADO with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130978.aspx
If you stick with SQLOLEDB, then you can use DNS or BigIP to manually switch
the traffic between servers, or just change your config and boune the site.
David
|||Thanks for your reply.
Yesterday & was able to determine that without mirroring I could connect to
each server with the SQL login. However, with mirroring enabled I could only
connect to SQL1 with the SQL login. I began to suspect the SID & found this
article: http://support.microsoft.com/default.aspx/kb/918992/
"Magas" wrote:
[vbcol=seagreen]
> If you use SQL Server login to connect to the mirror database - make sure
> logins on both mirror servers have the same SID. The easiest way to do it -
> copy login from one server to another using SSIS package.
>
> "BigSam" wrote:
sql
Mirror Server
i am looking at options for a 'hot spare' server for my SQL application
the ideal senario would be that the 2nd server would 'take over' under a
controlled manner as the same server etc
anybody using any third party solutions that can handle this?
--
Many thanks
Jezjez_UK,
Do you want to protect your disks as well as the server hardware? Have a
look at clustering. How far apart physically are your servers? Also look
out for database mirroring in SQL Server 2005 next year - this has an
automatic three second failover time.
When you say "controlled manner" do you mean manually? How much data
loss can you tolerate? You might also want to consider log shipping and
possibly a third party replication tool like Double-Take from NSI software.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
jez_UK wrote:
> i am looking at options for a 'hot spare' server for my SQL application
> the ideal senario would be that the 2nd server would 'take over' under a
> controlled manner as the same server etc
> anybody using any third party solutions that can handle this?
>
the ideal senario would be that the 2nd server would 'take over' under a
controlled manner as the same server etc
anybody using any third party solutions that can handle this?
--
Many thanks
Jezjez_UK,
Do you want to protect your disks as well as the server hardware? Have a
look at clustering. How far apart physically are your servers? Also look
out for database mirroring in SQL Server 2005 next year - this has an
automatic three second failover time.
When you say "controlled manner" do you mean manually? How much data
loss can you tolerate? You might also want to consider log shipping and
possibly a third party replication tool like Double-Take from NSI software.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
jez_UK wrote:
> i am looking at options for a 'hot spare' server for my SQL application
> the ideal senario would be that the 2nd server would 'take over' under a
> controlled manner as the same server etc
> anybody using any third party solutions that can handle this?
>
Wednesday, March 21, 2012
Minimal required DLLS for Named Pipes connection?
I have a work around for an application use Integrated Security
instead of SQL Security (which is great). I need to use named pipes
for this to work.
I only got it to work after I did a SQL 2005 Connectivity and Client
Tools install which
installed everything but the Server services. Boss doesn't like this
approach, wants minimal install.
My questions:
Are there just a few DLLs that I need to connect to SQL Server with
named pipes only?
Do I need to Register the DLLs using REGSRV32.EXE?
Thanks
ErikYou can probably get this to work with only installing the client
connectivity. I wouldn't try to hack it.
Jason Massie
http://statisticsio.com
"Erik G" <info@.fdaregulatory.com> wrote in message
news:1194885570.885338.225030@.v3g2000hsg.googlegroups.com...
>I have a work around for an application use Integrated Security
> instead of SQL Security (which is great). I need to use named pipes
> for this to work.
> I only got it to work after I did a SQL 2005 Connectivity and Client
> Tools install which
> installed everything but the Server services. Boss doesn't like this
> approach, wants minimal install.
> My questions:
> Are there just a few DLLs that I need to connect to SQL Server with
> named pipes only?
> Do I need to Register the DLLs using REGSRV32.EXE?
> Thanks
> Erik
>
instead of SQL Security (which is great). I need to use named pipes
for this to work.
I only got it to work after I did a SQL 2005 Connectivity and Client
Tools install which
installed everything but the Server services. Boss doesn't like this
approach, wants minimal install.
My questions:
Are there just a few DLLs that I need to connect to SQL Server with
named pipes only?
Do I need to Register the DLLs using REGSRV32.EXE?
Thanks
ErikYou can probably get this to work with only installing the client
connectivity. I wouldn't try to hack it.
Jason Massie
http://statisticsio.com
"Erik G" <info@.fdaregulatory.com> wrote in message
news:1194885570.885338.225030@.v3g2000hsg.googlegroups.com...
>I have a work around for an application use Integrated Security
> instead of SQL Security (which is great). I need to use named pipes
> for this to work.
> I only got it to work after I did a SQL 2005 Connectivity and Client
> Tools install which
> installed everything but the Server services. Boss doesn't like this
> approach, wants minimal install.
> My questions:
> Are there just a few DLLs that I need to connect to SQL Server with
> named pipes only?
> Do I need to Register the DLLs using REGSRV32.EXE?
> Thanks
> Erik
>
Minimal required DLLS for Named Pipes connection?
I have a work around for an application use Integrated Security
instead of SQL Security (which is great). I need to use named pipes
for this to work.
I only got it to work after I did a SQL 2005 Connectivity and Client
Tools install which
installed everything but the Server services. Boss doesn't like this
approach, wants minimal install.
My questions:
Are there just a few DLLs that I need to connect to SQL Server with
named pipes only?
Do I need to Register the DLLs using REGSRV32.EXE?
Thanks
Erik
You can probably get this to work with only installing the client
connectivity. I wouldn't try to hack it.
Jason Massie
http://statisticsio.com
"Erik G" <info@.fdaregulatory.com> wrote in message
news:1194885570.885338.225030@.v3g2000hsg.googlegro ups.com...
>I have a work around for an application use Integrated Security
> instead of SQL Security (which is great). I need to use named pipes
> for this to work.
> I only got it to work after I did a SQL 2005 Connectivity and Client
> Tools install which
> installed everything but the Server services. Boss doesn't like this
> approach, wants minimal install.
> My questions:
> Are there just a few DLLs that I need to connect to SQL Server with
> named pipes only?
> Do I need to Register the DLLs using REGSRV32.EXE?
> Thanks
> Erik
>
sql
instead of SQL Security (which is great). I need to use named pipes
for this to work.
I only got it to work after I did a SQL 2005 Connectivity and Client
Tools install which
installed everything but the Server services. Boss doesn't like this
approach, wants minimal install.
My questions:
Are there just a few DLLs that I need to connect to SQL Server with
named pipes only?
Do I need to Register the DLLs using REGSRV32.EXE?
Thanks
Erik
You can probably get this to work with only installing the client
connectivity. I wouldn't try to hack it.
Jason Massie
http://statisticsio.com
"Erik G" <info@.fdaregulatory.com> wrote in message
news:1194885570.885338.225030@.v3g2000hsg.googlegro ups.com...
>I have a work around for an application use Integrated Security
> instead of SQL Security (which is great). I need to use named pipes
> for this to work.
> I only got it to work after I did a SQL 2005 Connectivity and Client
> Tools install which
> installed everything but the Server services. Boss doesn't like this
> approach, wants minimal install.
> My questions:
> Are there just a few DLLs that I need to connect to SQL Server with
> named pipes only?
> Do I need to Register the DLLs using REGSRV32.EXE?
> Thanks
> Erik
>
sql
Minimal install possible?
The smallest downloadable version seems to be 53MB. Is there any way to create a smaller version to use as an embedded DB for an application? I only need it to be single-user, for what that's worth. I'm thinking more in the 10MB to 20MB range.to my knowledge NO and the SQLExpress team made every effort to ensure the smallest D/Ls possible.|||
You have an option to use SQL Server 2005 Everywhere edition. It has to be run in-process and won't run as a service (however, I think it might be possible to create your own windows service where you embed SQL Server Everywhere). It only uses a few MB HD space, and uses about 5 MB RAM. Available at http://www.microsoft.com/sql/ctp_sqlserver2005everywhereedition.mspx, though only in CTP. Scheduled for release at the end of 2006.
Best regards
Bj?rnar Sundsb?
Minimal install possible?
The smallest downloadable version seems to be 53MB. Is there any way to create a smaller version to use as an embedded DB for an application? I only need it to be single-user, for what that's worth. I'm thinking more in the 10MB to 20MB range.to my knowledge NO and the SQLExpress team made every effort to ensure the smallest D/Ls possible.|||
You have an option to use SQL Server 2005 Everywhere edition. It has to be run in-process and won't run as a service (however, I think it might be possible to create your own windows service where you embed SQL Server Everywhere). It only uses a few MB HD space, and uses about 5 MB RAM. Available at http://www.microsoft.com/sql/ctp_sqlserver2005everywhereedition.mspx, though only in CTP. Scheduled for release at the end of 2006.
Best regards
Bj?rnar Sundsb?
Monday, March 19, 2012
min memory per query
I have a VB.NET application which reads thousands of records from a MSDE
Database, processes them, and writes them against a MYSQL Database. I'm
using the MySQL .NET DataProvider for accessing the MYSQL Database. When I
test the application on my lap top (512MB RAM; MSDE Memory limited to 260MB)
I don't get any errors. When I test the same application on another lap top
(2GB Ram; no limits) I get the error message, that there isn't enough memory
for the querz and I should reduce "min memory per query", which is already
at 512KB. On a desktop computer with 512MB Ram I don't get errors. On a
Server with 2GB Ram the application runs fine, but the MSDE reserves about
1.4GB of Ram. If I limit the Ram of the MSDE on that server to 800MB I get
the error message.
On every computer there is installed: Win XP Pro (or Win XP Server for the
server) SP1, MSDE 2000 (same version on each computer), same MySQL
DataProvider version, and .NET 1.1. On my lap top there is also .NET 1.0
installed. I'm working with VS 2002, which is only installed on my lap top.
I didn't get the errors when writing to the MySQL database with ODBC, but I
can't use ODBC because of the MySQL Database version I have to access.
For processing the first third of the records the memory which is used by
the MSDE is about 250MB on each computer. After that the memory usage on my
lap top and on the desktop pc stays at a low level, but on the other lap top
and on the server the memory usage goes through the roof.
I don't have any glue what the problem can be, it seems that the release of
the memory is handled differently on those machines. Can someone give me a
hint where to look for, what to do, or how to recreate the problem on the
other machines? I can't even look for the problem if I can't recreate the
error on my development machine (my lap top).
Any tipps are welcome!
Thanks
Peter
Hi Peter
I don't think the error is the fact that the min value is too high but the
fact that there is not enough memory to complete the query with what's
there.
See
http://msdn.microsoft.com/library/de...rr_2_65pt.asp.
If you look at the query plan, it could be that your stats are out of date,
or that you are missing indexes or unecessary/excessive hashing/sorting is
occuring
http://msdn.microsoft.com/library/de...onfig_68q6.asp
John
"Peter Zentner" <peter@._REM_zentner-online.de> wrote in message
news:%23DvjjHCxEHA.2632@.TK2MSFTNGP10.phx.gbl...
>I have a VB.NET application which reads thousands of records from a MSDE
> Database, processes them, and writes them against a MYSQL Database. I'm
> using the MySQL .NET DataProvider for accessing the MYSQL Database. When I
> test the application on my lap top (512MB RAM; MSDE Memory limited to
> 260MB)
> I don't get any errors. When I test the same application on another lap
> top
> (2GB Ram; no limits) I get the error message, that there isn't enough
> memory
> for the querz and I should reduce "min memory per query", which is already
> at 512KB. On a desktop computer with 512MB Ram I don't get errors. On a
> Server with 2GB Ram the application runs fine, but the MSDE reserves about
> 1.4GB of Ram. If I limit the Ram of the MSDE on that server to 800MB I get
> the error message.
> On every computer there is installed: Win XP Pro (or Win XP Server for the
> server) SP1, MSDE 2000 (same version on each computer), same MySQL
> DataProvider version, and .NET 1.1. On my lap top there is also .NET 1.0
> installed. I'm working with VS 2002, which is only installed on my lap
> top.
> I didn't get the errors when writing to the MySQL database with ODBC, but
> I
> can't use ODBC because of the MySQL Database version I have to access.
> For processing the first third of the records the memory which is used by
> the MSDE is about 250MB on each computer. After that the memory usage on
> my
> lap top and on the desktop pc stays at a low level, but on the other lap
> top
> and on the server the memory usage goes through the roof.
> I don't have any glue what the problem can be, it seems that the release
> of
> the memory is handled differently on those machines. Can someone give me a
> hint where to look for, what to do, or how to recreate the problem on the
> other machines? I can't even look for the problem if I can't recreate the
> error on my development machine (my lap top).
> Any tipps are welcome!
> Thanks
> Peter
>
Database, processes them, and writes them against a MYSQL Database. I'm
using the MySQL .NET DataProvider for accessing the MYSQL Database. When I
test the application on my lap top (512MB RAM; MSDE Memory limited to 260MB)
I don't get any errors. When I test the same application on another lap top
(2GB Ram; no limits) I get the error message, that there isn't enough memory
for the querz and I should reduce "min memory per query", which is already
at 512KB. On a desktop computer with 512MB Ram I don't get errors. On a
Server with 2GB Ram the application runs fine, but the MSDE reserves about
1.4GB of Ram. If I limit the Ram of the MSDE on that server to 800MB I get
the error message.
On every computer there is installed: Win XP Pro (or Win XP Server for the
server) SP1, MSDE 2000 (same version on each computer), same MySQL
DataProvider version, and .NET 1.1. On my lap top there is also .NET 1.0
installed. I'm working with VS 2002, which is only installed on my lap top.
I didn't get the errors when writing to the MySQL database with ODBC, but I
can't use ODBC because of the MySQL Database version I have to access.
For processing the first third of the records the memory which is used by
the MSDE is about 250MB on each computer. After that the memory usage on my
lap top and on the desktop pc stays at a low level, but on the other lap top
and on the server the memory usage goes through the roof.
I don't have any glue what the problem can be, it seems that the release of
the memory is handled differently on those machines. Can someone give me a
hint where to look for, what to do, or how to recreate the problem on the
other machines? I can't even look for the problem if I can't recreate the
error on my development machine (my lap top).
Any tipps are welcome!
Thanks
Peter
Hi Peter
I don't think the error is the fact that the min value is too high but the
fact that there is not enough memory to complete the query with what's
there.
See
http://msdn.microsoft.com/library/de...rr_2_65pt.asp.
If you look at the query plan, it could be that your stats are out of date,
or that you are missing indexes or unecessary/excessive hashing/sorting is
occuring
http://msdn.microsoft.com/library/de...onfig_68q6.asp
John
"Peter Zentner" <peter@._REM_zentner-online.de> wrote in message
news:%23DvjjHCxEHA.2632@.TK2MSFTNGP10.phx.gbl...
>I have a VB.NET application which reads thousands of records from a MSDE
> Database, processes them, and writes them against a MYSQL Database. I'm
> using the MySQL .NET DataProvider for accessing the MYSQL Database. When I
> test the application on my lap top (512MB RAM; MSDE Memory limited to
> 260MB)
> I don't get any errors. When I test the same application on another lap
> top
> (2GB Ram; no limits) I get the error message, that there isn't enough
> memory
> for the querz and I should reduce "min memory per query", which is already
> at 512KB. On a desktop computer with 512MB Ram I don't get errors. On a
> Server with 2GB Ram the application runs fine, but the MSDE reserves about
> 1.4GB of Ram. If I limit the Ram of the MSDE on that server to 800MB I get
> the error message.
> On every computer there is installed: Win XP Pro (or Win XP Server for the
> server) SP1, MSDE 2000 (same version on each computer), same MySQL
> DataProvider version, and .NET 1.1. On my lap top there is also .NET 1.0
> installed. I'm working with VS 2002, which is only installed on my lap
> top.
> I didn't get the errors when writing to the MySQL database with ODBC, but
> I
> can't use ODBC because of the MySQL Database version I have to access.
> For processing the first third of the records the memory which is used by
> the MSDE is about 250MB on each computer. After that the memory usage on
> my
> lap top and on the desktop pc stays at a low level, but on the other lap
> top
> and on the server the memory usage goes through the roof.
> I don't have any glue what the problem can be, it seems that the release
> of
> the memory is handled differently on those machines. Can someone give me a
> hint where to look for, what to do, or how to recreate the problem on the
> other machines? I can't even look for the problem if I can't recreate the
> error on my development machine (my lap top).
> Any tipps are welcome!
> Thanks
> Peter
>
Subscribe to:
Posts (Atom)