Witness giving error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Witness giving error

Hai
Friends
First wishes Happy New Year to you All.I done Mirroring successfully
without Witness.But when I am trying with Witness it’s giving error "Alter Database command couldn’t sent to remote server instance ‘TCP://FKBSUB:5022’.
The database mirroring configuration is not changed.Verify Server is connected and try again (Microsoft SQL Server,Error:1456)" ‘TCP://FKBSUB:5022’ is my witness address Filson
is the witness server Firewall enabled ? the port 5022 should be open. refer this link :http://msdn2.microsoft.com/en-us/library/ms190430.aspx Madhu
Ya It’s Firewall enabled.But one important thing I missed.If I am using Witness as a enterprised edition it’s working fine.But If i am using SQL Express as Witness it’s giving above mentioned error
Filson
is in’t this sql Express is a named instance. In that case u have mentioned the default instance name, change to Named instance. And also check the service pack for SQL Express. Madhu
SQL Express edition should be fine witness… By default SQL Express edition is not configure for remote conections, make sure it is configured for allow remote connections in SAC. Mohammed U.
HI All
I am facing same error. Mirroring running fine between Principal and Mirroring Server. Just want to configure Witness Server and this following error.
The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://IndelRGDev.VCCORP.COM:7022’. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)
I have verified the following points…
  1. All 3 server runnig same accounts.
  2. I have found Endpoints are already configured on all of 3 Server with 7022 Port by running these T-SQLs.
    • SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
      t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
      e.connection_auth_desc
      FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
      ON e.endpoint_id = t.endpoint_id
    • SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints
    • SELECT name, port FROM sys.tcp_endpoints
  3. I have checked Window Events log on Principal Server and found following Error:
    • "Database Mirroring login attempt by user ‘NT AUTHORITYANONYMOUSLOGON.’ failed with error: ‘Connection handshake failed. The login ‘NTAUTHORITYANONYMOUS LOGON’ does not have CONNECT permission on theendpoint. State 84"
  4. Is this could be the solution as it recommend to place the host file on each of Server If yes the which host file and how ?
    • http://social.technet.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/f3088009-4e2e-4695-b2ad-5c4a2cc55f69/
So, What I am missing here to configured Witness Server? I am confused. Please help.
Thanks in Advance
sonny
Try setting up Witness on SQL Server 2005 Express Edition Advance with atleast SP1. I faced the same problem which got resolved after installing SP1.
Also, make sure that you enabled TCP/IP remote connections in witness. {Surface Area Manager}
In configuration manager, expand network Configuration –> Protocols–> Double click TCP/IP and provide port number.
Thanks….
I have checked and these are alreday configured… But here it is some information that might help to see the problem…
  • Witness SQL Server has Developer Edition with SP2 (Principal and Morroring has Standarad Edition with SP2)
  • TCP/IP already configured with 1433 default port number (but when witness configured on port 7022 as Principal and Mirroring Server and they are listening on 7022 port)
Thanks for help and please suggest further steps to solve it.
sonny
Sonny,
In your configuration, 1433 is the port for client applications to connect and 7022 is the port for mirroring so the databases should be listening on both ports.
Look, though microsoft has delivered a fix for this error in SP2, you have to do some hit n trial.
  1. Coz sometimes you can fix this error by dropping and recreating the endpoints on witness and Principal.
  2. Restarting the Witness.
  3. Reinstalling the Witness.
Tell me a few things:
  1. Are you running the default instance or, named instance?
  2. When you receive error 1456 what does event log say?
Send me the details of your servers/ Instances, Configuration Manager settings (Port settings) etc. No need to mention the IPs (Security point of view)[;)]
Then, if it is confortable for you to start a fresh mirroring session. I will create and send the scripts to be executed on all the servers. Let’s see if this helps…
Sonny
you may have problem in using SQL Developer edition for the production purpose, whihc is not recommend as a good practice and MS do not agree for that.
The best option is to use Express edition as witness provided you have configured the connections between the instances that are involved in teh database mirroring.
Sonny,
Not sure if you ever fixed this. But after 2 days ofheadaches and reading many threads and forums about this error thatoffered no fixes that worked for me, I figured I’d share what finallydid the trick.
My Primary and Mirror servers are running SQL2005 Standard with SP3 on Windows Server 2003.I am using SQL 2005Express SP3 on an XP machine for my witness server. All are on the samedomain, which I’ll refer to as MYDOMAIN for simplicity. I knew needinghosts files were likely not an issue for me, and the wizard wasautomatically filling in "TCP://WITNESSSERVER.MYDOMAIN.com:5022" when Idid "Configure Security". However, upon clicking "OK", I wasgetting the same 1456 error "The ALTER DATABASE command could not besent…" and seeing the same The login ‘NTAUTHORITYANONYMOUS LOGON’ does not have CONNECT permission on theendpoint messages in my error logs.
Finally, after hunting and hunting, I opened "Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager" Eventhough I installed SQL expess under the "MYDOMAINAdministrator" logonI used for the Primary and Mirror standard ed installs, and could swearif prompted, I would have entered that logon for my Service Account…but sure enough under "Log On As", it said "NT AUTHORITYANONYMOUSLOGON" for both my instance and the SQL Server Browser. I right clickedeach, went to Properties, and picked "This Account:", and changed themto the proper "MYDOMAINAdministrator" login and password, andrestarted the services.
Sure enough, when I now tried to add a witness server, it worked like a charm!
Hopefully this willhelp you. Also, I second satya’s advice to follow MS’ reccomendation touse Express edition rather than Developer as your witness server ifpossible.
I had a same problem as filson.
Your answer was excellent guide for me.
Finally, i solved problem :)
]]>