Sql-server – Error 1456 when trying to SET a Witness using TSQL

mirroringsql server

Trying to run the following command:

ALTER DATABASE myDB SET WITNESS = 'TCP://myServer.local:7024';

I keep getting the following error:

Msg 1456, Level 16, State 3, Line 21
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://myServer.local:7024'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

Tried checking firewall ports, making sure certs are valid, making sure endpoints are granted access.

SQL Logs show the following message:

Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'

I have partner-partner quorum working fine but need the Witness to achieve full quorum. Using certificates to authenticate (no Domain). Has anyone come across this before?

Best Answer

Unfortunately I do not have the reputation to comment, actually I would rather add this as a comment and ask you about the output of your sys.database_mirroring_endpoints DMV first...

I'm assuming that you have verified the connection to be working using telnet or PS Test-NetConnection...

This sounds like the endpoint is not configured for the same authentication type and algorithm.

Run

select dme.*,c.name as cert_name,c.thumbprint,dbp.name as db_principal_name,spr.name as s_principal_name from sys.database_mirroring_endpoints dme
inner join sys.certificates c on dme.certificate_id=c.certificate_id
inner join sys.database_principals dbp on c.principal_id=dbp.principal_id

on your Principal and Secondary instances. Verify STATE_DESC to read "STARTED" and note down the following items which should both be the same on both instances.

  • role_desc
  • connection_auth_desc
  • encryption_algorithm_desc
  • thumbprint

Run the same statement on the witness instance and also note down the db_principal_name (last column).

Compare your values from the Principal/Mirror and the Witness. If the Witness has any values other than your mirror, change the endpoint on the Witness accordingly. Also verify the thumbprint of the certificate to be the same on your instances.

Warning: changing your endpoint on the Witness might break any Availability Groups or other database mirroring configurations there, so use caution. You can script your existing endpoint configuration in the Object Explorer before you change anything (Server Objects --> Endpoints --> Database Mirroring).

For example, if your connection_auth_desc on Principal/Mirror says "CERTIFICATE" and encryption_algorithm_desc "AES" using certificate SomeCert:

ALTER ENDPOINT endpoint_mirroring
AS TCP(LISTENER_PORT=7024, LISTENER_IP=ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE [SomeCert], ENCRYPTION=REQUIRED ALGORITHM AES, ROLE=ALL)

The login for the user that is stated in db_principal_name should also be allowed to connect to your endpoint. Verify this e.g. using the GUI by going to Security --> Logins --> double click the login and under Securables you should see your endpoint with "CONNECT" permission.

If not and if your Login is named "SomeLogin":

grant connect on endpoint::endpoint_mirroring to [SomeLogin]
Related Question