By: Matteo Lorini | Updated: 2011-08-01 | Comments (3) | Policy Based Management
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.
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".
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.
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.
(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.
- Evaluate your certificate expiration dates and remember you can ignore the internal certificates.
- Review these related tips:
- SQL Server 2005 Encryption Certificates 101. http://www.mssqltips.com/sqlservertip/1514/sql-server-2008-transparent-data-encryption-getting-started/
- SQL Server 2008 Transparent Data Encryption getting started. http://www.mssqltips.com/sqlservertip/1514/sql-server-2008-transparent-data-encryption-getting-started/
- Managing SQL Server 2005 Master Keys for Encryption http://www.mssqltips.com/sqlservertip/1312/managing-sql-server-2005-master-keys-for-encryption/
- Database level permissions for SQL Server 2005 and 2008 http://www.mssqltips.com/sqlservertip/1718/database-level-permissions-for-sql-server-2005-and-2008/
Last Updated: 2011-08-01
About the author
View all my tips