Skip to content

Instantly share code, notes, and snippets.

@stummsft
Created April 5, 2019 00:00
Show Gist options
  • Save stummsft/6c620ddf33c08acb7dd4c7fa8c3b43ea to your computer and use it in GitHub Desktop.
Save stummsft/6c620ddf33c08acb7dd4c7fa8c3b43ea to your computer and use it in GitHub Desktop.
Programmatically set the TLS certificate to be used by a given SQL Server Instance
function Set-SqlTlsCertificate {
[CmdletBinding(SupportsShouldProcess=$true)]
param (
[Parameter()]
[Alias("CN", "MachineName")]
[String[]]$ComputerName = "localhost",
[Parameter()]
[String[]]$InstanceName,
[Parameter()]
[Alias("Thumbprint")]
[ValidatePattern("^[A-F0-9]{40}$")]
[ValidateLength(40,40)]
[ValidateNotNullOrEmpty()]
[String]$CertificateThumbprint
)
begin {
if ($null -eq $InstanceName -or $InstanceName -eq "") {
$setAllInstances = $true;
} else {
$setAllInstances = $false;
}
$anyMatchingInstanceFound = $false;
#Thumbprint MUST be in upper case!
$CertificateThumbprint = $CertificateThumbprint.ToUpper();
$sqlnamespaceRoot = "root\Microsoft\SqlServer";
$serverSettingsQuery = "SELECT * FROM ServerSettings";
$findCertificatesQuery = "SELECT * FROM SecurityCertificate";
}
process {
foreach ($computer in $ComputerName) {
#Namespaces are the different installed SQL support versions
$namespaces = Get-WmiObject -ComputerName $computer -Namespace $sqlnamespaceRoot -Class __Namespace | Where-Object Name -like "ComputerManagement*"
#Prior to SQL 2017, management components required exact version match
#Starting from SQL 2017, a given instance may appear in more than one management component, so we need to
$foundSqlInstances = @();
$foundInstanceNames = @();
$foundMatchingCertificate = $false;
$namespaces | ForEach-Object {
Get-WmiObject -ComputerName $computer -Query $serverSettingsQuery -Namespace "$($sqlnamespaceRoot)\$($_.Name)" | ForEach-Object {
$foundSqlInstances += $_;
}
Get-WmiObject -ComputerName $computer -Query $findCertificatesQuery -Namespace "$($sqlnamespaceRoot)\$($_.Name)" | ForEach-Object {
if ($_.SHA -eq $CertificateThumbprint) {
Write-Debug -Message "Found matching certificate with thumbprint $($CertificateThumbprint) on computer: $($computer)";
$foundMatchingCertificate = $true;
}
}
}
if ($foundMatchingCertificate -eq $false) {
Write-Warning -Message "Could not find matching certificate with thumbprint $($CertificateThumbprint) on computer: $($computer). Verify that the certificate is installed, is valid, and is not expired.";
}
foreach ($sqlInstance in $foundSqlInstances) {
if (($setAllInstances -eq $true -or $sqlInstance.InstanceName -eq $InstanceName) -and $foundInstanceNames -notcontains $sqlInstance.InstanceName) {
$anyMatchingInstanceFound = $true;
$foundInstanceNames += $sqlInstance.InstanceName;
if ($PSCmdlet.ShouldProcess("Install certificate on $($computer)\$($sqlInstance.InstanceName)")) {
$result = $sqlInstance.SetCurrentCertificate($CertificateThumbprint);
if ($result.ReturnValue -ne 0) {
throw "Failed to set TLS certificate. Return value: $($result.ReturnValue)";
}
}
}
}
}
}
end {
if ($anyMatchingInstanceFound -eq $false) {
throw "No SQL instance found!";
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment