By: Mohammed Moinudheen | Last Updated: 2017-04-12 | Comments | Database Mirroring
We have SQL Server database mirroring configured using certificates and we are getting database mirroring failure alerts. Upon investigation we found that the certificates used to configure database mirroring have expired. What are the steps to get the database mirroring working again?
It is assumed that you have SQL Server database mirroring configured using certificates and you are seeing errors in the SQL Server logs of the mirrored servers, as shown below. If you look at the error details, it shows "Certificate expired".
Gather SQL Server Database Mirroring Certificate Details
From the error messages, you can see that the certificate used by the mirroring endpoint has expired. You can check the certificate details by querying sys.certificates catalog view. Use the below query to get the certificate details:
select name,expiry_date,* from sys.certificates
This is what the output looks like from the query.
As the certificates are no longer valid, new certificates need to be created with future expiry_dates. If no expiry_date is given while creating the certificates, the default is one year for expiration of the certificates.
Before creating the new certificates, query the sys.endpoints catalog view to get details about the endpoint used for database mirroring. You would need this for altering the existing endpoint. Use the below query to get the endpoint details:
select * from sys.endpoints
This is what the output looks like from the query.
For getting the ports used for database mirroring on the principal and mirror servers, query the sys.tcp_endpoints catalog view on both servers. Use the below query to get the details of the ports used for database mirroring:
SELECT name,type_desc,port, * FROM sys.tcp_endpoints
Below is the screenshot from the principal server. Port 7022 is used on the on principal. Run the same query on the mirror server to get the port details as we will need it while creating the new certificates.
Create New Certificates for SQL Server Database Mirroring
You can follow the below steps to implement the new certificates.
1) Run this code first on the principal server.
-------------------- RUN THIS FIRST ON PRINICPAL------------------------------------------------------------ Create Certificate principal_user_new_Mar23 With Subject ='principal_user_new_Mar23', expiry_date='03/23/2019' -- Provide far dated expiry date Go --Alter the Endpoint to authenticate using the new certificate alter Endpoint SQL_Mirror State = Started AS TCP (Listener_Port = 7022, Listener_IP = ALL) For Database_Mirroring ( Authentication = Certificate principal_user_new_Mar23, -- Use the new certificate created in above step Role = ALL ) Go --Backup the new certificate and copy it over to the Mirror server Backup Certificate principal_user_new_Mar23 To File ='C:\RELEASES\principal_user_new_Mar23.cer' -- You need to change the drive location to appropriate folder on your server Go
2) Run this code next on the mirror server.
------RUN THIS SECOND ON MIRROR---- Create Certificate mirror_cert_new_Mar23 With Subject ='mirror_cert_new_Mar23', expiry_date='03/23/2019' -- Provide Suitable Expiry date Go --Alter the Endpoint to authenticate using the new certificate Alter Endpoint SQL_Mirror State = Started AS TCP (Listener_Port = 7023, Listener_IP = ALL) For Database_Mirroring ( Authentication = Certificate mirror_cert_new_Mar23, Role = ALL ) Go --Backup the new certificate and copy it over to the Principal server Backup Certificate mirror_cert_new_Mar23 To File ='C:\RELEASES\mirror_cert_new_Mar23.cer'-- You need to change the drive location to appropriate folder on your server Go
3) Ensure the certificates backed up in step 1 and 2 are copied across. That is, the certificate backed up in step 1 on the principal needs to be copied over to the mirror and the certificate backed up in step 2 on the mirror server needs to be copied over to the principal server in the appropriate folder.
4) Run this code next on the principal server.
-- RUN THIS THIRD ON PRINCIPAL--------- Use master Go -- Create a new SQL login with complex password Create Login mirror_new_Mar23 With Password ='[email protected]' GO -- Associate a user with the new Login Create User mirror_user_new_Mar23 for Login mirror_new_Mar23 GO -- Attach the Certificate created on the mirror Instance to the User– Create Certificate mirror_cert_new_Mar23 Authorization mirror_user_new_Mar23 From File ='C:\RELEASES\mirror_cert_new_Mar23.cer' --Provide appropriate folder location GO -- Grant Connect permissions to the new user on the mirroring endpoint Grant Connect on Endpoint::SQL_mirror TO [mirror_new_Mar23] GO
5) Run this code next on the mirror server.
-------RUN THIS FOURTH ON MIRROR Use master Go -- Create a new SQL login with complex password Create Login principal_new_Mar23 With Password ='[email protected]' GO -- Associate a user with the new Login Create User principal_user_new_Mar23 for Login principal_new_Mar23 GO -- Attach the Certificate created on the Principal to the User– Create Certificate principal_user_new_Mar23 Authorization principal_user_new_Mar23 From File ='C:\RELEASES\principal_user_new_Mar23.cer' GO -- Grant Connect permissions to the new user on the mirroring endpoint Grant Connect on Endpoint::SQL_Mirror TO [principal_new_Mar23] GO
With the last step, you will notice that the database mirroring automatically gets fixed and will get back in sync again.
Below is a screenshot from the SQL Server Logs in Management Studio after database mirroring has been fixed.
Cleanup Old SQL Server Database Mirroring Certificates
You can carry out the below steps as part of further maintenance without impacting database mirroring. This is not mandatory, but it would help to keep things tidy on your servers. Querying the sys.certificates catalog view, you can view the new certificates and expiry_dates.
You can also see the old expired certificates. Those expired certificates can be dropped as they are no longer required.
Use the below script and run this on both the principal and mirror servers.
drop certificate principal_cert -- Provide the expired certificate name
drop certificate mirror_cert -- Provide the expired certificate name
You can next delete the old logins that were used by the mirroring endpoints on both the principal and mirror server. As an example, go to the principal server and delete the old mirroring login.
Once done, delete the old associated user from the master database.
Similarly, drop the unused login and user from the mirror server.
- Configure database mirroring using certificates with near dated expiry dates for the purpose of testing this tip
- Follow the steps in this tip in sequence and verify if database mirroring is working fine after issuing new certificates
- Review additional SQL Server Database Mirroring Tips.
Last Updated: 2017-04-12
About the author
View all my tips