Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Internal SQL Server Certificates have expired is this an issue


By:   |   Read Comments (3)   |   Related Tips: More > 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...

create a policy to check for expired certificates within our sql server instance

Enter the following on the New Condition screen and click OK to save.

in ssma under management > policy management

Then click OK again to save the policy.

enter the new condition


Evaluate

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.

when you evaluate the policy it will show the sql server cetificates expired

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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, July 23, 2014 - 7:57:21 PM - Randy Back To Top

Posted too soon.  BOL states that Service Broker checks the expiration date.  Encryption is not affected.   


Wednesday, July 23, 2014 - 7:51:11 PM - Randy Back To Top

I was looking for certificate migration scripts when I came across this.  No luck with scripts.  Anyway, we had to use a set of procedures to use xp_cmdshell to run COBOL from SQL Server.  (Pick up your lower jaw.  Ugly, but it allowed us to migrate off the mainframe - saving $$$$.)  Anyway, we created a certificate to sign the procedures.  It expired 5 years ago.  It appears that the tip applies to our certificate as well.  Wonder if it's a bug that will break the code when we migrate SQL 2005 to SQL 2012?  If it does, I will now be ready for it. 


Monday, August 01, 2011 - 7:23:27 AM - Abhi Back To Top

Thank you Matteo Lorini


Learn more about SQL Server tools