Pulling data on SSL expiration from SQL server

Question

I am trying to figure this out and seems to be out of thoughts and need your inputs to help me figure out on this.

We have implemented SSL across our 200+ servers, SQL2012 & 2014.

These cert are valid for 9 months . These are from vendor symantec.

Now the problem is tracking when they get expired. Keeping a manual track of these many servers are tedious.

Is there a way we can pull this up from SQL to get the information and create an SSRS report to check when they are going to expire?

• Solomon Rutzky: How do you currently go about checking the expiration dates on each server?
• BeginnerDBA: we track manually when it was implemented and calculate the day from thereafter on excel. That is lame , i know. But there should be a way to figure this out.
• Solomon Rutzky: As far as I can see, that info only exists in the registry...Do you have a way to query the registry across your servers? I'm sure it could be scripted into a CMD script.
• BeginnerDBA: @SolomonRutzky, As i am new at this, not sure on querying registry. However just to let you know, we get certificate from symantec, register them under mmc console and then once thumbprint is generated we passed that value under certificate in registry for location--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<Instance _name>\MSSQLServer\SuperSocketNetLib
• Solomon Rutzky: Correct. So it comes down to where the certificate store is. If not in the registry, then somewhere else, but still not in SQL Server. I / we just need to find the MS tool for looking at the certificate store. There is a command line tool for querying the registry, I just need to find it again.

Answer 1

The best way to do this is to use powershell to connect to the servers. You can do this without using any 3rd party modules as showed at sqlserverdbknowledge.wordpress.com

or use dbatools to connect to the servers and pull the information with Get-DbaNetworkCertificate

#$SQLServers = get-DbaRegServer -SqlInstance [CMS] -Group "GROUP\Subgroup"
$SQLServers = "Server2Check"
$Certificates = Get-DbaNetworkCertificate -ComputerName $SQLServers
$Certificates |Where-Object {($_.Expires) -le ([datetime](Get-Date).AddDays(90)) } 

Answer 2

Is the data present in the expiry_date column of sys.certificates in each DB or in master.sys.certificates, to be pulled out and reported upon?

• BeginnerDBA: when i query select * from master.sys.certificates i get the data
• BeginnerDBA: Also i checked the column for thumbprint, but it seems none of the certificate ID's match with the one we implemented, as i cannot find the thumbprint listed on mine matching with them
• Solomon Rutzky: @BeginnerDBA As far as I can tell from all of the setup instructions that I have found, these Certificates are not loaded into SQL Server, so I wouldn't expect them to be found in SQL Server.
• jrdevdba: I don't think that has the relevant expiration date. The sys.certificates entries appear to be for certificates that are within databases (such as for TDE). I don't see the SSL cert information for the cert we have bound in the Network Protocols of the SQL Server Configuration Manager. Having said that, I'm surprised there is not a T-SQL way to retrieve this information.