Home > #kravis, #PowerShell, #programming, PowerShell, PowerShell Posts, Technology, TSQL > Certificate :- Test Website SSL Protocols

Certificate :- Test Website SSL Protocols

Certificate :- Test Website SSL Protocols

NOTE: This turned out to be a long post.

The “S” in HTTPS:\\ means that the site has security and is supposed to be secure.  There is a lot more to the “S” though and that’s where the SSL part of the title comes from.  Hypertext Transfer Protocol Secure (HTTPS) is a combination of the Hypertext Transfer Protocol (HTTP) with the Secure Socket Layer (SSL)/Transport Layer Security (TLS) protocol. TLS is an authentication and security protocol widely implemented in browsers and Web servers. So, as you surf around the web, you’ll encounter HTTPS:\\ and HTTP:\\ sites. I would never enter personal information into an HTTP:\\ site as this isn’t secure.  And, supposedly with HTTPS:\\ sites like banks and online shopping there is some expectation that the site is secure enough for us to do our transactions. All these things you should be aware of.

So, as we take precautions for doing business online I’ve recently noticed that so many certificates are expiring on web sites I’m visiting and it’s occurring on an assortment of sites. I use a tool that popups when I encounter issues with certificates. You’d think that a company’s overall strategy for ensuring that security certificates remain up to date and monitored for expiration dates should be in the daily SOP.

After looking into the issue I decided I’d start by searching for something along the lines of this code by  Chris Duck  I already had some simple code that I’ve been using but wanted something where I could modify to suite my needs. In fact found many similar ideas while searching the web and this is how I’ve put my research to use.

Loading and running Test-WebSiteSslProtocols

I made some simple modifications to the script as this location:
begin
{
$ProtocolNames = [System.Security.Authentication.SslProtocols] | gm -static -MemberType Property | ?{ $_.Name -notin @(“Default”, “None”) } | %{ $_.Name }
$global:certinfo = @() #- ADD THIS
}
process
And at this place in the code.

[PSCustomObject]$ProtocolStatus
$global:certinfo = $ProtocolStatus #- ADD THIS

This is the modified function without all the comments.
function Test-WebSiteSslProtocols
{
<#
.SYNOPSIS
Check validity of SSL on web sites visited populate collection to be used for updating into database
Ideas found searching the web
#>

param (
[Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, ValueFromPipeline = $true)]
$URLChecked,
[Parameter(ValueFromPipelineByPropertyName = $true)]
[int]$Port = 443
)
begin
{
$ProtocolNames = [System.Security.Authentication.SslProtocols] | gm -static -MemberType Property | ?{ $_.Name -notin @(“Default”, “None”) } | %{ $_.Name }
$global:certinfo = @()
}
process
{
$ProtocolStatus = [Ordered]@{ }
$ProtocolStatus.Add(“URLChecked”, $URLChecked)
$ProtocolStatus.Add(“Port”, $Port)
$ProtocolStatus.Add(“KeyLength”, $null)
$ProtocolStatus.Add(“SignatureAlgorithm”, $null)

$ProtocolNames | %{
$ProtocolName = $_
$Socket = New-Object System.Net.Sockets.Socket([System.Net.Sockets.SocketType]::Stream, [System.Net.Sockets.ProtocolType]::Tcp)
$Socket.Connect($URLChecked, $Port)
try
{
$NetStream = New-Object System.Net.Sockets.NetworkStream($Socket, $true)
$SslStream = New-Object System.Net.Security.SslStream($NetStream, $true)
$SslStream.AuthenticateAsClient($URLChecked, $null, $ProtocolName, $false)
$RemoteCertificate = [System.Security.Cryptography.X509Certificates.X509Certificate2]$SslStream.RemoteCertificate
$ProtocolStatus[“KeyLength”] = $RemoteCertificate.PublicKey.Key.KeySize
$ProtocolStatus[“SignatureAlgorithm”] = $RemoteCertificate.SignatureAlgorithm.FriendlyName
$ProtocolStatus[“Certificate”] = $RemoteCertificate
$ProtocolStatus.Add($ProtocolName, $true)
}
catch
{
$ProtocolStatus.Add($ProtocolName, $false)
}
finally
{
$SslStream.Close()
}
}
[PSCustomObject]$ProtocolStatus
$global:certinfo = $ProtocolStatus
}
}

The [PSCustomObject]$ProtocolStatus is local to the function. As I wanted to use this for input into a function to update
a database I’ve added $global:certinfo = $ProtocolStatus
$global:certinfo can now be used as input to other functions.

I’ll be investigating running this same process in the cloud where it would be cool cataloging information contained in certificates or to look
for anomalies that might be found in a certificate.

Running the function (Test-WebSiteSslProtocols http://www.chase.com) above will return as a data set:

As I’ve also added the global collection which will allow you to drill down into the results.

Peering into the global collection

I’ve used 3 different methods to display information from the collection.

  1. $global:certinfo
  2. $global:certinfo.Certificate
  3. $global:certinfo.Certificate | format-list

As you see each returns varying views that can be used for further analysis or updating into a database.  With method 1($global:certinfo) we have a collection of values. ComputerName,
Port, KeyLength, SignatureAlgorithm, Ssl2, Ssl3,  Tls, Tls11,  and Tls12 are single values that don’t require a drill down to collect populate values for updating into a database.

Certificates returns a collection.  Item 2 ($global:certinfo.Certificate), dose not return enough visual information I use Item 3. $global:certinfo.Certificate | format-list

as returned by or function to get web records.

Extensions is returned as an object. As of yet I’ve not found a sql datatype at least in 2012 to use for import directly. So, I’ve created this snippet that can extract and serialize into xml.

To see all the elements available I’m using method 3( $global:certinfo.certificate | Format-List) As this returns the Extensions information.

The area that contains Subject, Issuer, Thumbprint, FriendlyName, NotBefore, NotAfter and Extensions.  Extensions contains another collection for the Oid values.

$global:certinfo.certificate.Extensions and $global:certinfo.certificate.Extensions.Oid (not very friendly)

The following code snippet I’ve wrapped into a function just for simplicity. Just strait code without try/catch blocks at least for now. 🙂

This function, takes the the $global:certinfo.certificate.Extensions data and generates a more friendly way to view the data.

function CompactExtensions() # Also, in Insert-SslWebSite function (slightly different format)
$global:MyExt = @()
$global:ext = @()
$Extensions = & {
for ($i = 0; $i -le $global:certinfo.Certificate.Extensions.count – 1; $i++)
{
Write-Output ([pscustomobject]@{

Extensions = & {
Write-Output ([pscustomobject]@{
ValueU = $global:certinfo.Certificate.Extensions.EnhancedKeyUsages[$i].Value
FriendlyNameU = $global:certinfo.Certificate.Extensions.EnhancedKeyUsages[$i].FriendlyName
Value = $global:certinfo.Certificate.Extensions.oid[$i].Value
FriendlyName = $global:certinfo.Certificate.Extensions.oid[$i].FriendlyName
RawData = ([string]::Join(“,”, $global:certinfo.Certificate.Extensions[$i].RawData))
})
# #End foreach objectcollection
} #End Object Collection$
})
}
}
$global:ext = $extensions
$global:MyExt = $global:ext.extensions

$EnhancedKeyUsageList = & {
Write-Output ([pscustomobject]@{
EnhancedKeyUsageList = {
foreach ($itm in ($global:certinfo.Certificate.Extensions.EnhancedKeyUsages))
{
Write-Output ([pscustomobject]@{
Value = $itm.Value
FriendlyName = $itm.FriendlyName
})
} #End foreach objectcollection
} #End Object Collection
})
}
}

When you run CompactExtensions the Oid collection is extracted and merged into $global:extensions and when presented in this way is more readable.

A version of CompactExtensions is also in the Insert-SslWebSite

As, I want to insert or update records into a database  I want to convert the $global:extensions.extensions into xml for insertion into the database field extensions.

So, using a small piece of .NET $ExtensionsXml = [System.Management.Automation.PSSerializer]::Serialize($global:extensions.extensions, 3)

has created an in memory variable that will be used to input into the extensions field in the table.

Database Fields.

Adding this information into a database will allow for further analysis.  I’ve created (WIP) an API for backend calls to assist with parsing and validating information in the Certificate Subject field and OID values from the extensions xml field.  My API tracks OIDs, serial numbers and generally information contained in the certificate. Still WIP but soon to be published.

CertificateSubject
CN=www.chase.com, OU=GTI GNS, O=JPMorgan Chase and Co., STREET=270 Park Ave, L=New York, S=New York, PostalCode=10017, C=US, SERIALNUMBER=0691011, OID.2.5.4.15=Private Organization, OID.1.3.6.1.4.1.311.60.2.1.2=Delaware, OID.1.3.6.1.4.1.311.60.2.1.3=US
CN=www.umpquabank.com, OU=Umpqua Bank, O=Umpqua Bank, STREET=445 SE Main St., L=Roseburg, S=Oregon, PostalCode=97470, C=US, SERIALNUMBER=143662, OID.2.5.4.15=Private Organization, OID.1.3.6.1.4.1.311.60.2.1.3=US

My first pass at setting up the table that I needed used varchar and nvarchar on fields.

CREATE TABLE [dbo].[SSLByWebSite](
 [URLChecked] [varchar](255) NULL,
 [Port] INT NULL,
 [KeyLength] [varchar](255) NULL,
 [SignatureAlgorithm] [varchar](255) NULL,
 [Ssl2] BIT NULL,
 [Ssl3] BIT NULL,
 [CertificateSubject] [nvarchar](max) NULL,
 [Extensions] [nvarchar](max) NULL,
 [CertificateIssuer] [varchar](255) NULL,
 [CertificateSerialNumber] [varchar](255) NULL,
 [CertificateNotBefore] [varchar](255) NULL,
 [CertificateNotAfter] [varchar](255) NULL,
 [CertificateThumbprint] [varchar](255) NULL,
 [Tls] BIT NULL,
 [Tls11] BIT NULL,
 [Tls12] BIT NULL
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Function to upload into database.

The fields that are in the table are represented in the Insert-SslWebSite function.

This function builds up the parameter list that is passed into SQL to be inserted into the table.

Currently no data is checked for dups, nor indexed. In this release. I will add at least one to URLChecked as I do want to make delta

comparison checks on future calls to the web sites.

function Insert-SslWebSite ()
{ # Currently not manditory
param (
[string]$URLChecked = $global:certinfo.URLChecked
,
[string]$ServerName = $env:servername
,
[int]$NewCompPKID
)
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Smo’) | out-null

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = “Server=$ServerName; Database=<DATABASENAME>; Integrated Security=true”
$conn.Open()

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = “INSERT INTO SSLByWebSite(URLChecked,Port `
,KeyLength,SignatureAlgorithm,Ssl2,Ssl3,CertificateSubject,Extensions,CertificateIssuer,CertificateSerialNumber`
,CertificateNotBefore, CertificateNotAfter, CertificateThumbprint, Tls,Tls11, Tls12) `
VALUES (@URLChecked, @Port, @KeyLength, @SignatureAlgorithm, @Ssl2, @Ssl3
,@CertificateSubject, @Extensions, @CertificateIssuer `
,@CertificateSerialNumber `
,@CertificateNotBefore `
,@CertificateNotAfter `
,@CertificateThumbprint `
,@Tls `
,@Tls11 `
,@Tls12);”

if ($global:certinfo.KeyLength) { “has VALUE” }
else { $global:certinfo.KeyLength = 0 }

$cmd.Connection = $conn
$CompInfoEntryDate = Get-Date

# Split Extensions prior to insertion into database
$Extensions = & {
for ($i = 0; $i -le $global:certinfo.Certificate.Extensions.count – 1; $i++)
{
Write-Output ([pscustomobject]@{
Extensions = & {

Write-Output ([pscustomobject]@{
EnhancedKeyUsageList = & {
TRY
{
foreach ($itm in ($global:certinfo.Certificate.Extensions[$i].EnhancedKeyUsages))
{
Write-Output ([pscustomobject]@{
Value = $itm.Value
FriendlyName = $itm.FriendlyName
})
} #End foreach objectcollection
} #End Object Collection
catch [System.IO.IOException] {
}
# Catch all other exceptions thrown by one of those commands
catch
{
# not really doing anything with these but here in case
}
# Execute these commands even if there is an exception thrown from the try block
finally
{
# not really doing anything with these but here in case
}
}
Critical = $global:certinfo.Certificate.Extensions[$i].Critical
Value = $global:certinfo.Certificate.Extensions.oid[$i].Value
FriendlyName = $global:certinfo.Certificate.Extensions.oid[$i].FriendlyName
RawData = ([string]::Join(“,”, $global:certinfo.Certificate.Extensions[$i].RawData))
})
# #End foreach objectcollection
} #End Object Collection$
})
}
}
# Quick export and import as string to for db insert
# Used as example. In practice can use different methods to generate. Both methods work great
# $extensions.extensions | export-clixml c:\temp\temp.txt
# [string]$mdxml = get-Content c:\temp\temp.txt # Value is inserted as a string
# $extensions.extensions | export-clixml c:\temp\temp.txt
$Depth = 3
[string]$mdxml = [System.Management.Automation.PSSerializer]::Serialize($extensions.extensions, $Depth)

# $mdxml = “”
$cmd.Parameters.AddWithValue(“@URLChecked”, $global:certinfo.URLChecked) | Out-Null
$cmd.Parameters.AddWithValue(“@Port”, $global:certinfo.Port) | Out-Null
$cmd.Parameters.AddWithValue(“@KeyLength”, $global:certinfo.KeyLength) | Out-Null
$cmd.Parameters.AddWithValue(“@SignatureAlgorithm”, $global:certinfo.SignatureAlgorithm) | Out-Null
$cmd.Parameters.AddWithValue(“@Ssl2”, $global:certinfo.Ssl2) | Out-Null
$cmd.Parameters.AddWithValue(“@Ssl3”, $global:certinfo.Ssl3) | Out-Null
$cmd.Parameters.AddWithValue(“@CertificateSubject”, $global:certinfo.Certificate.Subject) | Out-Null
$cmd.Parameters.AddWithValue(“@Extensions”, $mdxml) | Out-Null
$cmd.Parameters.AddWithValue(“@CertificateIssuer”, $global:certinfo.Certificate.Issuer) | Out-Null
$cmd.Parameters.AddWithValue(“@CertificateSerialNumber”, $global:certinfo.Certificate.SerialNumber) | Out-Null
$cmd.Parameters.AddWithValue(“@CertificateNotBefore”, $global:certinfo.Certificate.NotBefore) | Out-Null
$cmd.Parameters.AddWithValue(“@CertificateNotAfter”, $global:certinfo.Certificate.NotAfter) | Out-Null
$cmd.Parameters.AddWithValue(“@CertificateThumbprint”, $global:certinfo.Certificate.Thumbprint) | Out-Null
$cmd.Parameters.AddWithValue(“@Tls”, $global:certinfo.Tls) | Out-Null
$cmd.Parameters.AddWithValue(“@Tls11”, $global:certinfo.Tls11) | Out-Null
$cmd.Parameters.AddWithValue(“@Tls12”, $global:certinfo.Tls12) | Out-Null

$cmd.ExecuteNonQuery() | Out-Null

$conn.Close()
}

Once you have both functions implemented it’s easy to run as follows:

Test-WebSiteSslProtocols http://www.chase.com | Insert-SslWebSite.

It’s nice to be able to pipe this into the Insert-SslWebSite function. And the results of these simple methods show xml for extensions and an inserted

record for URLChecked. I will add a key to URLChecked. 🙂

I intend on placing all code with comments on gethub after I complete my testing and some minor updates to functions to allow for parallel workflows.

So, in a future post I’ll layout how I use PowerShell with Workflows and running parallel while reading in list of websites I want to monitor.

In larger shops this can be beneficial.

Let me know what you think.

Joseph

  1. Anonymous1
    April 25, 2017 at 9:18 pm

    Looks good!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: