Replacing Endpoint Certificates that are near expiration

In my previous post I have explained how Database Mirroring and Service Broker use certificates for endpoint authentication. The only thing validated by SSB/DBM on a certificate are the valid-from date and the expiration date. In fact, even if SSB would not validate these dates, the TLS protocol used underneath by SSB/DBM authentication mechanism would validate these dates. In practice the only one that matter is the expiration date since the valid-from date is usually valid from the moment the certificate was created. Although if you follow this blog you know that I have already talked about a problem that may appear with certificates not yet valid, see http://rusanu.com/2008/08/25/certificate-not-yet-valid.

If you have in care servers that use Certificate based Authentication for Database Mirroring or Service Broker endpoints you must be prepared to replace the certificates used when they expire. Neither DBM nor SSB will not interrupt an existing connection when the certificate used to authenticate expires, but when a new connection is attempted the expired certificate will prevent it from being established resulting and some difficult to diagnose and troubleshoot errors. Luckily the certificates about to expire ca be identified and replaced before they expire, with minimal impact on a production server.

Identifying Endpoints that use Certificates

To find out if the Database Mirroring or Service Broker endpoints are using certificates based authentication run the following queries:

select * from sys.database_mirroring_endpoints;
select * from sys.service_broker_endpoints;

The authentication used by the endpoint is described in the connection_auth_desc column. If it says CERTIFICATE then the endpoint is using certificates for authentication and you must next check the expiration date of the certificate used. To find out just which one is this certificate, check the certificate_id column and then check the sys.certificates metadata catalog to see if the certificate used by the endpoint is about to expire. The expiry_date column will show the certificate expiration date. If the expiration date is near then you need to prepare the certificate replacement.

Replacing Certificates used by Endpoints

The replacement of certificates that are near expiration can be a very smooth operation with no impact on production if done correctly. The procedure is identical for Database Mirroring and for Service Broker endpoints:

1. Create a new certificate.
2. Backup the newly created certificate.
3. Copy the certificate to all the peer servers.
4. Restore the certificate on each peer server in master database, under the same ownership as the old certificate.
5. Alter the endpoint to use the new certificate.
6. Drop the old certificate on each peer.
7. Drop the old certificate on the server hosting the endpoint being updated.

Because the certificates are deployed (exchanged) prior to changing endpoint’s certificate, this procedure ensures that the communication is only stopped for a very short brief during the ALTER statement at step 5. And since we’re deploying the certificates on the peer servers using the same owner as the previously used certificate, there is no need to create a new login and to grant CONNECT permission, the old login is reused and it’s existing permissions are just fine.

Example

I have two SQL Server instances that are exchanging Service Broker messages using certificate based authentication endpoints. The two SQL instances are default instances on the machines REMUSRX64 and VSQL2K5EXPRESS. I’m going to verify if any of the certificates are near expiration and replace any certificate about to expire.

First thing I need to check the endpoints authentication type and find the certificate used, so I’m runnig the following query on REMUSX64:

select connection_auth_desc, certificate_id, * from sys.service_broker_endpoints

The endpoint metadata shows that is using CERTIFICATE authentication and is using the certificate with id 261. So next I can check the certificate expiration date:

select expiry_date, thumbprint, * from master.sys.certificates where certificate_id = 261;

The certificate used by the Service Broker endpoint on REMUSRX64 is going to expire on November 1st. That is quite near so I better go ahead and replace this certificate. BTW you notice that I have also explicitly selected the certificate thumbprint, more on this later. First I’m going to create a new certificate that later will be used be the Service Broker endpoint on REMUSX64 for authentication:

create certificate [REMUSRX64_Nov_2008]
	with subject = 'REMUSRX64 Endpoint Identity',
	start_date = '10/25/2008';

As you see I did specify a start_date in order to avoid the problem I described in my earlier blog post with certificate start date in Eastern hemisphere. Of course you should use a start_date value that matches the day you are doing the replacement operation. Next I’m going to copy the newly created certificate to VSQL2K5EXPRESS. First thing I’ll backup the certificate to a .CER file:

backup certificate [REMUSRX64_Nov_2008]
	to file = 'REMUSRX64_Nov_2008.CER';

Next I’m copying over the REMUSRX64_Nov_2008.CER file to VSQL2K5EXPRESS machine via a normal file copy operation. BTW, because I did not specify an explicit path the file was created in the same folder wheremaster database resides. If the two machines cannot access each other shares I would use something like mail the file or copy it via an USB stick. Certificate files are public and there is no need to protect them during copy operation, so I don’t need to take any precaution because a malicious user can gain no advantage from obtaining any of my public certificate files. Next thing I need to do is to restore the certificate on VSQL2K5EXPRESS under the same ownership (authorization) as the old certificate used for authentication. So first I need to find out what that ownership is, ie. I need to find out who is the user on VSQL2K5EXPRESS that owns the old certificate used by REMUSRX64 for authentication. When I looked up the old used certificate expiration date I also selected the thumbprint, and I’m going to use that thumbprint now to find the user I need on VSQL2K5EXPRESS:

select principal_id, * from master.sys.certificates
	where thumbprint = 0x67BB038B404E24BAA95B28F714C38A39323E1643;

The owner of the certificate is the user with the principal id 6. Why did I use the thumbprint to look up the certificate? Because this is the only bulletproof way to identify the right certificate. I cannot use the name, because the certificate on VSQL2K5EXPRESS can have any name, does not have to match the name used on REMUSRX64. Next I’m going to find the name of the user with principal id 6:

select * from master.sys.database_principals
	where principal_id = 6;

The user is named REMUSRX64_Endpoint and with this knowledge I can now restore the newly created certificate copied from REMUSRX64 on VSQL2K5EXPRESS:

create certificate [REMUSRX64_Endpoint_Certificate_Nov_2008]
	authorization [REMUSRX64_Endpoint]
	from file = 'c:\temp\REMUSRX64_Nov_2008.CER';

Because I used the same ownership (authorization clause) on VSQL2K5EXPRESS as the old certificate used for authentication I do not need to create a login and grant CONNECT permission. The new certificate will authenticate the same login as the old one and thus benefit from the existing permissions.

Now I have in place the deployment needed to switch to the new certificate. Note that during all these operations the old certificate was left in place and communication continued unhindered, using the old certificate for authentication. It is also important to realize that in this example I have shown how to copy the certificate to only one peer (to VSQL2K5EXPRESS). If your instance is communicating with multiple peers, then you must first copy the certificate and do all the operations described above to all the peers before proceeding. With this said, now is the moment we can switch to the new certificate by changing the endpoint properties to use the new certificate on REMUSRX64:

alter endpoint broker
	for service_broker (authentication = certificate [REMUSRX64_Nov_2008]);

With this statement I have switched over to use the new certificate. The endpoint was stopped and started during the ALTER statement and this interrupted any existing connection, but the communication resumed immediately and authentication used the new certificate. We can now validate that the communication is authenticated using the new certificate by running this query on VSQL2K5EXPRESS:

select principal_name, peer_certificate_id, authentication_method, *
	from sys.dm_broker_connections

The peer REMUSRX64 was authenticated to the principal name REMUSRX64_Endpoint based on the certificate with id 260. You can verify that this is the new certificate. If you’re curious what does the authentication method Microsoft Unified Security Proto mean, it is the name of the SChannel SSPI provider which was used for authentication.

With the switch effective the last thing is to clean up the old certificate. It is now safe to DROP the old certificate both on REMUSRX64 and on VSQL2K5EXPRESS as this certificate is no longer used for Service Broker endpoint authentication. I’m not going to show how to do this, is straightforward.

Database Mirroring

In case of Database Mirroring endpoints using certificates for authentication the procedure described above is almost identical, the only difference is that you are going to have to look at the sys.database_mirroring_endpoints metadata catalog instead of sys.service_broker_endpoints. Also note that if you have a witness in your deployment, then my earlier remark about deploying to all peers before altering the endpoint to switch to the new certificate applies to you, since the endpoint has at least two peers. Also because Database Mirroring endpoints tend to be created together as part of mirroring session deployment, the certificates used tend to have near expiration dates. This procedure showed how to replace the certificate used by one endpoint and is is very likely that you will need to repeat the procedure on all the instances involved in a mirroring session: principal, mirror, witness.