Sql Server Reporting Services (SSRS)


#1

I tackled using Certify the Web to handle SSRS certificates today. I ended up using dns-01 challenge since I use cloudflare, but I did experiment with using a pure powershell script to serve the http-01 challenge, but I’ll post that separately.

SSRS requires the use of WMI to handle certificate bindings, and I ended up with this post script:

$certSubject = "CN=rs.yoursite.org"
$ssrsServerName = "CheckWMIForName"
$httpsPort = 443
$ipAddress = "0.0.0.0"

$wmiName = (Get-WmiObject –namespace root\Microsoft\SqlServer\ReportServer -Filter "Name='$ssrsServerName'"  –class __Namespace).Name
$version = (Get-WmiObject –namespace root\Microsoft\SqlServer\ReportServer\$wmiName  –class __Namespace).Name
$rsConfig = Get-WmiObject –namespace "root\Microsoft\SqlServer\ReportServer\$wmiName\$version\Admin" -class MSReportServer_ConfigurationSetting

$newthumb = (gci -path cert:/LocalMachine/My | Where-Object {$_.Subject.StartsWith($certSubject) -and $_.Issuer.StartsWith("CN=Let's Encrypt Authority")} | Sort-Object -property NotAfter -descending | Select-Object -First 1).Thumbprint.ToLower()
$oldthumb = $rsConfig.ListSSLCertificateBindings(1033).CertificateHash.Item([array]::LastIndexOf($rsConfig.ListSSLCertificateBindings(1033).Application, 'ReportServerWebApp'))

if ($oldthumb -ne $newthumb) {
    $rsConfig.RemoveSSLCertificateBindings('ReportServerWebApp', $oldthumb, $ipAddress, $httpsport, 1033) 
    $rsConfig.RemoveSSLCertificateBindings('ReportServerWebService', $oldthumb, $ipAddress, $httpsport, 1033)
    $rsConfig.CreateSSLCertificateBinding('ReportServerWebApp', $newthumb, $ipAddress, $httpsport, 1033)
    $rsConfig.CreateSSLCertificateBinding('ReportServerWebService', $newthumb, $ipAddress, $httpsport, 1033) 
}

There’s plenty of optimizations that can be done with this script, but this is better than starting from scratch.
The biggest hassle for me was finding the right WMI path for the Admin object.


#2

Nice work!

You could probably skip the part where you search for the cert by subject as the Post Request powershell params will include details of the cert hashes (new and previous) by default in $result.ManagedItem.CertificateThumbprintHash and $result.ManagedItem.CertificatePreviousThumbprintHash. I will update the docs.at https://docs.certifytheweb.com/docs/script-hooks.html to make this clearer.

In the future we’d like to provide a UI that will allow you to specify additional parameters for pre-post request scripts (so this example the config for the RS app name/service name), so that scripts can be a little more general purpose.