2

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?

  • If TCP port 7024 is already used by the endpoint on the SQL server you need to select another TCP port that's not in use endpoint to other server.. have you choose different endpoints? select * from sys.service_broker_endpoints run this and find results. – CR241 Jun 20 '18 at 23:25
  • @CR241 The results show all columns as blank for all servers. Is there something I need to add to that query that I'm missing? – CymanSez Jul 3 '18 at 19:12
0

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]
  • Thanks, @T L and @CR241. I'll give your suggestions a try. – CymanSez Jul 2 '18 at 17:02
  • @T L, I'm not able to bind the value of spr.name because it doesn't exist. I've removed this from the query. Is spr.name required? – CymanSez Jul 3 '18 at 19:30
  • Sorry for the late response, no, I originally tried to link the sys.principals view in the same query but abandoned the idea for some reason. – T L Jul 4 '18 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.