Share via

SSL configuration and Reporting Services

  • Article

When installing Reporting Services 2005 with Secured Socket Layer (SSL), it is a common mistake not to install the CA certificate in the trusted root for the local computer.

This will lead to the following error trying to access to access the Report Manager you receive the following error message:

“The underlying connection was closed: could not establish Trust relationship for the SSL/TLS secure channel"

You can verify that the CA certificate is installed in the “Console Root\Certificates (Local Computer)\Trusted Root” following the steps bellow :

1. Go to start -> run -> type in "mmc" and hit "OK"

2. This will launch Microsoft management Console

3. go to File -> Add/remove snap-in

4. In Add/remove snap-in window, under "Standalone" tab, Click on "Add button"

5. This will launch Add Standalone Snap-in window

6. Choose "Certificates" from available snap-ins and click on Add button

7. This will launch "Certificates snap-in" with three check boxes

- My user Account
- Service Account
- Computer Account

8. Choose " Computer Account" and click on finish (to install the certificate for the machine not for a single user)

9. Expand Console Root\Certificates (Local Computer)\Trusted Root Certification Authority\Certificates

10. Right Click on “Certificates” and select Import / All Tasks / Import...) choose the certificate (.cert) and follow the wizard until the end

11. You will need to restart the IIS before testing the Report Manager (run iisreset from the console)

Note: When connecting to Reporting Services from the client machines, if you want to avoid the message asking you if you trust the server you would have to install the CA certificate in the Trusted root of the client machines. If you install it choosing the “local machine” it will be available for all users that log onto the machine. If you use “User account” it will be available only for that user.

An easy way to remove the message for each user could be to put the certificate file from the CA on a web page and ask the users to connect to it and installing it.

It is a good idea as well to make sure that the certificate has been configured correctly:

1. Go to IIS and the properties of the Default Web Site. In the Directory Security tab, under "Secure communication" click the "View certificate" button.

2. Make sure there aren't any errors/warnings on neither the General tab nor the Certification Path tab.

3. Get the name from the "Issued to" on the General tab, or the "Subject" property on the Details tab. This will probably be the fully-qualified domain name (FQDN).

4. Exit out of IIS Manager.

5. You'll need to make two changes in the config files. Enter the name from step 3 above in:

- UrlRoot entry in the RSReportServer.config

- ReportServerUrl entry in the RSWebApplication.config

 You can follow the indications bellow:

Taken from the link:

Using Secure Sockets Layer (SSL) for SQL Server 2000 Reporting Services

https://msdn2.microsoft.com/en-us/library/aa902687(SQL.80).aspx

Reporting Services Configuration Files

If you used anything other than the NetBIOS name in the common name for the Web Server Certificate, then you'll need to make several changes to some configuration files to reflect the non-NetBIOS name in the URLs. Also, if you've used the NetBIOS name, you might find it helpful to change the embedded https:// URLs to your server over to https:// URLs. The following paragraphs detail these changes.

· RSReportServer.config

In RSReportServer.config, which you will find in \Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer, you'll need to locate the <URLRoot> element and update the NetBIOS name to the DNS name, and also the https:// to https:// . Take care to use the same case for the letters as you used in the certificate:

So, in our example here we needed to edit the <URLRoot> to be:

<UrlRoot>https://d1.internal.boost.net/ReportServer</UrlRoot>

· RSWebApplication.config

You'll find RSWebApplication.config in C:\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportManager. The change needed here is to update <ReportServerUrl> to include the name used in the certificate.

<ReportServerUrl>https://d1.internal.boost.net/ReportServer</ReportServerUrl>

Finally, the following line in RSReportServer.config governs the master control over SSL:

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

Acceptable values are:

· 3 Most secure—Use SSL for absolutely everything.

· 2 Secure—Use SSL for rendering and methods that pass credentials but don't insist on it for all SOAP calls.

· 1 Basic Security—Accepts http but rejects any calls that might be involved in the passing of credentials.

· 0 Least Secure—Don't use SSL at all.

2 is the value that the installation wizard will input if you install with the Use SSL check box selected, but we prefer to use 3 and ensure that the Report Manager is also using SSL.

Additionally you can try to open another page in the default web site using ssl and see if It was working fine. This would confirm that the certificate is installed correctly.

And to check the information in the RSWebApplication.config configuration file: if you specify ReportServerURL, you must delete the value of the entry for ReportServerVirtualDirectory (not the tags just the value if it exists)

Taken from the link:

RSWebApplication Configuration File

https://msdn2.microsoft.com/en-us/library/ms155878.aspx

ReportServerUrl Provides an alternative way to specify the SOAP endpoint of the report server that is used by Report Manager. You can use this setting instead of ReportServerVirtualDirectory when Report Manager and the report server virtual directories are hosted on separate servers or Web sites, or if you need to specify a fully qualified domain name for the report server. ReportServerURL and ReportServerVirtualDirectory are mutually exclusive. If you specify ReportServerURL, you must delete the entry for ReportServerVirtualDirectory. To specify this setting, you must edit the RSWebApplication.config file manually. Valid values include <https://<server>/<reportserver>> or <https://<server>/<reportserver>> if you are using certificates. If Report Manager is installed separately on an Internet-facing Web server, this value must be a fully-qualified domain name that can be used to access a report server instance behind the firewall. This value can never be set to localhost.

If a Secure Sockets Layer (SSL) certificate is installed, this value must be the name of the server that is registered for that certificate. If you get the error "The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel", set ReportServerUrl to the fully-qualified domain name of the server for which the certificate was issued. For example, if the certificate is registered to <https://adventure-works.com.onlinesales>, the report server URL would be <https://adventure-works.com.onlinesales/reportserver>.

 

Maria Esteban

Ingeniero de Soporte de Reporting Services

Comments