Migrating to SQL 2005: connect

Showing posts with label connect. Show all posts

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?

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?

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?

Mirror Setup Problems

We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:

- Doing Principal and Mirror (no witness)

- Using Management Studio

- Both servers are SQL Server 2005 SP1 Standard Edition

- Same domain account, same user id

- We can telnet to both and connect to both from Stuio.

We were originally getting error 1418, but now we're getting:

"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

This is what the log file looks like:

2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.

2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.

2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.

2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.

2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.

2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

Here’s an update:

I tried doing mirroring via the command line.

Creating endpoints went OK, backup and restore went OK.

Setting partner went OK from the mirror to principal, but blew up for principal to mirror:

ALTER DATABASE test1

SET PARTNER = ‘tcp://srvr1.xxx.net.com:5022’;

Msg 1418, Level 16, State 1, Server SRVR1, Line 1

The server network address "tcp://srvr1.xxx.net.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Name is right, ports are listening, endpoints are started. Why would it go from A to B but not B to A?

Thanks.

update again - problem more specific, starting a new thread.

|||I had the same problem, I have noticed that principal tries to connect to the mirror using 'NT AUTHORITY\ANONIMOUS ACCESS' (look at the errorlog file in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), so I just added that user with sysadmin role and everything works fine|||

I solved the problem by changing the way that SQL server services (SQL server and Agent) start, instead of local system account i used domain user account that had rights on both servers !

Best regards
Nikola