Using test certificate with Reporting Services 2005 to establish SSL connection

  • Article

If you are planning to publish your Reporting Services site to the internet then you need to secure the communication between the server and the clients. The industry standard to do so is to buy an SSL certificate from a public certificate authority (CA) like VeriSign. This would cost you much money especially that you have to pay for each physical server.

The other solution that would be appropriate if you are publishing your reports to your company's employees is to use a certificate issued by you or a "testing certificate".

To do so you need to do the following steps

  • 1. Install Certificate Authority (CA) on this computer

By installing Certificate Authority Service (CA), you would behave like VeriSign so you can issue certificates to other if they requested a certificate from your server. To install Certificate Authority on Windows 2003, follow these steps

Go to Control Panelà Add/ Remove programs à Add/ Remove windows components

Check on Certificate Services and follow the steps or the wizard as following

  • a) Choose Stand-alone root CA because it's necessary for Reporting Services to trust the certificates issued from this CA.
  • b) Write the Common Name (CN) of the new CA.
  • c) The wizard will restart IIS and it's better to reboot the machine after installing the new CA
  • 2. Request a certificate using IIS MMC from this CA

From IIS, right click on the web site that has Reporting Services installed on it and choose properties. Open the "Directory Security" tab and press "Service Certificates" button.

Choose "Create new Certificate" then Next

From the second screen in the wizard choose "Prepare the request now but send it later" then click Next

In the Name and security Settings page, write a name of your certificate.

In the Organization Information page, write your company name and the department.

In the "Your Site's Common Name" page, write the common name of your site. Take care of this step because it's very important this name should be the name of your site. i.e. if the site full name is Extranet.MyCompany.com so the common name should be Extranet.MyCompany.com

At the end of this wizard, it will save the request in text file.

 

 

  • 3. Issue the certificate

Now, open the Certificate Authority (CA) console (Administrative tools à Certification Authority.

Right click on the CA name in the console and choose "Submit new request"

Browse to the certificate request file that you created using IIS wizard and choose it.

You will find it under bending Requests folder, right click on it and choose issue. You will find it under the Issued certificates folder.

Right click on the certificate and choose "Export Binary Data". Choose cer extension to the file and save it.

  • 4. Install Certificate on IIS

Now Open again IIS management console and from "Directory Security" tab of the web site properties, choose "Server Certificate". In the wizard choose "Process the bending request and install certificate".

Choose the file that you saved using Certificate Authority and complete the wizard.

Now you have a certificate ready for you web site, if you want to restrict access to SSL connections only for the whole site or any particular web application, do the following.

            From the web site/application properties choose directory security tab then in the "Secure Communication" box, choose Edit. Check require secure channel (SSL).

  • 5. Set the Reporting Services to use this Certificate

Finally, you need to configure Reporting Services to work over SSL.

Open Reporting Services Configuration from the SQL Server 2005 group.

Choose Report Server Virtual Directory section.

Mark the checkbox "Require Secure Socket Layer Connections (SSL)"

In the "Require For" drop down, choose the appropriate method for your usage the description of them is

1 - Connections only

2 - Report data

3 - Entire Web service API

The levels are cumulative. Level 3 is the most secureand 1 is the least secure one. Form more information about them please refer to https://msdn2.microsoft.com/en-us/library/ms154709.aspx

In the certificate name text box, add the certificate common name as you wrote in step 2 so it should be in our example Extranet.MyCompany.com

Now restart the machine then begin your encrypted browsing J

  • 6. Side issues.

I've faced some side issues during the implementation of SSL for Reporting Services 2005. Like the following

You need to reboot the server after setting the certificate

  • The common error message in SSL with reporting services is "The underlying connection was closed: Could not establish secure channel for SSL/TLS" This error message means that the reporting web application code doesn't trust the certificate of the Reporting Services web service.
    • Note:

When you browse the reporting web application, you actually are calling the XML Web Service of Reporting Service because the web application is calling it.

That's why it's required to install the certificate as trusted certificate in all your servers if you have server farm.

 

  • When you want to deploy your reports from your development machine to the server, the deployment fails with the error "The underlying connection was closed: Could not establish secure channel for SSL/TLS"

You need also to install the certificate as trusted certificate in the development machine, the easiest way to do so is the following:

  1. Browse to the reporting site using IE
  2. Click on view certificate button in the warning window in case of IE6 or the red area at the top in case of IE 7 (see image below)
  3. In the view certificate window, click on Install certificate
  4. In the certificate store screen, choose "Place all certificate in the following store"
  5. Click on browse and choose Trusted root certificate authorities. You will get a warning that Microsoft can't trust this certificate, choose yes to install it.
  6. You now trust the certificate but you need also to trust the certificate authority which issued this certificate.
  7. Browse to the same site again (you may need to open another instance of IE) and click on view certificate then choose certificate path tab. This will show you the issuer of the certificate. Click on the issuer and choose view certificate (at the bottom) and install the issuer in the Trusted root certificate authorities

 

 

  • You get a warning when you browse to the Reporting site from Internet Explorer.

You need to install the certificate as discussed earlier.