Home > How-to Guides > How to setup a SSL certificate with SQL Reporting Services ( HTTPS for SSRS )

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

 

SUMMARY

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 

STEPS

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

  1. The scripts uses a path "c:\Temp". Please confirm that Temp folder exists on the C drive before proceeding to step 2. 
  2. Open PowerShell as Administrator and type the following commands
    1. 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)
    2. $pwd=ConvertTo-SecureString "password" -asplainText -force (please replace the password with a more complicated string)
    3. $file="C:\temp\SQLcertificate.pfx" (the location where the certificate will be exported)
    4. Export-PFXCertificate -cert cert:\LocalMachine\My\<Thumbprint produced during first command> -file $file -Password $pwd (Export the certificate to a file)
    5. Import-PfxCertificate -FilePath $file cert:\LocalMachine\root -Password $pwd (the command imports the generated certificate into the Trusted Root Certification Authorities store)

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

 

 

(Optional step – removing HTTP port)

 

 

  1. Move the generated certificate file to the machine where the EPG Web Management is installed.
    1. 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
    2. On the destination Server double click on the file and in the first window choose “Local Machine”
    3. When asked for a password, provide the password that was set in step 1.1 above
    4. 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.
    5. Click Next and Finish the installation

  1. Configure EPG-Reports to use the new HTTPS URL
    1. Open EPG-Reports URL
    2. Switch to Settings tab
    3. Replace the Reporting Services URL with the new URL starting with HTTPS://
    4. Press Save on the top of the page

Updated: