Reporting Services – SSL Certs

Objective

Our task is to secure Web\HTTP traffic.

Process

Request Certificate

We will not touch on the various pathways to request a certificate and leave that for another post.

 

Receive Certificate

Certificates are precious and so maintain custody during request and receipt.

Secure File

Here we receive it through secured email…

BTW, the file type in this case is PFX.

 

Register Certificate

Microsoft Management Console ( MMC )

Prepare MMC for Certificate.msc

Initiate mmc.exe

Initiate MMC by running mmc.exe

Add/Remove snap-in…

Use menu items “File” “Add/Remove Snap-in …“.

From the “Available snap-ins“, please choose “Certificates“.

 

Add/Remove snap-ins – Selected snap-ins ( Certificate )

Here is what the screen looks like upon clicking the Add> button and adding the “Certificates” snapin.

 

Certificate Snap-in

Here we choose the “Computer account” as the targeted account.

 

Using MMC – Import Certificate

Computer – Personal Store
Menu

Access the “Console Root” “Certificates (Local Computer)” Personal Certificates node.

Right click the Selected Node and from the drop down menu, choose “All Tasks” “Import…”

 

Certificate Import Wizard – Welcome to the Certificate Import Wizard

The “Certificate Import Wizard” window opens up…

Explanation
  1. Store Location
    • Local Machine
Certificate Import Wizard – File To Import

The “File to Import” window appears

Please click the Browse button…

 

Certificate Import Wizard – File To Import – Open Dialog – File Type – X.509 Certificate

The “File Open” window opens.

The default file type is “X.509 Certificate (*.cer, *.crt)

 

Certificate Import Wizard – File To Import – Open Dialog – File Type – Personal Information Exchange ( PFX )

In our case, we have a PFX file.

And, so we chose “Personal Information Exchange (*.pfx)

Certificate Import Wizard – File To Import – Specify File To Import

Confirm the filename.

And, click the Next button to complete the Certificate Import.

 

Review Certificates in Personal Store
List Certificates

Access “Console Root” \ “Certificates ( Local Computer )” \ Personal \ Certificates and review the certificates.

 

Review Certificate

Please review the imported certificate, the important areas:

  1. Issued By
    • This is the certificate issuer
  2. Expiration Date
    • The certificate’s Expiration Date
  3. Intended Purpose
    • The certificate intended purpose
      • Want to be sure that the following are included
        • Server
Review Certificate – In Depth

Select the certificate and doubleclick on it.

Outline

Please review the various tabs:

  1. General
  2. Details
  3. Certificate Path
General

Details

Certificate Path

 

Reporting Services Configuration Manager

Launch SQL Server Configuration Manager

From Windows desktop, perform a Search for SQL Server applications.

To do so initiate Search, and enter “sql server

 

SQL Server Configuration Manager Connect

The first step is to choose the server and Reporting Server Instance to connect to…

 

Reporting Services Configuration Manager – Web Service URL

Original

Advanced Multiple Web Site Configuration

Access the “Advanced Multiple Web Site Configuration“, by clicking the “Advanced” button.

Advanced Multiple Web Site Configuration – Before

 

Add a Report Server SSL Binding
Add a Report Server SSL Binding – Initial

Add a Report Server SSL Binding – List of Certificates – Initial

Restart Report Server

If the certificate is not shown in the list of certificates, please restart the Report Server as detailed here:

Configure SSL Connections on a Native Mode Report Server
Link

Expand the list of SSL Certificates. Reporting Services detects server authentication certificates in the local store. If you installed a certificate and you do not see it in the list, you might need to restart the service. You can use the Stop and Start buttons on the Report Server Status page in the Reporting Services Configuration tool to restart the service.

 

Add a Report Server SSL Binding – List of Certificates – After

Please exit the “Edit a Report Server SSL Binding” window, and come back and choose the target certificate.

We chose “(All IPv4)” and click OK.

And, repeat for “(All IPv6)“.

 

Reporting Services Configuration Manager – Report Manager URL

Once the Web Services configuration is done, please choose “Report Manager URL”.

Original

 

Process

Click the “Advanced” button and follow identical steps to the ones we took for “Web Services”.

 

Revised

 

Validate

Outline

  1. Launch Web Browser
  2. Enter URL, please be sure to use https and not http
  3. Access the Page’s property
  4. Review the Certificate

 

Steps

Launch Web Browser – Using https

Launch a browser and enter the URL.

In our case we entered https://RS.labdom.org/Reports

 

Web Page Property

On Internet Explorer (IE), right click on an empty spot on the page and choose Properties from the drop down menu.

 

 

Review Certificate

 

 

References

  1. William R. Vaughn and Peter Blackburn
    • Installing and Configuring SQL Server Reporting Services
      Link
  2. Microsoft Docs
    • Docs > SQL > SQL Server Reporting Services > Report server web service > Net framework
      • Using Secure Web Service Methods
        Link
    • Docs > SQL> SQL Server > Reporting Services > Security
      • Configure SSL Connections on a Native Mode Report Server
        Link
    • Docs > SQL > SQL Server > Reporting Services > Report server
      • RsReportServer.config Configuration File
        Link
  3. Microsoft | Developer
    • Team Foundation Server – Setup, Administration and Operations Blog
      • TF255455: SQL Server Reporting Services is configured to require a secure connection.
        However, no HTTPS URL is configured with a valid certificate
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

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