SSL Support

[microsoft]

SSL Support

[HealisticEngineer]

Hi Guys,

I've created my own solution to the problem, that you could also use.

I've fixed function that was created by @RuiRomano for SQL 2008 to make it work with 2016 and 2017

Also note the wmi call would need to be versioned as I was working with PowerBi Server and SQL 2017 so its version v14 however I did test with SQl 2016 using v13 and that works fine as well.
As I was testing locally I needed to create selfsign so that part can be dropped, however I've kept it in so you can see the end to end how it works.

Get FQDN

$dnsname = ([System.Net.Dns]::GetHostByName((hostname)).HostName)
#Create SSL Certificate (replace with PKI function)
New-SelfSignedCertificate -CertStoreLocation Cert:\LocalMachine\My -DnsName $dnsname -FriendlyName PBIRS

Get the cert Thumbprint

$certHash = (Get-ChildItem -Path cert:\LocalMachine\My -DnsName $dnsname).Thumbprint

Make cert trusted

$cert = (Get-ChildItem -Path Cert:\LocalMachine\My\$certHash)
Export-Certificate -Cert $cert -FilePath c:\cert.cer
$file = ( Get-ChildItem -Path C:\cert.cer )
$file | Import-Certificate -CertStoreLocation Cert:\LocalMachine\Root

Function

function Set-SSRSSystemConfiguration($sslUrl, $certHash, $sslPort, $ReportServerInstance){
		# https://ruiromanoblog.wordpress.com/2010/05/08/configure-reporting-services-ssl-binding-with-wmi-powershell/
		# The .ToLower() avoids the error “A Secure Sockets Layer (SSL) certificate is not configured on the Web site.”
		$certHash = $certHash.ToLower()
		Write-Output “Configure SSRS SSL binding”
		
		$serverClass = get-wmiobject -namespace "root\Microsoft\SqlServer\ReportServer\RS_$ReportServerInstance\v14\Admin" -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(“ReportServerWebApp”, $sslUrl, $lcid)
		if (!($result.HRESULT -eq 0)) { write-error $result.Error }
		$result = $serverClass.ReserveURL(“ReportServerWebApp”, $sslUrl, $lcid)
		if (!($result.HRESULT -eq 0)) { write-error $result.Error }
		$result = $serverClass.RemoveSSLCertificateBindings(“ReportServerWebApp”, $certHash, “0.0.0.0”, $sslPort, $lcid)
		if (!($result.HRESULT -eq 0)) { write-error $result.Error }
		$result = $serverClass.CreateSSLCertificateBinding(“ReportServerWebApp”, $certHash, “0.0.0.0”, $sslPort, $lcid)
		if (!($result.HRESULT -eq 0)) { write-error $result.Error }
		} #close funciton

Call function to configure the SSL binding

Set-SSRSSystemConfiguration "https://$($dnsname):443" $certHash 443 PBIRS

[ronott]

Hey Guys,

Thanks @HealisticEngineer for the TLS function!

For SSRS 2019 I had to add the SetSecureConnectionLevel method, appart from that, works like a charm.

function Set-SSRSSystemConfiguration($sslUrl, $certHash, $sslPort, $ReportServerInstance){
		# https://ruiromanoblog.wordpress.com/2010/05/08/configure-reporting-services-ssl-binding-with-wmi-powershell/
		# The .ToLower() avoids the error “A Secure Sockets Layer (SSL) certificate is not configured on the Web site.”
		$certHash = $certHash.ToLower()
		Write-Output “Configure SSRS SSL binding”

		...

		$result = $serverClass.SetSecureConnectionLevel("1")
		if (!($result.HRESULT -eq 0)) { write-error $result.Error }

		} #close funciton

+1 for adding SSL Support