Configuration Manager 2012 R2 Reporting Services and SSL Trusting and Binding Issues

Hi everyone, i was configuring Reporting Services for Configuration Manager 2012 R2 that day, and i got two annoying issues when it comes to SSL binding.

I want to share with you the two issues and how I solved them.

Duplicate SSL binding, or Unknown appearing in the https URL

So you have a certificate in the personal store in the reporting server with name SRV-1.contoso.com, you already configured a certificate binding via the (Web Service URL) in the Reporting Services Configuration Manager. Now, you want to change the URL name, and thus the certificate, so you went to the certificate store and deleted the certificate for SRV-1.contoso.com before you use the Reporting Services Configuration Manager to unbind it. Now every time you try to add another certificate, the Reporting Services Configuration Manager keep showing the old name that exists in the old certificate, or worse, display an Unknown URL like https://unknown.contoso.com/reports... .

Also the following event appears in the event viewer.

Event ID 110, Source: “Report Server Windows Service”, Details: “The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead”.

Configuration Manager 2012 R2 Reporting Services and SSL 1

To investigate more, i went to the configuration file on the reporting server located here: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config.  On the file, you can find all binding including the one for the old name SRV1-contoso.com.

<Application>
<Name>ReportManager</Name>
<VirtualDirectory>Reports</VirtualDirectory>
<URLs>
<URL>
<UrlString>http://+:80</UrlString>
<AccountSid>S-1-5-21-184627253-622988433-926223558-1099164</AccountSid>
<AccountName>contoso\svc_Report</AccountName>
</URL>
<URL>
<UrlString>https://SRV-1.contoso.com:443</UrlString>
<AccountSid>S-1-5-21-184627253-622988433-926223558-1099164</AccountSid>
<AccountName>contoso\svc_Report</AccountName>
</URL>
<URL>
<UrlString>https://SRV-2.contoso.com:443:443</UrlString>
<AccountSid>S-1-5-21-184627253-622988433-926223558-1099164</AccountSid>
<AccountName>contoso\svc_Report</AccountName>
</URL>
</URLs>
</Application>

Now deleting the part containing SRV-1.contoso.com solves the issue. Here is the part that i deleted:

<URL>
<UrlString>https://SRV-1.contoso.com:443</UrlString>
<AccountSid>S-1-5-21-184627253-622988433-926223558-1099164</AccountSid>
<AccountName>contoso\svc_Report</AccountName>
</URL>

When you attempt to connect to the data source in Report Builder 3.0 with CM 2012, you receive the following error message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.)

So you are running Configuration Manager (20120 r2 in my case), and you have a reporting service (in another machine in my case), and you want to build a report, so you have your SQL report builder (Version 3 in my case), and when you try to do certain actions, you will get ugly errors about certificate chain not trusted.

Configuration Manager 2012 R2 Reporting Services and SSL 2

Reason is simple: SCCM SQL server is using self signed certificate for this operation. You have either to trust this certificate on computers you are running the SQL report builder from, or issue a trusted Web Server certificate and assign it to the SCCM SQL server.

The best way to find out where this self signed certificate is located, is by going to your SCCM SQL server, open the SQL Server Configuration Manager> SQL Server Network Configuration>Protocols for… > Right click properties> Certificate tab and then click view.

Configuration Manager 2012 R2 Reporting Services and SSL 3

Simpliest way is to export this certificate and import it to the trusted root certifications store on the computer on which you will be running the SQL Report Builder

Tip: a good reference that I recently read after posting this blog post can be found here. Check it out.

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