info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



Powershell Functions to check if TLS 1.2 updates are required
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-01-19









Powershell Code:


# Functions to check if TLS 1.2 updates are required
# Script currently supports checking for the following:
# a. Check if SQL Server Native Client can support TLS 1.2
# b. Check if Microsoft ODBC Driver for SQL Server can support TLS 1.2
# This script is restricted to work on x64 and x86 platforms

CLS

Function Check-Sqlncli
{
# Fetch the different Native Client installations found on the machine
Write-Host "[List of SQL Server Native Clients installed (from Registry)...]"
$sqlncli = Get-InstalledPrograms | Where-Object {$_.DisplayName -like "*Native Client*" -and $_.Publisher -like "*Microsoft*"} | Select DisplayName,DisplayVersion
# Fetch the different Native Client installations found on the machine
$sqlncli
Write-Host "`n"

Write-Host "[Check if TLS 1.2 updates are required...]"
# Check and report if an update is required for each entry found
foreach ($cli in $sqlncli)
{
# SQL Server 2012 and 2014
if ($cli.DisplayVersion.Split(".")[2] -lt 6538 -and $cli.DisplayVersion.Split(".")[0] -eq 11) #Example: 11.3.6598.0 (6598 > 6538 and 11) (Pass)
{
Write-Host $cli.DisplayName "with version" $cli.DisplayVersion " needs to be updated to use TLS 1.2" -ForegroundColor Red
}
# SQL Server 2008
elseif ($cli.DisplayVersion.Split(".")[2] -lt 6543 -and $cli.DisplayVersion.Split(".")[1] -eq 0 -and $cli.DisplayVersion.Split(".")[0] -eq 10)
{
Write-Host $cli.DisplayName "with version" $cli.DisplayVersion " needs to be updated to use TLS 1.2" -ForegroundColor Red
}
# SQL Server 2008 R2
elseif ($cli.DisplayVersion.Split(".")[2] -lt 6537 -and ($cli.DisplayVersion.Split(".")[1] -eq 50 -or $cli.DisplayVersion.Split(".")[1] -eq 51) -and $cli.DisplayVersion.Split(".")[0] -eq 10)
{
Write-Host $cli.DisplayName "with version" $cli.DisplayVersion " needs to be updated to use TLS 1.2" -ForegroundColor Red
}
else
{
Write-Host $cli.DisplayName "with version" $cli.DisplayVersion " supports TLS 1.2" -ForegroundColor Green
}
}
}

Function Check-SqlODBC($server)
{
# Fetch the different MS SQL ODBC installations found on the machine
#$sqlodbc = Get-WmiObject -Class Win32reg_AddRemovePrograms | Where-Object {$_.DisplayName -like "*ODBC*" -and $_.Publisher -like "*Microsoft*"} | Select DisplayName,Version

Write-Host "`n"
Write-Host "`n"
Write-Host "[List of ODBC installed (from Registry)...]"
$sqlodbc = Get-InstalledPrograms | Where-Object {$_.DisplayName -like "*ODBC*" -and $_.Publisher -like "*Microsoft*"} | Select DisplayName,DisplayVersion
$sqlodbc
# Check and report if an update is required for each entry found
foreach ($cli in $sqlodbc)
{
# SQL Server 2012 and 2014
if ($cli.DisplayVersion.Split(".")[2] -lt 4219 -and $cli.DisplayVersion.Split(".")[0] -eq 12)
{
Write-Host $cli.DisplayName "with version" $cli.DisplayVersion " needs to be updated to use TLS 1.2" -ForegroundColor Red
}
else
{
Write-Host $cli.DisplayName "with version" $cli.DisplayVersion " supports TLS 1.2" -ForegroundColor Green
}
}
}

<#
Get-InstalledPrograms
#>
Function Get-InstalledPrograms()
{
$array = @()
#Define the variable to hold the location of Currently Installed Programs
$UninstallKey="SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall"
#Create an instance of the Registry Object and open the HKLM base key
$reg=[microsoft.win32.registrykey]::OpenRemoteBaseKey('LocalMachine', $env:COMPUTERNAME)
#Drill down into the Uninstall key using the OpenSubKey Method
$regkey=$reg.OpenSubKey($UninstallKey)
#Retrieve an array of string that contain all the subkey names
$subkeys=$regkey.GetSubKeyNames()
#Open each Subkey and use GetValue Method to return the required values for each
foreach ($key in $subkeys)
{
$thisKey=$UninstallKey+"\\"+$key
$thisSubKey=$reg.OpenSubKey($thisKey)
$obj = New-Object PSObject
$obj | Add-Member -MemberType NoteProperty -Name "ComputerName" -Value $env:COMPUTERNAME
$obj | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value $($thisSubKey.GetValue("DisplayName"))
$obj | Add-Member -MemberType NoteProperty -Name "DisplayVersion" -Value $($thisSubKey.GetValue("DisplayVersion"))
$obj | Add-Member -MemberType NoteProperty -Name "InstallLocation" -Value $($thisSubKey.GetValue("InstallLocation"))
$obj | Add-Member -MemberType NoteProperty -Name "Publisher" -Value $($thisSubKey.GetValue("Publisher"))
$array += $obj
}
return $array

}

# Call the functions
Check-Sqlncli $env:COMPUTERNAME
Check-SqlODBC $env:COMPUTERNAME




Resources:

Use the below link to determine whether your current version of SQL Server already has support for TLS 1.2 or whether you have to download an update to enable TLS 1.2 support. Use the download links in the table to obtain the server updates that are applicable to your environment.

TLS 1.2 support for Microsoft SQL Server

TLS 1.2 Support for SQL Server 2008, 2008 R2, 2012 and 2014