Jeremy Carter

These are instructions on how to replace expired certificates which are used for database mirroring.

Here is the following error you will see in the sql error log.

Message
Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.'. [CLIENT: xxx.xxx.xxx.xxx]


First we need to get a bit of information for the endpoint name and logins which are associated with mirroring. The following query will show the endpoint name, the certificate name, as well as the years in which the cert is valid.

SELECT e.name [endpoint_name], c.name [cert_name], c.start_date, c.expiry_date
FROM sys.database_mirroring_endpoints e
INNER JOIN sys.certificates c
	ON e.certificate_id=c.certificate_id

We can also get a list of the logins which have permissions to the endpoint and their associated certs. The following query will return the login name, endpoint name, cert name, as well as the years in which the cert is valid.

SELECT
sp.name AS [login_name],
e.name AS [endpoint_name],
c.name AS [cert_name],
c.start_date,
c.expiry_date
FROM
sys.server_permissions AS prmssn
INNER JOIN sys.endpoints AS e
	ON e.endpoint_id = prmssn.major_id
	and prmssn.class = 105
INNER JOIN sys.server_principals AS sp
	ON sp.principal_id = prmssn.grantee_principal_id
INNER JOIN sys.database_principals AS dp
	ON sp.sid=dp.sid
INNER JOIN sys.certificates c
	ON dp.principal_id=c.principal_id
WHERE e.type=4

Now we can take the information learned in the first two queries and do the real work of replacing the certs.

First on the principal…

--create a new cert for the endpoint
USE master;
CREATE CERTIFICATE [principal_new_cert]
   WITH SUBJECT = 'mirroring cert',
	START_DATE='07/11/2010', --make sure this is a day prior to the current date
	EXPIRY_DATE='07/12/2020'; --make sure this is set out 10-20 years
GO

--backup the cert for the endpoint
BACKUP CERTIFICATE [principal_new_cert] TO FILE = 'c:\principal_new_cert.cer';
GO

--set mirroring to use the new cert
ALTER ENDPOINT DBMirrorEndPoint
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [principal_new_cert])
GO

--finally delete the old cert for the endpoint
DROP CERTIFICATE [old_principal_cert]
GO

Now copy the backup of the principal endpoint cert to the mirror server. Then on the mirror server continue on…

--On the mirror
--drop the old cert for the principal login
DROP CERTIFICATE [old_principal_cert]
GO

--create the new cert using the backup you made on the principal server
CREATE CERTIFICATE [principal_new_cert] AUTHORIZATION PrincipalServerUser
FROM FILE = 'c:\principal_new_cert.cer'
GO

--create a new cert for the endpoint
USE master;
CREATE CERTIFICATE [mirror_new_cert]
   WITH SUBJECT = 'mirroring cert',
	START_DATE='07/11/2010', --make sure this is a day prior to the current date
	EXPIRY_DATE='07/12/2020'; --make sure this is set out 10-20 years
GO

--backup the new cert for the endpoint
BACKUP CERTIFICATE [mirror_new_cert] TO FILE = 'c:\mirror_new_cert.cer';
GO

--set mirroring to use the new cert
ALTER ENDPOINT DBMirrorEndPoint
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [mirror_new_cert])
GO

--finally delete the old cert for the endpoint
DROP CERTIFICATE [old_mirror_cert]

You will now need to copy the mirror endpoint cert over to the principal. You can then finish everything up on the principal.

--drop the old cert for the mirror login
DROP CERTIFICATE [old_mirror_cert]
GO

--create the new cert using the backup you made on the mirror server
CREATE CERTIFICATE [mirror_new_cert] AUTHORIZATION MirrorServerUser
FROM FILE = 'c:\mirror_new_cert.cer'
GO

--finally resume the mirroring session for each database
ALTER DATABASE [mirrored_database_name] SET PARTNER RESUME

And there you have it. Your certs are replaced and hopefully you will never have to worry about replacing them again. …Although if you still have th server in 10 or 20 years you might have bigger problems .