Sunday, June 6, 2010

Problem with SSL in SQL Reporting Services

When you do a fresh install of the SQL Server, you get an option to install and configure or only install Reporting Services along with the Database engine. If you choose the install and configure option, the Report Server and Report Manager websites will be created and configured for you by the wizard itself (along with a bunch of other stuff like the databases used by Reporting Services). However, it seems the wizard configures the websites to use SSL and when you try to browse the websites using the default URLs – http://localhost/reports and http://localhost/reportserver, you get this error:

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel

Digging deeper, if we look at the RSReportServer.config file which we can find in <installation directory>:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer (this is the location for SQL 2008 R2, for SQL 2008 the folder name should be MSRS10.MSSQLSERVER) , we can find the configuration setting which causes this. Within the <configuration> node, there should be an element as below,

<Add Key="SecureConnectionLevel" Value="2"/>

The value of the SecureConnectionLevel setting can vary from 0-3, with a higher value indicating a greater level of security. In order to avoid using SSL, we need to change the value to 0. After changing this value and saving the config file, if we try hitting the Report manager or Report server site, it should start working. One surprising thing that I found was that there was no way (at least I couldn’t find one) to change this setting from the Reporting Services Configuration Manager. Even though we can add/remove the certificates which will be used by SSL, even if we delete all certificates from the configuration manager, it still doesn’t stop using SSL. But hopefully this post will help you to change it from the config file and get rid of the error.

No comments:

Post a Comment