2

I created a domain-independent 2-node SQL Server 2016 Always On Availability Group using the Microsoft instructions:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/domain-independent-availability-groups?view=sql-server-ver15

One of the steps creates an endpoint using a certificate:

CREATE ENDPOINT DIAG_EP
STATE = STARTED
AS TCP (   
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL
      )
FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE InstanceX_Cert,
    ROLE = ALL
      )

The cluster is working and some months later I want to check on the expiration date of this certificate. So I run this query:

select * from sys.certificates

This query returns the empty set. This can't be right, I know the mirroring endpoint is using a certificate. So then I run this query to list all the mirroring endpoints:

select * from sys.database_mirroring_endpoints

And indeed it says connection_auth_desc = CERTIFICATE and certificate_id = XXX in the response. So it must be using a certificate. None of the columns in the response actually say which certificate or what the expiration date is.

Does anyone have any guidance on how to track down this certificate used in the mirroring endpoint in the SQL Server Always On Availability Group and check its expiration date? Also, how can I update this certificate in the future without causing downtime for the cluster?

Any help would be greatly appreciated!

2
  • 1
    when you run a "select * from sys.certificates" t-sql command, which database context you are in ? are you in master database, or are you in one of the user databases ? May 24, 2020 at 17:20
  • @AlekseyVitsko That was the problem! It wasn't set to the master database. Thank you so much. You may wish to add this as the answer so I can mark it as the answer!
    – A X
    May 24, 2020 at 21:52

1 Answer 1

3

Instance-level certificates are stored in the master database
You will need to set your context to master database, and then check certificates:

use [master]
select * from sys.certificates

Or simply use three-part name in a single command:

select * from [master].sys.certificates
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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