SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Configure SSRS with an SSL Certificate

By Thomas Liddle,

SQL Server Reporting Services is becoming increasing popular to produce reports with data from SQL Server or Analysis Services.  Keeping that data secure in an internal or external environment is a must.  In this article, we will walk though how to configure SQL Server Reporting Services 2016 and enable SSL with a self-signing certificate.

Prerequisites

You will need the following to configure SSL on SQL Server Reporting Services

  • SQL Server Reporting Services Native Mode and SQL Server Database Engine 2016 installed
  • Trusted or Self-Signing Certificate (Instructions for Self-Signing Certificates are below)

Generate a Self-Signing Certificate with Powershell

To setup SSL for SQL Server Reporting Services 2016 we will need to generate a certificate.  If you already have a certificate from a trusted authority, you can skip this step.  For our demostrations, we will gernerate a self-signing certificate and import it into our Trusted Root.

  • Open Powershell as Administrator
  • Run the following commands
New-SelfSignedCertificate -CertStoreLocation cert:\LocalMachine\my -dnsname SRVRMSSQL10
$pwd=ConvertTo-SecureString "password1" -asplainText -force
$file="C:\temp\srvrmssql10.pfx"
Export-PFXCertificate -cert cert:\LocalMachine\My\<Thumbprint produced during first command> -file $file -Password $pwd
Import-PfxCertificate -FilePath $file cert:\LocalMachine\root -Password $pwd

Configure Web Service URL

  1. Open Reporting Services Configuration Manager
  2. Click Connect at the “Reporting Services Configuration Connection” Screen

  3. Click on Web Site URL in the left-hand pane window
  4. In the “HTTPS Certificate” drop-down box select the certificate you installed.  You will see the Port text box pre-fill and the Report Server Web Services URL pre-fill. 
  5. Click Apply

Configuring a Database for SQL Server Reporting Services

  1. Click on Database in the left-hand pane window
  2. Click Change Database
  3. In the “Report Server Database Configuration Wizard” click Create a New Report Server Database
  4. Click Next

  5. Type in the Server Name and Authenticate Type for the connection of SQL Server Reporting Services to the database. You have two options “SQL Server Account” or “Current User”. This step is NOT the service account that will be running the SSRS services. It is only used to create the database for SSRS.
  6. Click Next
  7. In the Database Screen, type in the name of your database and click Next

  8. In the Credentials Screen, you will choose the service credentials used by SSRS to connect to the database. You have 3 options, Windows, SQL, or Service Credentials. For demonstrations purposes, we will choose SQL Credentials and click Next.

  9. Click Next at the Summary Page
  10. Click Finish when it is completed

Configure the Web Portal URL

  1. Click the Web Portal URL, in the Reporting Services Configuration Manager
  2. The Virtual Directory is already pre-filled. Click Apply.

Testing your New Secure URL

  1. In the Web Portal URL Screen, click on the link with the :433 port in it.  This will open a web browser to your secure SSRS installation.

    Secure SSRS Web Site

 
Total article views: 1528 | Views in the last 30 days: 39
 
Related Articles
FORUM

SQL Server 2008 Reporting Services

SSRS 2008 Report Service on SQL Server 20005 Database

BLOG

SQL SERVER REPORTING SERVICE MIGRATION

Last week as part of job I had to migrate the Databases and Reporting Service of SQL server 2005 To....

FORUM

Error configuring Report Services DataBase Name

Report Services Configuration Manager

BLOG

SQL Server Reporting Services R2 - Publishing Report Parts

SQL Server Reporting Services R2 – Publishing Report Parts SQL Server Reporting Services R2 (SSRS...

FORUM

SQL Server Reporting Services

Reporting Services Connection Problem

Tags
 
Contribute