How to get currently used SSL certificate thumbprint from MSSQL

All we need is an easy explanation of the problem, so here it is.

So, how to get currently running SSL certificate (especially thumbprint) from MSSQL using Powershell? It could include SQL commands…

I know that it is in Windows Registry, but if you change it then it will be only applied after MSSQL restart.

So there is undefined blank space between configured cert in the registry and actually the running one.

Thanks!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can check in the SQL Server log (openning it from powershell of with a sp_readerrorlog)

If you go with the TSQL approch, a script like this should do the job

declare @lastReboot datetime;
select @lastReboot = sqlserver_start_time from sys.dm_os_sys_info;
create table #loglist (id int, L_date datetime, size int);
insert into #loglist 
exec sys.sp_enumerrorlogs;

declare @LogID int
select top 1 @LogID=id from #loglist where L_date <= @lastReboot order by id;
set @LogID = @LogID-1;
drop table #loglist;

exec sp_readerrorlog @LogID,1, N'Cert Hash'

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply