January 23, 2012 at 5:50 am
Hi
I am using SQL Server 2008 R2
I have SQL Server configured in a high availability configuration with automatic failover using a Witness, Principal and Mirror server
I have a web site running on IIS 7 that connects to the database using a SQL Server login. The connection string has a "Data Source" property and a "Failover Partner" property
When i failover manually the database switches the principal to the mirror correctly
The web site returns an error message of "Login failed for user xxx"
When i investigate i can see that the SQL Server login has been disconnected/unmapped to the database on the mirror server.
If i run the SQL Script on the Mirror Server (now the principal after the failover):
USE [MirrorDatabase]
ALTER USER [SQLServerLogin] WITH LOGIN = [SQLServerLogin]
the SQL Server user login is re-mapped to the database and the web site re-connects to the database on the mirror server.
This does not seem right, as I thought the idea of automatic failover was to allow a client application, a web site in this case, to seamlessly re-connect to the principal database regardless of which server it is on without manual intervention.
Does anyone know what I could be doing wrong and how to fix this problem?
Regards
Justin
January 23, 2012 at 6:39 am
It sounds like you are using a SQL Authentication login and you have created a new login on the mirror server. If this is the case have a quick google for the sp_help_revlogin.
This procedure will allow you to correctly script the login from the principal server with the correct password and more importantly SID. This will mean the login will map correctly no matter what.
This has the info you need.
http://support.microsoft.com/kb/246133
Cheers
Mat
January 23, 2012 at 7:03 am
execute the following query against the principal and the mirror instances
select sid from sys.sql_logins where name = 'yourlogin'
The output will be different. You can either use sp_helprvlogin as already suggested, or to sync just one user use the following code to build a create login statement
select 'create login ' + quotename(name) + ' with password = ' +
convert(nvarchar(50), sys.fn_varbintohexstr(password_hash)) + ' hashed, sid = ' +
convert(nvarchar(50), sys.fn_varbintohexstr(sid))
from sys.sql_logins where name = 'yourlogin'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 23, 2012 at 7:51 am
Hi Thanks Mat and Perry, your explanation of the problem was useful and the solution worked.
I can now failover between the principal and the mirror and the web site re-connects with no manual intervention required.
Regards
Justin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply