locked
Database Mirroring Error 1456 RRS feed

  • Question

  • Hi Everyone,

    I doing some test on SQL DB Mirroring. I have set two VM one as principal server and another as mirror server. I can setup for High Safety mode to test Manual failover. The problem arise when i try to setup a witness to test auto failover. When i try to setup using SSMS, i encounter the below error.

    "The alter database command could not be sent to the remote server instance 'TCP://Mywitnessserver:5022". The database mirroring configuration was not changed. Verify the server is connected and try again.(SQL Server, Error 1456)."

    I have try to ping the witness server from both principal and mirror server and the result is succesful. I also try to telnet the witness from both pricipal and witness server and the result is succesful.

    I also have created same sql server user for all principal, witness and mirror server but still the same error come out.

    When i checked the SQL Server error log in witness server it shows the below error.

    "Database Mirroring login attempt failed with error Connection handshake failed. An OS call failed (8009030c) 0*8009030c(The login attempt failed) State 67.

    Details:

    Principal Server : Windows 2012 , Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)   Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (VM)

    Mirror Server : Windows 2012, Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)   Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (VM)

    Witness Server : Windows Server 2008, Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)   Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (VM)

    Can anyone have idea on how to solve this error? I will apreciate your help.

    Monday, February 10, 2014 3:02 AM

Answers

  • In Principal server --> SQL Service Account run Administrator

    In Mirror server --> SQL Service Account run under Administrator

    In witness server --> SQL service run under NT Authority\Network Service.

    Hi ,

    According to your description, as Balmukund post, when using Windows Authentication, the service account used by the witness must be in the same domain as the service accounts used by the principal and mirror server instances, or it must be in a trusted domain. Usually, if in select witness server option, we specify the server name, I recommend you used Windows Authentication to connect to principal server, which has sysadmin privileges, we also checked the Encrypt Data option and 5024 is the Port number which we used for Mirror endpoint. For more information about implement database mirroring via GUI.

    In addition, you can set database mirroring witness by using T-SQL, there is detail about adding a database mirroring witness using Windows Authentication (Transact-SQL), you can review the following article.
    http://msdn.microsoft.com/en-us/library/ms190430.aspx

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Monday, February 17, 2014 5:43 AM
    Tuesday, February 11, 2014 6:33 AM

All replies

  • Can you check if SPN is set for the witness server? It looks like issue is with the kerberos handshake authentication.

    If SPN is not configured in witness, please configure SPN. Please check the below link.

    http://technet.microsoft.com/en-us/library/bb735885.aspx

    Monday, February 10, 2014 4:35 AM
  • What is the service account? Make sure it domain account on all three machines.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Monday, February 10, 2014 5:23 AM
  • Hi Balmukund,

    Thanks for your prompt reply.

    In Principal server --> SQL Service Account run Administrator

    In Mirror server --> SQL Service Account run under Administrator

    In witness server --> SQL service run under NT Authority\Network Service.

    Monday, February 10, 2014 6:17 AM
  • In Principal server --> SQL Service Account run Administrator

    In Mirror server --> SQL Service Account run under Administrator

    In witness server --> SQL service run under NT Authority\Network Service.

    Hi ,

    According to your description, as Balmukund post, when using Windows Authentication, the service account used by the witness must be in the same domain as the service accounts used by the principal and mirror server instances, or it must be in a trusted domain. Usually, if in select witness server option, we specify the server name, I recommend you used Windows Authentication to connect to principal server, which has sysadmin privileges, we also checked the Encrypt Data option and 5024 is the Port number which we used for Mirror endpoint. For more information about implement database mirroring via GUI.

    In addition, you can set database mirroring witness by using T-SQL, there is detail about adding a database mirroring witness using Windows Authentication (Transact-SQL), you can review the following article.
    http://msdn.microsoft.com/en-us/library/ms190430.aspx

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Monday, February 17, 2014 5:43 AM
    Tuesday, February 11, 2014 6:33 AM