sql server reporting services https

Configure SSRS with an SSL Certificate

Thomas Liddle , 2017-03-02

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

Configure Web Service URL

• Open Reporting Services Configuration Manager

• Click on Web Site URL in the left-hand pane window
• 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. 

Configuring a Database for SQL Server Reporting Services

• Click on Database in the left-hand pane window
• Click Change Database
• In the “Report Server Database Configuration Wizard” click Create a New Report Server Database

• 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.

• Click Next at the Summary Page

Configure the Web Portal URL

• Click the Web Portal URL , in the Reporting Services Configuration Manager

Testing your New Secure URL

Secure SSRS Web Site

Log in or register to rate

You rated this post out of 5. Change rating

• Reporting Services (SSRS)

Join the discussion and add your comment

Related content

Extending sql server reporting services with sql clr table-valued functions.

• by Additional Articles
• TechNet Blogs

This article describes how to take advantage of SQL CLR table-valued functions to combine different types of data sources to create rich and exciting SQL Server Reporting Services reports.

2,625 reads

Developing Report Navigation and Drilldown

This article presents an excerpt from the book, Microsoft SQL Server 2005 Reporting Services for Dummies, by Mark Robinson. Learn how to produce interesting navigation and drill down reporting using the basic tools provided within SQL Server 2005 Reporting Services.

2,216 reads

Building Custom Reporting Services Reports for SQL Server Management Studio

• Database Journal

Learn how to build Custom Reports, without installing Reporting Services, using a new feature found in Microsoft Service Pack 2 (SP2) for SQL Server 2005.

4,068 reads

Using Report Parameters in SQL Server Reporting Services

• SQL Server Performance

Report parameters assist in narrowing down a report for better analysis.

2,614 reads

How to setup a SSL certificate with SQL Reporting Services ( HTTPS for SSRS )

This article applies to the following products:

• Messageware Exchange Protocol Guard 2019
• Messageware Exchange Protocol Guard  2016
• Messageware Exchange Protocol Guard  2013
• Messageware OWA Guard 2016
• Messageware OWA Guard 2013
• Messageware OWA Guard 2010

EPG Reports rely on SQL Reporting Services that by default are using port 80 for communication with the database. The article presents a way to enable more secure way of communication using encrypted traffic using HTTPS port 443. 

The article has been based on the article: https://support.sectigo.com/IS_KnowledgeDetailPage?Id=kA03l000000vFwq 

If no Certificate Authority is installed in the domain, we will have to generate a Self-Signed to be able to use it to encrypt the traffic

On the SQL server, please perform the steps below to generate a Self-Signed certificate and install it to both Personal Store and Trusted Root Certifications authorities

• The scripts uses a path "c:\Temp" . Please confirm that Temp folder exists on the C drive before proceeding to step 2. 
• New-SelfSignedCertificate -CertStoreLocation cert:\LocalMachine\my -dnsname SQLHOSTNAME -NotAfter (Get-Date).AddMonths(60) (please replace the SQLHOSTNAME with an actual Hostname of your SQL Server)
• $pwd=ConvertTo-SecureString " password " -asplainText -force (please replace the password with a more complicated string)
• $file="C:\temp\SQLcertificate.pfx" (the location where the certificate will be exported)
• Export-PFXCertificate -cert cert:\LocalMachine\My\<Thumbprint produced during first command> -file $file -Password $pwd (Export the certificate to a file)
• Import-PfxCertificate -FilePath $file cert:\LocalMachine\root -Password $pwd (the command imports the generated certificate into the Trusted Root Certification Authorities store)

• On the SQL Server open the Report Server Configuration Manager
• Switch to Web Service URL
• Click “Advanced” and click Add under HTTPS on the bottom of the window
• Choose the certificate from the list and Click OK
• The Reporting Service are now configured to use both HTTP and HTTPS traffic.
• Optionally - For better security the HTTP protocol can be removed from the list, leaving only HTTPS as active.

(Optional step – removing HTTP port)

• Copy the file generated in the first step – in the above example the file is saved under C:\temp\SQLcertificate.pfx – to the Server where the EPG Web Management is installed
• On the destination Server double click on the file and in the first window choose “Local Machine”
• When asked for a password, provide the password that was set in step 1.1 above
• On the next screen select “Place all certificates in the following store” , click Browse and select “Trusted Root Certification Authorities” from the list and click OK .
• Click Next and Finish the installation

• Open EPG-Reports URL
• Switch to Settings tab
• Replace the Reporting Services URL with the new URL starting with HTTPS://
• Press Save on the top of the page

Support Center

How To Request Certificate for PBRS or SSRS

Originally, I posted back in December 2019 as blog set one How to Setup SSRS to user HTTPS, parts 1 and part 2. Since the original I had a number of questions and clarifications about both posts. So, I thought that I would update the whole set. This first in the set will show you how to request certificate for PBRS or SSRS. To help with the blog, I’m also adding videos to each one within the set. They are always at the end of the blog.

The original blog post was on my list of “things to do” for a while, but it wasn’t until a good friend of mine,  Niall Brady , asked me if I had a guide on how to setup SQL Server Reporting Service (SSRS) or Power BI Report Server (PBRS) to use certificates e.g. HTTPS. In this one I cover all of the steps you need in order to request your server certificate (website certificate) for use with SSRS / PBRS and then I show you how to setup SSRS to use the Network Service account. In my second post, I show you how to setup SSRS to use the server certificate for HTTPS, how to test the SSRS website and finally how to clean-up the SSRS site if you get an “OLD” folder. Those where the original blogs, what I didn’t cover was how to remove the HTTP from SSRS/ PBRS.

Remember when we are talking about Microsoft Configuration Managers (MCM), many peoples still call it SCCM or ConfigMgr. In all cases this blog will over it not matter what you call ConfigMgr (official acronym) or SCCM .

What type of Certificate do you need to SSRS / PBRS?

This might seem obvious to many people but judging on the number of emails I received on the subject. It is not always obvious. The answer is a Web Certificate. if you read reading this post you likely have tp requestion this from an internal Certificate Authority (CA). If you don’t have a CA see How To Install AD Certificate Services and How To Configure AD Certificate Server .

How To Request Certificate for to be used by SSRS / PBRS

The overall process is as follows:

• Using Certificate plug in for MMC, request the certificate
• Setup SSRS / PBRS to use the server certificate.
• Test the SSRS website.
• Removed Port 80 from SSRS / PBRS

SETUP SSRS TO USE HTTPS PREREQUISITES

In order to put together this step-by-step guide, I created a new virtual machine (VM) memrp2 and then installed SQL Server 2019 (using the SQL 2017 guised and nothing has really changed How to Install SQL Server 2017 ). Next, I installed SSRS itself ( How to Install SQL Server Reporting Services 2017 ) Long ago, I setup a domain certificate authority in my domain, so you should have the same too. I will make it a ConfigMgr reporting point after I setup the SSL/HTTPS. Now we are at our stating point. From here, I will show you how to setup SSRS to use HTTPS.

request the certificate Using Certificate plug-in

Launch MMC.exe, then from the File menu, select Add/Remove Snap-in…

Select Certificates and click Add >

Select Computer Account and click Next .

Keep the current computer and click Finish .

Expand the Certificates (Local Computer) | Personal | Certificates

Right click Certificate and point to All Tasks and select Request New Certificate…

Click Next .

If you setup your CA with web Cert, you should see the Active Directory Enrollment Policy, click Next .

You will see all the available certs within you CA, Find the Web Server template, click the More information is required… link.

On the Subject tab, Change the Subject name Type to Common name , then enter the FQDN of your server in the value text box before clicking Add > . Next in the Alternative name section, Change the type to DNS, in the Value text box again enter the FQDN before clicking Add > . You window should look similar to above, Click OK to continue.

Click Enroll .

Again, Click Finish .

How To Request Certificate for PBRS or SSRS – Video

In my  next blog post , I continue along from this point by showing you how to setup SSRS / PBRS to use the server certificate for HTTPS, test the SSRS website and how to clean-up the SSRS website, If you get an “OLD” folder. Please feel free to touch base with me  @GarthMJ .

• British Pound
• PositiveSSL DV
• PositiveSSL DV Multi-Domain
• PositiveSSL DV Wildcard
• PositiveSSL EV
• PositiveSSL EV Multi-Domain
• PositiveSSL DV Trail
• Compare PositiveSSL Certificates
• Certificate Management
• Secure Email (S/MIME)
• Code Signing
• Website Backup
• PCI Scanning
• Ticketing System
• Knowledge Base
• CSR Decoder
• Certificate Search
• Order Status Checker

PositiveSSL KnowledgeBase

• PositiveSSL DV Trial
• Support Videos
• Sectigo Validation
• Code & Document Signing Certificates
• Domain Validated (DV) Certificates
• eIDAS / QWAC Certificates
• Extended Validation (EV) Certificates
• Organization Validated (OV) Certificates
• S/MIME (email) Certificates
• Domain Control Validation (DCV)
• Advisory Notices
• SSL Validation FAQs
• Back Up My...
• Export My...
• Import My...
• Order An Email Certificate?
• Remove a Certificate...
• Request a Refund
• Sectigo Certificate Manager (SCM)
• Videos: SCM Admin how to...
• Admin Guides
• API Documentation
• InCommon Certificate Services
• SCM Help docs
• Release Notes
• Certificates
• Certificate Signing Request (CSR)
• Certificate Installation
• Accelerator for Web Applications
• Application Server
• Cloud Platform
• Control Panel
• FTP / SFTP file Server
• Load Balancer
• Mail Server
• Remote Desktop Protocol
• Security Appliance / Device
• SW Web Switch
• Web / Firewall Proxy Server
• Certificate FAQ
• Document Signing
• Email (S/MIME)
• SSL Technical FAQs
• .   Code Signing Certificates
• .   Converting a Certificate
• .   Unified Communications Certificates
• .   EV SSL FAQs
• SSL Renewal FAQs
• Sectigo Web Security Platform/ CodeGuard
• Database Backup
• Hosting Providers-cPanel/WHMCS Plugins
• Host Provider Cloud Backup
• Managing your Account
• Video Tutorial
• VPS/Dedicated Backup
• Announcements
• Sectigo Web Security Platform
• Web Accelerate
• Web Monitor
• HackerGuardian / IoT
• HackerGuardian
• User Manual
• PCI Standards
• IoT API Documentation
• IoT Manager Admin Guide
• Sectigo Certification Authority
• Certificate Lifespan
• Partner Support
• Privacy Policy & Warranty Info
• Sectigo AddTrust External CA Root
• Trustlogo / Site Seals
• Notifications
• Sectigo Certification Authority Download
• Root & Intermediate(s)
• SafeNet Authentication Client software
• Partner Documents
• Web Server Guides

How to Configure SQL Server Reporting Services (SSRS) with an SSL Certificate

• Guides and Tutorials
• Setup and How-to Guides
• Secure Microsoft SSRS

Secure Microsoft’s SQL Server Reporting Services with SSL/TLS

Microsoft’s SQL Server Reporting Services is a popular Business Intelligence framework. Through SSRS, administrators can analyze, collate, and synthesize data from a variety of sources into easy to consume web-based reports. It is common for third-party software to rely on the rich underpinnings of SSRS for integration purposes, choosing to make SSRS reports available through its own interface.

Since data is used to make decisions, it is very important to protect privacy and prevent tampering over the network. Imagine a nosy employee intercepting salary data, or a bad actor manipulating a report in-flight in order to present altered data! This is why securing SSRS with SSL/TLS is so important – SSL/TLS provides both encryption and message integrity. Securing SSRS can be confusing at first however, with a couple of “gotchas” along the way.

Configuring SSRS

Adding an ssl/tls certificate.

It is recommended to configure SSRS BEFORE applying an SSL/TLS certificate.

By default, SSRS will create a “virtual service account”. For those familiar with Active Directory, this is somewhat similar to a localized version of a GMSA (Group-managed service account). This special local account doesn’t require you to set a password. In certain situations, it is preferable to use a domain account. In the case of third-party integration, often the account running the software also runs the SSRS instance!

On this screen, simply hit “Apply” to accept the defaults. We will come back in and replace this with an HTTPS connection later, but this will allow us to proceed through the rest of the setup.

Under “Database” we must create a database for our SSRS instance. Somewhat counterintuitively, select “Change Database”.

Select “Create a new report server database”. (Or, if this is an upgrade/migration, choose an existing report server database instead). Usually, SQL Server runs on the server running Reporting Services itself, but it is possible to configure it to point to another server.

If the user you are signed in as has sysadmin level privilege on your local SQL Server instance, simply select “Next”. This establishes a connection to the instance for configuration purposes, but the credentials chosen on this screen are NOT persistent. Don’t worry about SSRS running as your administrative user in perpetuity!

Select “Next” to create your Report Server database. Most of the time, your report server mode will be “Native”. The alternative is a Sharepoint-managed SSRS instance, which is installed as part of a Sharepoint deployment through a different tool.

On this screen, we specify the credentials SSRS will use going forward to connect to SQL Server. The default is to create an account for this purpose with least privilege. In most cases, this is the best choice.

On the summary screen, select “Next”. SSRS will begin its database configuration routine.

The wizard takes three to four minutes. When it completes, select “Finish”.

Select “Web Portal URL” and choose “Apply”.

Email settings are important to configure if you want to be able to email out reports on a schedule.

Execution Account allows you to specify a service principal in whose context certain operations to remote servers will run. Most of the time, we can leave this blank. In the cases it is required, it is usually called out in vendor documentation.

Take a backup of your encryption keys.

You will need to specify a password, which will be used to protect the symmetric key. Be sure to record this passphrase for safekeeping, this will be absolutely required during upgrades of SSRS.

Unless your vendor documentation says otherwise, “Subscription Settings”, “Scale-Out Deployment” and “Power BI Service” can usually be ignored. These constitute certain advanced features of SSRS.

Your SSL/TLS certificate must now be setup in two separate spots, which can be confusing at first. Since SSRS’s configuration tool will let you specify a certificate that is present in the machine store’s personal certificate node, first we must import a certificate to this location. Alternatively, we could create our CSR from the MMC on the SSRS Server (which stores the private key on this machine in the “Certificate Enrollment Requests” node), and subsequently import our signed response from our CA.

n.b. Ensure when creating your CSR to populate the SAN name field in the certificate, even if it is the same as the common name. This is required for browsers to trust your certificate!

Older versions of SSRS were notorious for being unable to work with wildcard certificates. More recent versions do not have this problem.

Under “Web Services URL”, select “Advanced”.

Choose “Add” on the bottom.

Choose a “Certificate”. Available options are sourced by enumerating anything in the machine store’s node. Ensure that you select a certificate with a corresponding private key! Anything in the personal node will appear, but a certificate without a private key is not a valid selection, and will result in cryptic errors.

Note that “URL” is sourced from the common name of the certificate.

Optionally, remove the HTTP Identity, and then select “OK”.

The above error is common in the case of using an internally signed CA. The underlying windows machine MUST trust the certificate chain of the certificate you select in order to create a binding for it. If needed, add relevant root and Intermediate Certificate into the appropriate machine certificate stores!

Once the configuration has been appropriately applied, the “apply” button will become greyed out.

Next, select “Web Portal URL”.

Select “Advanced”.

Once again, select the bottom “Add” button.

Choose your certificate (the same selection as before is fine). Select “OK”.

SSRS is now configured to use SSL/TLS!

Author: Jeremy Schatten Published: 11-10-2021

Next Guide...

Ssl installation guide for apache on redhat linux (rhel).

Red Hat Enterprise Linux (RHEL) is a Linux-based operating system from Red Hat designed for organizations. RHEL can work on desktops, on servers, in hypervisors or in the cloud. Red Hat and its community-supported counterpart, Fedora, are among the most widely used Linux distributions in the world.…

Jasinski Technical Wiki

Contributor links, other wiki sections, updating ssl certificate on ssrs server, overview ¶, procedure ¶, get the csr ¶, provision new ssl certificate ¶, install the certificate in the certificate store ¶, configure ssrs to use the new certificate ¶, troubleshooting ¶.

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam . Except where noted, all contents Copyright © 1999-2023, Patrick Jasinski.