CODEWITHSUNDEEP
sql-servercertificatedmv
Owen McGee
Owen McGee

Reputation: 19

Is there a DMV that tells me what type of certificate is used in the current database connections

I need to know if the certificate signed externally , added to the SQL service and given to the application is actually being used because MS has a nasty habit of creating self-signed sha1 certificates in older supported version and these are deemed insecure these days.

My aim is to force the apps to use the correct certificate and to identify those app connections which aren't using the appropriate certificate.

I have searched the internet and DMVs and can't find the information.

Upvotes: 1

Views: 417

Answers (1)

Charlieface
Charlieface

Reputation: 72229

Whether the certificate is self-signed, you can't get it in TSQL, it's only available from the registry.

You can use Powershell though. Set the instance name on the first line or use a loop.

$instancename = YourInstanceHere;
$thumprint = Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instancename\MSSQLServer\SuperSocketNetLib" -Name Certificate;
if ($thumprint -eq $null)
{
    "No certificate set";
}
else
{
    $cert = (dir -Path cert:\$thumprint -Recurse)[0];
    if ($cert -eq $null)
    {
        "No certificate found";
    else
    {
        "Is Self Signed $($cert.Subject -eq $cert.Issuer)";
    }
}

Note that the server only checks the registry at startup, so may be using an old certificate if it hasn't been restarted since it was changed.

If you want to know whether connections are using encryption, you can query sys.dm_exec_connections.encrypt_option

select
  c.session_id,
  c.encrypt_option,
  c.client_net_address,
  s.host_name,
  s.database_id,
  s.login_name
from sys.dm_exec_connections c
join sys.dm_exec_sessions s on s.session_id = c.session_id;

Upvotes: 0

Related Questions