Server Service Broker Certificate Not Valid

Question

Hello,

There is an alert configured for the below.

The SQL Server Service Broker certificate is not valid. Replication traffic between the parent and child SQL Servers cannot be encrypted and nor authenticated.

This is used in Configuration Manager.

Please let me know how and where in SQL or configuration manager to check the SQL Server Service Broker certificate is valid or not.

Answer 1

In each database that's relevant, check for expired certificates:

SELECT name, thumbprint, expiration_date, subject
FROM sys.certificates
WHERE expiration_date < GETDATE();

You can also check for statuses related to active conversations:

SELECT conversation_handle, message_type_name, message_body
FROM sys.transmission_queue
WHERE state = 'READY';

If it's expired and you need to replace it, start by creating a new cert:

CREATE CERTIFICATE NewCertificate WITH SUBJECT = 'Service Broker Certificate Replacement', EXPIRY_DATE = '2030-12-31';

Create a new endpoint with the new certificate:

DROP ENDPOINT IF EXISTS ServiceBrokerEndpoint;

CREATE ENDPOINT ServiceBrokerEndpoint STATE = STARTED AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER ( AUTHENTICATION = CERTIFICATE NewCertificate );

IF necessary, then alter the queues to use the new certificate.

You'll probably need to end existing conversations using the previous certificate.

Answer 2

Hi @Boopathi S,

You may run mmc and then check the certificate.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.