Database Mirroring - Witness

Question

Database Mirroring - Witness

stewart noble

SSCrazy

Points: 2009
More actions
August 3, 2006 at 8:33 am

#114499

Hi All,

I've been lloking at this for so long it has started to go all fuzzy in front of me.

I am trying to set up database mirroring between two SQL Server 2005 Standard Edition servers (both SP1). They are both within the same domain and I have got Synchronous (High Protection) working beautifully between them. However I really want to include a witness in the picture. To this end I have instaslled a copy of SQL Server 2005 Express on the server which I am currently using as the Mirror. I have tried to get this working and am constantly failing at the same point. When I try to set ALTER the principal database I get the same error whether I am doing it through Management Studio or through T-SQL. The error I get is
"The ALTER DATABASE command could not be sent to the remote server instance 'TCP://TOP-DB05.array.com:5023'. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (.Net SqlClient Data Provider)"
I am almost sure this is a permissioning problem, but I just cannot figure out where.
Has anyone come across a similar problem?

thanks for your help

S

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

stewart noble SSCrazy Points: 2009 More actions · Answer 1

By the way, I have get TCP/IP and Named Pipes enabled on the SQL Express installtion. But it doesn't seem to make any difference.

S

Balmukund SSCertifiable Points: 6648 More actions · Answer 2

can you please try below from command prompt

telnet TOP-DB05.array.com 5023

Above is to check if port is open

stewart noble SSCrazy Points: 2009 More actions · Answer 3

Hi,

I can telnet to port 5023 on TOP-DB05.array.com

It is very confusing. i am trying to think of an alternative, but at present without a witness, the mirroring is of limited use, as I am sure you can imagine.

Thanks for your help

S

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 4

Stewart,

I am having the same experiece, except that the principal and mirror servers are running Enterprise Ed. and the SQLExpress is installed on a third server.

Setting up a two-way mirroring session is easy, but setting up a Witness has proved impossible with either the Wizard or T-SQL.

I have queried sys.endpoints, sys.server_permissions, and sys.database_mirroring_endpoints to confirm that the endpoints are started and that connection permissions are correct.

Here is another diagnostic tool that may help you. Using the command prompt enter either netstat -abn (for Windows Server 2003 SP1 machines) or enter netstat -ano (for Windows Server 2003 pre-SP1 machines). This will display the ports being listened to and the list of processes. According to MS, if a second process is listening on the Witness port, mirroring connection will fail.

Unfortunately, this did not resolve our problem. If I find a solution, I will post it here.

Elliott

stewart noble SSCrazy Points: 2009 More actions · Answer 5

I am really really struggling with this.

I am now getting the following message in my event log

Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: The database principal has no mapping to a server principal. State 89.'.

I just know this is a permissions problem but i cannot figure out where.

If anyone has any thoughts they would be gratefully received.

Thanks

s

stewart noble SSCrazy Points: 2009 More actions · Answer 6

This is slowly but surely sapping me of the will to live.

I am now getting an error message in the event log as follows:

Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'

Now, while I understand what this means, what I can't figure out is why the PRincipal is trying to connect to the witness using an anonymous login when there is a perfectly good Administrator login that has been enabled on the principal, the Mirror AND the Witness. Can I force the principal to use the appropriate login? they are all in the same domain (in fact the witness is on the same box as the Mirror).

Is it supposed to be this hard?

S

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 7

Stewart,

Books On Line has the T-SQL code to GRANT CONNECTION permission on the endpoint. Perhaps if you ran that manually on the Witness, it would eliminate that specific failure you are getting.

Elliott

stewart noble SSCrazy Points: 2009 More actions · Answer 8

I've granted Connect permissions on the endpoint to the Array\Administrator and to the NT Authority\Annonymos Logon but this didn't work.

Hmmm

S

Michel Julien SSC Rookie Points: 36 More actions · Answer 9

I had the same problem doing SET WITNESS using certificates with 3 instances of SQL on the same server.

Then I found an article on msdn support that said the ACL of C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys could be the cause and to look at article 278381. Well, the ACL for 'Administrator' was set to 'special'. I changed it to 'FULL'.

I re-issued the "GRANT CONNECT ON ENDPOINT::myendpoint to principalLogin" on the witness SQL instance and then I re-issued the SET WITNESS command on the Principal server and it worked! (I first tried without the grant connect on the Witness SQL instance but it still failed.)

M Julien

Fred Terry SSC Enthusiast Points: 156 More actions · Answer 10

Stewart, I got the same message on my mirror instance when trying to set up the partner at the principal-- "Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: The database principal has no mapping to a server principal. State 89.'."

It seems to have been a result of my mistakenly trying to configure Mirroring using the Wizard, when this is not allowed when using certificate-based authentication.

For me, I ripped the mirroring out on all nodes (deleted endpoint, deleted the mirroring-specific certs on master, deleted the mirroring-specific users on master, deleted the mirroring-specific logins). Then re-ran the T-SQL Scripts I used to configure mirroring (the only way to do the certificate-based authentication is through T-SQL, not the wizard, as they warn the wizard breaks your endpoint).

Problem went away. Hope that helps!

Fred Terry SSC Enthusiast Points: 156 More actions · Answer 11

But now I get this issue with the witness... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=389402&SiteID=1

stewart noble SSCrazy Points: 2009 More actions · Answer 12

I ust don't think it works 🙁

We had got to the point where we were losing sight of what we were trying to do - principally trying to do an upgrade to SQL 2005 for performance and the enhancements it gives. So we took a step back and have simply done the upgrade and are least mirroring without the witness.

I wish it wasn't quite so hard.

S

Fred Terry SSC Enthusiast Points: 156 More actions · Answer 13

http://forums.microsoft.com/msdn/ShowPost.aspx?postid=750954&isthread=false&siteid=1

I had been trying to use IP addresses. Looks like that works okay for everything but as far as the Witness was concerned. Fixing the HOSTS file up on all the machines took care of it, at least for me (in addition to fixing the ACLs on the MachineKeys folder, not sure if that was required or not).

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 14

Check the records on your DNS server, or run nslookup against the IP addresses and hostnames locally on each server.

It's probably that there is no reverse look-up available for the IP address you said doesn't work in setting up the witness.