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.

1 Like
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.

1 Like
3

Just an update - for SSRS 2017, the components of SSRS have been renamed. The updated script (with the same unoptimizations as above) is:

$certSubject = "CN=cert.url.here"
$ssrsServerName = "RS_MSSQLSERVER"
$httpsPort = 443
$ipAddress = "0.0.0.0"

# Find the ssrsServerName by running:
# Get-WmiObject -namespace root\Microsoft\SqlServer\ReportServer -class __Namespace
# take the value of the name field
# note: the first line below just seems to be a test to confirm the proper name, since it returns the 'name' value after filtering for the 'name' in the first place

$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

# the cert thumbnail of the newest certificate
$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()

# the cert thumbnail of the currently bound certificate
$oldthumb = $rsConfig.ListSSLCertificateBindings(1033).CertificateHash.Item([array]::LastIndexOf($rsConfig.ListSSLCertificateBindings(1033).Application, 'ReportServerWebService'))

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

Due to the Issuer change in Nov 2020, the above script should now get the $newthumb from the updated issuer:

$newthumb = (gci -path cert:/LocalMachine/My | Where-Object {$_.Subject.StartsWith($certSubject) -and $_.Issuer.StartsWith("CN=R3")} | Sort-Object -property NotAfter -descending | Select-Object -First 1).Thumbprint.ToLower()
1 Like
5

But why do that at all though? The old and new thumbprint are in:

$result.ManagedItem.CertificateThumbprintHash and $result.ManagedItem.CertificatePreviousThumbprintHash.
6

I’m running Reporting Services 2014 and am running into a couple issues, the first being line 16 where it tries to pull the newly minted cert based on the Issuer of “Lets Encrypt” where as the new Certify the Web now seems to default to “R3”.

$newthumb = (gci -path cert:/LocalMachine/My | Where-Object {$_.Subject.StartsWith($certSubject) -and $_.Issuer.StartsWith("CN=R3")} | Sort-Object -property NotAfter -descending | Select-Object -First 1).Thumbprint.ToLower()

That corrected, I can’t seem to get past the second roadblock and that’s an error on line 16 complaining about “Index was outside the bounds of the array” (error message: https://i.imgur.com/4bWUY1q.png)

Line 16 is as follows:
$oldthumb = $rsConfig.ListSSLCertificateBindings(1033).CertificateHash.Item([array]::LastIndexOf($rsConfig.ListSSLCertificateBindings(1033).Application, 'ReportServerWebApp'))

Any suggestions would be greatly appreciated.

7

I was able to resolve the above. Instead of using ‘ReportServerWebApp’ I replaced it with ‘ReportManager’

The complete script I’m using now for MS Reporting Services 2014 is as follows:

$certSubject = "CN=reports.MYDOMAIN.com"
$ssrsServerName = "RS_MSSQLSERVER"
$httpsPort = 443
$ipAddress = "0.0.0.0"

# Find the ssrsServerName by running:
# Get-WmiObject -namespace root\Microsoft\SqlServer\ReportServer -class __Namespace
# take the value of the name field

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

# the cert thumbnail of the newest certificate
$newthumb = (gci -path cert:/LocalMachine/My | Where-Object {$_.Subject.StartsWith($certSubject) -and $_.Issuer.StartsWith("CN=R3")} | Sort-Object -property NotAfter -descending | Select-Object -First 1).Thumbprint.ToLower()

# the cert thumbnail of the currently bound certificate
$oldthumb = $rsConfig.ListSSLCertificateBindings(1033).CertificateHash.Item([array]::LastIndexOf($rsConfig.ListSSLCertificateBindings(1033).Application, 'ReportManager'))

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

As mentioned, if you are using param($result) to pass in details of the current managed certificate you don’t need to do brittle certificate discovery (if the R3 intermediate changes, this script won’t work anymore) and can instead use the thumbprint info passed in the result.

9

I’ve adapted this for an example on certify docs website, my version uses the results param instead of discovering the new cert thumbprint, allows for errors trying to remove the old binding (e.g. if there isn’t one) and uses variables for the application names. It would be great if someone could try it out to validate it.

10

Thanks for sharing this - I just implemented and validated it against our SQL Server 2017 install; had to change the three $ssrsXX vars at the top as expected but worked perfectly otherwise.

1 Like