Hey guys,
Any way to monitor Microsoft SQL Server Certificates for expiration? I tried the cert monitor, but after trying every setting, still get a configuration error.
Is this possible? They are internal CA certs, but we have a max on how long they can be issued for, and tons of dev, prod instances, littered everywhere. Would be great to monitor select ones. Would need expiration, not validity.
Dear Dennis,
We don't support SSL Expiry monitor for SQL Servers at the moment. However, you can write a custom plugin to monitor the expiry date.
Attaching a simple Custom Plugin that I wrote that will get the expiry days in the server in C#. This would help in monitoring the certificates in the server. You can configure the threshold such that the expirydays is less than your threshold value. I set mine as 60 days.
I have attached the dll and the source code for the plugin. Follow the steps to install the plugin from here and let me know if it works for you.
The crux of the code is below
IDictionary<String, object> pluginCollector = new Dictionary<String, object>();
var store = new X509Store(StoreName.My, StoreLocation.LocalMachine);
store.Open(OpenFlags.ReadOnly);
List<SSLCertificateInfo> certs = new List<SSLCertificateInfo>();
foreach (X509Certificate2 mCert in store.Certificates)
{
SSLCertificateInfo sslcert = new SSLCertificateInfo()
{
FriendlyName = mCert.FriendlyName,
Thumbprint = mCert.Thumbprint,
SerialNumber = mCert.SerialNumber,
DateString = mCert.GetExpirationDateString()
};
pluginCollector.Add("ExpiryInDays_"+ sslcert.FriendlyName, sslcert.ExpiryInDays);
}
The StoreName in X509Store will vary based on the certificate.
Note: This plugin is for the server in which the SQL Server resides and not the SQL Server application itself.
-Jasper
Product Manager, Site24x7