By: Matteo Lorini | Updated: 2011-08-01 | Comments (3) | Related: > Policy Based Management
Problem
We decided to create a Policy to check for expired certificates within our SQL Server instance. Once we created the policy and ran it we noticed that some of the internal SQL certificates were expired. We weren't sure if this was an issue or not, so we contacted Microsoft to find out. In this tip we cover what was found and the feedback from Microsoft.
Solution
A while ago we decided to implement a policy to check for expired certificates. The following steps show the policy that we created called "Check Expired Certificates".
Create Policy
In SSMS, under Management > Policy Management right click on Policies and select New Policy... and you will get a screen as follows. Provide a name and under Check condition select New condition...
Enter the following on the New Condition screen and click OK to save.
Then click OK again to save the policy.
Evaluate Policy
The policy should now be listed and to evaluate it right click on the policy name and select Evaluate.
When we evaluated the Policy, it showed that some of the SQL Internal Certificates expired as shown below.
So is this a problem?
Due to the fact that such certificates are on a critical production server, we decided to contact Microsoft Premier Support to figure out if this is a problem that needs to be addressed. Below is the transcript of the phone conversation with Microsoft Premier Support.
(Question) We are concerned whether the expiration of the Microsoft certificates will have adverse effect if we restart the SQL server service.
- (Answer) Certificate-based SQL Server Logins Server principals with names
enclosed by double hash marks (##) are for internal system use only. The following
principals are created from certificates when SQL Server is installed, and should
not be deleted.
- ##MS_SQLResourceSigningCertificate##
- ##MS_SQLReplicationSigningCertificate##
- ##MS_SQLAuthenticatorCertificate##
- ##MS_AgentSigningCertificate##
- ##MS_PolicySigningCertificate##
- ##MS_SchemaSigningCertificateB584020318C2066E11309EBC52BE461291CF6ED6##
(Question) These certificates have expired; will this have any effect on the server if SQL server is restarted?
- (Answer) No, these will not hinder any services of SQL server once restarted.
(Question): How to change the expiration date of these certificates?
- (Answer) As these certificates are generated when SQL is installed and are used internally by SQL server you cannot modify or alter these certificates.
So the bottom line is that this is not an issue and can be ignored.
Next Steps
- Evaluate your certificate expiration dates and remember you can ignore the internal certificates.
- Review these related tips:
About the author
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-08-01
