locked
Unable to add witness get error Error: 1456, Severity: 16, State: 3. The ALTER DATABASE command could not be sent to the remote server instance RRS feed

  • Question

  • Hi All.

    I have 2 clustered instances of SQL server, with the databases mirrored across the 2 failover clustered instances, SQL 2008 R2 (SP3) on a windows 2008 server.

    The setup previously had a witness server, however the server experienced a failure and had to be rebuilt, it was rebuilt with thesame name, however any attempt to add the server back as a witness has been failing. I have opened port 5022 acrosss all the servers concerned and I can also telnet onto port 5022 from sql instances to witness server and vice versa.

    The error that I keep getting is as follows.

    The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness_server:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
    
    Message
    Error: 1456, Severity: 16, State: 3.

    I have done some researched, ensured that the encryption algorithm is thesame across the 3 entities

    -- Production sql instance
    CREATE ENDPOINT [Mirroring] 
    	AUTHORIZATION [sa]
    	STATE=STARTED
    	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    	FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
    , ENCRYPTION = REQUIRED ALGORITHM RC4)
    GO
    
    
    -- DR failover instance
    
    CREATE ENDPOINT [Mirroring] 
    	AUTHORIZATION [sa]
    	STATE=STARTED
    	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    	FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
    , ENCRYPTION = REQUIRED ALGORITHM RC4)
    GO
    
    -- Witness
    CREATE ENDPOINT [Mirroring] 
    	AUTHORIZATION [sa]
    	STATE=STARTED
    	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    	FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE
    , ENCRYPTION = REQUIRED ALGORITHM RC4)
    GO
    

    I am not sure what needs to be done next.


    • Edited by MrFlinstone Wednesday, February 27, 2019 11:03 AM
    Wednesday, February 27, 2019 11:01 AM

All replies

  • Hi MrFlinstone,

     

    >>The setup previously had a witness server, however the server experienced a failure and had to be rebuilt, it was rebuilt with the same name, however any attempt to add the server back as a witness has been failing.

     

    For two server instances to connect to each other’s database mirroring endpoint point, the login account of each instance requires access to the other instance. Also, each login account requires “connect” permission to the Database Mirroring endpoint of the other instance.

     

    The login account of your newly created witness server does not have permission to log in to the principal database and mirror database.You need to add this login account to the principal and mirror databases.

     

    For more details, please refer to https://blog.sqlauthority.com/2018/03/30/sql-server-mirroring-error-1456-the-alter-database-command-could-not-be-sent-to-the-remote-server-instance/

     

    Best regards,

    Dedmon Dai


    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

    Thursday, February 28, 2019 2:48 AM
  • Hi,

    Thanks for the reply.

    I already granted the permissions, and still its thesame issue.

    I believe you mean granting permission to the sql service account being used by the SQL instances, they all happen to be using thesame account.

    Thursday, February 28, 2019 6:09 PM
  • Hi MrFlinstone,

    What i mean is granting permission to login account.Would you please refer to https://blog.sqlauthority.com/2018/03/30/sql-server-mirroring-error-1456-the-alter-database-command-could-not-be-sent-to-the-remote-server-instance/  

    Best regards,

    Dedmon Dai

     


    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

    Friday, March 1, 2019 2:19 AM
  • Hi,

    The errors are not thesame as the one mentioned in the blog post, I am not getting the following

    Database mirroring connection error 5 ‘Connection handshake failed. The login ‘SQLAUTHORITY\srv_w$’ does not have CONNECT permission on the endpoint. State 84.’ for ‘TCP://srv_w.sqlauthority.com:5022’.

    Besides as stated, I granted permissions to the end point and the sids are thesame in my case.

    Thanks.

    Friday, March 1, 2019 9:37 AM
  • Would you please refer to https://blog.sqlauthority.com/2017/01/06/sql-server-database-mirroring-login-attempt-failed-error-connection-handshake-failed/ . This is a problem similar to yours.

    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

    Monday, March 4, 2019 2:37 AM