locked
Mirroring Attemp Fails with errors RRS feed

  • Question

  • Hello,

    I have configured mirroring many times before on sql 2008 with no issues. I have two new clusters on sql 2012, and when I attempt to mirror the databases I get the following errors.

    An error occurred while starting mirroring.
    Additional information:
    L., Alter failed for Database ‘MyDatabase’. (Microsoft.SqlServer.Smo)
    L..> An exception occurred while executing a Transact-SQL statement or batch.
    (Microsoft. SqiServer . Connectionlnfo)
    L., The server network address ‘TCP://my-mirror-server-cluster: 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. (Microsoft SQL Server, Error: 1418)

    To get this error I have followed these steps;

    I go to the primary sql cluster and take a full backup of the database. (The database is in full recovery mode)

    I copy the database backup file to the desired SQL mirror cluster.

    I restore the database on that cluster, and I give it the same name, and I restore with no recovery.

    I go to the primary sql cluster and I configure mirroring security, pointing to the desired sql server for the mirroring instance. 

    This successfully works. 

    I click start mirroring and I get the error mentioned above.

    I also checked the logs and I see this error repeated almost ten times at the time I start mirroring on the mirrored server logs;

    07/25/2016 15:19:28,Logon,Unknown,Database Mirroring login attempt by user 'DOMAIN\SQLSVC.' failed with error: 'Connection handshake failed. The login 'DOMAIN\SQLSVC.' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: x.x.x.x]

    I verified the account SQLSVC has permissions in the SQL server security settings, and I do not know why it is failing.

    Any help would be greatly appreciated.



    Jacque Donald

    Monday, July 25, 2016 7:49 PM

Answers

  • Hi Jacque,

    Make sure that the login ‘DOMAIN\SQLSVC’ exists in your SQL Server instances, also grant the user CONNECT permission on the endpoint and check if the issue still persists. You can grant the permission with the following command.

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DOMAIN\SQLSVC];

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    • Marked as answer by Jacque_Donald Tuesday, July 26, 2016 1:48 PM
    Tuesday, July 26, 2016 9:51 AM

All replies

  • Hi,

      First thing I will check is the firewall, have you enable your end-point port in firewall? Most likely is that the endpoint cannot be reach.

      Hope this helps.

    Tuesday, July 26, 2016 1:24 AM
  • I forgot to mention that was the first thing I verified. Port 5022 is open between the two and I can talk to each one on the port from each other.

    The only thing I noticed is the account it is referencing is a local windows admin on the principal but not on the mirror, but the account does have sql admin rights on both sides. When I am configuring the mirror security I am not referencing the account in the error, I am using a different account though....


    Jacque Donald

    Tuesday, July 26, 2016 2:53 AM
  • Hi Jacque,

    Make sure that the login ‘DOMAIN\SQLSVC’ exists in your SQL Server instances, also grant the user CONNECT permission on the endpoint and check if the issue still persists. You can grant the permission with the following command.

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DOMAIN\SQLSVC];

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    • Marked as answer by Jacque_Donald Tuesday, July 26, 2016 1:48 PM
    Tuesday, July 26, 2016 9:51 AM
  • I have the account in both SQL server instances. I even granted it all server roles on each side. I ran the following command to identify the endpoint (shows as 'Mirroring')

    So i ran the command 

    GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\SQLSVC]; 

    It completed successfully on both instances. I re-ran the mirror configure security wizard and it still fails on the same error.


    Jacque Donald

    Tuesday, July 26, 2016 1:25 PM
  • I think I may have figured this out. I ran it again and I have a different SQLSVC account on the endpoint and now its bombing on that account. I just added it to the principal server instance and I started mirroring but it failed with a message that my log files were out of sync, which makes sense since the mirrored db is restored from a file from a few days ago. I am performing a new backup and overwriting the mirrored db and I will attempt again hoping it will work this time. I will post back as soon as I am done.

    Jacque Donald

    Tuesday, July 26, 2016 1:41 PM
  • SUCCESS. The databases are fully synchronized. Thank you so much for the help!

    Jacque Donald

    Tuesday, July 26, 2016 1:48 PM