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:
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.
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.
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)
}
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”.
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'))
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.
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.
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.
There seems to be an issue with the example on the docs @webprofusion. I had to replace some en dashes with real hyphens after copying, but then had trouble with this line:
I was getting an array index out of bounds error until I replaced $ssrsReportManagerName with $ssrsReportManagerName. After that, the script completed but completely removed the SSL binding instead of replacing the certificate binding with the new certificate. I opted to restore the server from a backup to fix it.
I am running SSRS 15.
If anyone has a more recent, working version, please let me know.
@srfrnielsen the example scripts are provided as general guide on how you might achieve something with scripting and you should not test them in production, instead you should confirm they work for you in test then deploy them as you see fit. They are often user contributed and are not necessarily subjected to any testing by the Certify The Web developers.
The script in that example tries to remove the existing bindings before it creates the new binding. I would advise trying to gain an understanding of what the script does before relying on it in production. If you are not a developer I would suggest enlisting the help of a developer to verify the script for your environment.
The “index out of bounds” error should by caught by the try-catch and means you don’t currently have an existing binding that matches.
