Configure Reporting Services SSL Binding with WMI & PowerShell

Recently I’ve been building some scripts in PowerShell  to automate the deployment of business inteligence bits like: SSAS, SSRS, SSIS, Databases,…

And one of the tasks was to configure the SSRS (Reporting Services) with ssl binding. The problem is that the only way to do that is with the SSRS WMI Provider.

With powershell we have a cmdlet “get-wmiobject” that make things a lot easier, this cmdlet builds a “proxy” to a wmi class and let us call the methods in this class.

All we need to do is to obtain an instance of the WMI Class ”MSReportServer_ConfigurationSetting”

$serverClass = get-wmiobject -namespace “rootMicrosoftSqlServerReportServerRS_sql2008v10Admin” -class “MSReportServer_ConfigurationSetting”

Reserve the Url calling the “ReserveURL” method

$result = $serverClass.ReserveURL(“ReportServerWebService”, “https://mysite:443”, 2070)

This method receives the folowing parameters:

  • Name of the SSRS web application: ReportServerWebService or ReportManager
  • The url to be reserved in http.sys
  • The locale id (LCID) of the return messages, in this case I’am using Portugal LCID

Then I need to call the “CreateSSLCertificateBinding” method

$result = $serverClass.CreateSSLCertificateBinding(“ReportServerWebService”, “‎e9b993f5a5101bf9bea71896ffc07118b9ca2dcc”, “0.0.0.0”, 443, 2070)    

This method receives the folowing parameters:

  • Name of the SSRS web application
  • The certificate hash or thumbprint
  • The Ip address of the webapplication, in this case I use Wildcard IP address 0.0.0.0
  • The ssl port
  • The LCID

The sintax of members & methods of the WMI Class ”MSReportServer_ConfigurationSetting” can be found here:

http://technet.microsoft.com/en-us/library/ms154070.aspx

To wrap it up, I will post a complete powershell script solution to this problem:

function Config-SSRSSystemConfiguration($sslUrl, $certHash, $sslPort)
{

# The .ToLower() avoids the error “A Secure Sockets Layer (SSL) certificate is not configured on the Web site.” (Thanks Michel)

$certHash = $certHash.ToLower()

Write-Output “Configure SSRS SSL binding”

$serverClass = get-wmiobject -namespace “rootMicrosoftSqlServerReportServerRS_sql2008v10Admin” -class “MSReportServer_ConfigurationSetting”

if ($serverClass -eq $null) { throw “Cannot find wmi class” }
$lcid = [System.Globalization.CultureInfo]::GetCultureInfo(“pt-PT”).LCID
$result = $serverClass.RemoveURL(“ReportServerWebService”, $sslUrl, $lcid)
if (!($result.HRESULT -eq 0)) { write-error $result.Error }
$result = $serverClass.ReserveURL(“ReportServerWebService”, $sslUrl, $lcid)
if (!($result.HRESULT -eq 0)) { write-error $result.Error }
$result = $serverClass.RemoveSSLCertificateBindings(“ReportServerWebService”, $certHash, “0.0.0.0”, $sslPort, $lcid)
if (!($result.HRESULT -eq 0)) { write-error $result.Error }
$result = $serverClass.CreateSSLCertificateBinding(“ReportServerWebService”, $certHash, “0.0.0.0”, $sslPort, $lcid)
if (!($result.HRESULT -eq 0)) { write-error $result.Error }
$result = $serverClass.RemoveURL(“ReportManager”, $sslUrl, $lcid)
if (!($result.HRESULT -eq 0)) { write-error $result.Error }
$result = $serverClass.ReserveURL(“ReportManager”, $sslUrl, $lcid)
if (!($result.HRESULT -eq 0)) { write-error $result.Error }
$result = $serverClass.RemoveSSLCertificateBindings(“ReportManager”, $certHash, “0.0.0.0”, $sslPort, $lcid)
if (!($result.HRESULT -eq 0)) { write-error $result.Error }
$result = $serverClass.CreateSSLCertificateBinding(“ReportManager”, $certHash, “0.0.0.0”, $sslPort, $lcid)
if (!($result.HRESULT -eq 0)) { write-error $result.Error }
}

#Create SSL Certificate

$certificatesFolder = “c:Certificates”
$cn = “mysite”

& makecert -r -pe -n CN=”$cn” -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localmachine `
-sky exchange -sp “Microsoft RSA SChannel Cryptographic Provider” -sy 12 “$certificatesFolderSSLCert.cer”

$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2(“$certificatesFolderSSLCert.cer”)

$certHash = $cert.Thumbprint

#Configure the SSL binding
Config-SSRSSystemConfiguration “https://$($cn):443″ $certHash 443

In this script I create the certificate with “makecert” command, and I load it to obtain the certificate hash.

In the powershell function “Config-SSRSSystemConfiguration”  before calling the methods (I had described earlier) I call the respective remove method witch signature is similar.

Note: This was tested in SSRS 2008, do not know if it works on SSRS 2005

Hope it helps.

Advertisements

9 thoughts on “Configure Reporting Services SSL Binding with WMI & PowerShell

  1. Hi Rui,

    Excellent blog post. The script is working only one thing:

    When i run the script the binding/reservation is created okay. And it is working too. But when I open the reporting configuration tool I can’t see the just created binding. Any idea how to see the binding created using the script also being displayed in this configuration tool ?
    Thanks

    Michel

    • Hi Michel, thank you. Its been a while since I had made this script so I dont remember this behavior, but when I have a oportunity I will check this. But maybe the tool only lists endpoints in the reportserver.rsconfig and the script need to change the config to be listed in the tool to. Best Regards

  2. I was using ur script but i am having error as below.

    ————————————————————————————————————
    PS C:\Users> C:\Users\Administrator\Desktop\sslbinding.ps1
    Configure SSRS SSL binding
    Config-SSRSSystemConfiguration : A Secure Sockets Layer (SSL) certificate is not configured on the Web site.
    At C:\Users\Administrator\Desktop\sslbinding.ps1:55 char:31
    + Config-SSRSSystemConfiguration <<<< "https://$($cn):443" $certHash 443
    + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Config-SSRSSystemConfiguration

    Config-SSRSSystemConfiguration : A Secure Sockets Layer (SSL) certificate is not configured on the Web site.
    At C:\Users\Administrator\Desktop\sslbinding.ps1:55 char:31
    + Config-SSRSSystemConfiguration <<<< "https://$($cn):443" $certHash 443
    + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Config-SSRSSystemConfiguration

      • I am using sql 2008 r2.
        the erro happens when we bind the certificate
        $result = $serverClass.CreateSSLCertificateBinding(“ReportManager”, $certHash, “0.0.0.0”, $sslPort, $lcid)

        it happens for both ReportServerWebService and ReportManager

      • Ok, this script was only tested and used against SQL 2008, maybe some adjustments are needed to work in 2008 R2, remember that there is a new version of the WMI class in 2008 R2: http://technet.microsoft.com/en-us/library/ms154070(v=sql.105).aspx

        I’am sorry but I do not have a 2008 R2 environment suited to test this right now.

        But I found this bug on connect: “http://connect.microsoft.com/SQLServer/feedback/details/576216/ssrs-fails-to-upgrade-from-sql-2008-to-sql-2008-r2”.

        In this case the SSL address was already reserved and maybe the script is not removing it properly. Can you check that?

        Best Regards

  3. Hi,

    Nice post. Just one addition (a importand one).
    Make sure you make the cert hash lower case ! Before supplied it to the CreateSSLCertificateBinding command!!
    So:
    $certhash = $certhash.Tolower()

    This will fix the error : “A Secure Sockets Layer (SSL) certificate is not configured on the Web site.”

    Michel

  4. Terrific work! That is the type of information that
    are meant to be shared across the web. Shame on Google for now not positioning this post higher!

    Come on over and talk over with my site . Thank you =)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s