locked
SQL Server Database Mirroring Issue RRS feed

  • Question

  • Hello,
    I have SQL server 2008 R2 Enterprise edition 64 bit in Principle and Mirror Server and SQL Server 2008 R2 Express edition(64bit) in witness server and Operating system is windows server 2008 R2 SP1 in all three servers.
    I am trying mirroring for the first time, I have created endpoints in All three servers i.e. Principle, Mirror and Witness servers with TransactSQL query.
    And set the endpoints started.
    I have taken the full backup and Transaction Logs backup of DB from Principle server and restored both to the Mirror server with "No Recovery Option".
    I started configuring mirroring and my mirroring setup connected to all the endpoints, but when I am starting the mirroring it is giving me the error in Principle server:
    The server network address "TCP://server.domain.com:5023 can not be reached or does not exist. check the network address name and that the ports for the local remote endpoints are operational.(Microsoft SQL Server, Error:1418)
    While in Principle server event logs I am getting Event Id 1474:
    Database mirroring connection error 4 'An error occurred while receiving data '10054(An existing connection was forcibly closed by the remote host)'.'for TCP://server.domain.com:5023
    In mirror server event logs I am getting:
    Database mirroring Login attempt by user domain/user failed with error: connection handshake failed. The login does not have CONNECT permission on the endpoint, STATE84
    While the User I am trying to access with is domain user with administrative privilege in active directory and sysadmin privilege in all three SQL server instances.
    For Information: I have All 3 protocols Enabled in all 3 servers i.e. Shared Memory, Named Pipes and TCP/IP.
    And I have SQL Server and SQL Server Browser services running in all the three Servers.
    I have ports open in my firewall for mirroring is 5022(principle), 5023(Mirror) , 5024(Witness) and 1433(TCP and UDP) between all three servers.
    When I am trying to connect Mirror and witness from Principle Server it is connecting perfectly from SSMS, when I am connecting Principle and Mirror from Witness server SSMS it's also connecting perfectly, but when I am trying to connect Principle and Witness from Mirror it is not connecting and giving me the error: SQL Network Interfaces, error:26-Error locating Server/Instance Specified
    When I am checking the traffic in Firewall firewall is blocking 1434/UDP port between mirror and witness server. But we haven't opened 1434 in any of my servers, while Principle and mirror and Principle and witness are connecting perfectly.
    Please someone help.
    I am also attaching the snapshots for the errors.
    Sorry for the big post but i thought it will help pinpoint the issue.
    Tuesday, September 19, 2017 6:06 AM

Answers

  • Hello,

    It was a silly mistake which created such havoc and wasted my 3 days, Actually I am having SQL server 2008 R2 Enterprise edition and SQL server Express edition both installed in my Principle and Mirror servers. In my mirror server Endpoint was redirecting to SQL Server EXPRESS Instance while the endpoint was created in SQL Server Enterprise edition Instance. I got to know about by doing port query. When I have stopped my SQL Server Express service in Mirror server the port 5023 was not listening, while when I stopped MSSQLServer service from SQL Server Configuration Manager it was still showing listening.

    That means my Endpoint is redirected to SQL Server Express edition Instance. So I stopped SQL Server Express Edition in both my Principle and Mirror Server, Dropped endpoints in all three servers, and again created Endpoints in SQL Server 2008 R2 Enterprise Edition.

    Now configured mirroring in Principle server, all instances connected and Mirroring started.

    It worked.

    Sorry for troubling you guys.

    Thanks a lot.

    • Marked as answer by Rohits_da Thursday, September 21, 2017 5:29 AM
    Thursday, September 21, 2017 5:27 AM

All replies

  • Hi Ratsr,

    >> Database mirroring Login attempt by user domain/user failed with error: connection handshake failed. The login does not have CONNECT permission on the endpoint, STATE84

    According to the error message, you’ll need to grant CONNECT permission on the endpoint to that login.

    Besides, I found an issue similar to yours, click here see if this works for you.

    If you have any other questions, please let me know.

    Regards,
    Lin


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 19, 2017 9:49 AM
  • Hello Lin,

    Thanks for reply,I tried and the GRANT PERMISSION was successful on Principle and Witness server but in Mirror serve  when I am trying to grant connect permission on the endpoint to the Domain User it sends the error "cannot grant, deny or resolve permission to sa, dbo, entity owner, information schema, sys, or yourself.

    User I am using is SYSADMIN and Database is In Recovery State.


    • Edited by Rohits_da Tuesday, September 19, 2017 11:39 AM
    Tuesday, September 19, 2017 10:26 AM
  • Hi Ratsr,

    >>I tried and the GRANT PERMISSION was successful on Principle and Witness server

    The GRANT CONNECT permission suggest was based on your third picture so it should resolve that issue. 

    >>When I am checking the traffic in Firewall firewall is blocking 1434/UDP port between mirror and witness server. But we haven't opened 1434 in any of my servers, while Principle and mirror and Principle and witness are connecting perfectly.

    Since you have masked all the instance names, I have no idea whether you are using named instance or not. But if you are using named instance, you should enable UDP 1434 as it’s being used by SQL Server Browser Service, which is required to connect to named instance by using SERVERNAME/INSTANCENAME. 

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 20, 2017 7:26 AM
  • Thank You Lin,

    I will open port in Our firewall and let you know if it resolves the issue.

    I should have done that earlier but we have one more setup with SQL Mirroring and only ports open between then is 1433 for SQL Server Instance and 5022(Principle Endpoint), 5023(Mirror Endpoint) and 5024(Witness Endpoint).

    And that setup is working fine.

    So I was wondering if that setup is working without UDP-1434 being open then this setup should also work.

    BTW, Thanks I will post the results asap after opening the port.

    Rohit

    Wednesday, September 20, 2017 12:20 PM
  • Hello,

    It was a silly mistake which created such havoc and wasted my 3 days, Actually I am having SQL server 2008 R2 Enterprise edition and SQL server Express edition both installed in my Principle and Mirror servers. In my mirror server Endpoint was redirecting to SQL Server EXPRESS Instance while the endpoint was created in SQL Server Enterprise edition Instance. I got to know about by doing port query. When I have stopped my SQL Server Express service in Mirror server the port 5023 was not listening, while when I stopped MSSQLServer service from SQL Server Configuration Manager it was still showing listening.

    That means my Endpoint is redirected to SQL Server Express edition Instance. So I stopped SQL Server Express Edition in both my Principle and Mirror Server, Dropped endpoints in all three servers, and again created Endpoints in SQL Server 2008 R2 Enterprise Edition.

    Now configured mirroring in Principle server, all instances connected and Mirroring started.

    It worked.

    Sorry for troubling you guys.

    Thanks a lot.

    • Marked as answer by Rohits_da Thursday, September 21, 2017 5:29 AM
    Thursday, September 21, 2017 5:27 AM