Query Network Protocols from T-SQL or PowerShell

Question

Query Network Protocols from T-SQL or PowerShell

webrunner

SSC-Dedicated

Points: 31882
More actions
November 13, 2024 at 5:12 pm

#4481231
Hello experts,
I want to be more organized about the SSL certificates we have bound to SQL Servers. Is there a way to use T-SQL or PowerShell to retrieve that information, especially the cert name and expiration date?
If I can gather such info in a table, I can sort it and predict when to expect the warning, and then update the certs before the patching.
Thanks for any help.
-- webrunner
- This topic was modified 1 month, 1 week ago by webrunner.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 13, 2024 at 7:05 pm

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 1

if you want to extract that information from the OS:

# View certificates in the Local Machine's Personal store
Get-ChildItem -Path Cert:\LocalMachine\My

# Get detailed info for a certificate by thumbprint
$MachineCert |%{
    <# Get detailed info for a certificate by thumbprint #> 
    $CertInfo = $_ 
    $CertPath = $('Cert:\LocalMachine\My\{0}' -f $CertInfo.Thumbprint )
    Get-ChildItem -Path $CertPath #| Format-List -Property *
    }

It's a start ...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

webrunner SSC-Dedicated Points: 31882 More actions · Answer 2

Hi Johan,

Sorry for my delayed reply. Thanks! I'll check it out.

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

henrykangg Newbie Points: 1 More actions · Answer 3

Johan Bijnens wrote:

if you want to extract that information from the OS:

# View certificates in the Local Machine's Personal store
Get-ChildItem -Path Cert:\LocalMachine\My

# Get detailed info for a certificate by thumbprint
$MachineCert |%{
    <# Get detailed info for a certificate by thumbprint #> 
    $CertInfo = $_ 
    $CertPath = $('Cert:\LocalMachine\My\{0}' -f $CertInfo.Thumbprint )
    Get-ChildItem -Path $CertPath #| Format-List -Property *
    }

It's a start ...

@escape road: Thanks a lot for your helpful information.