Adding SQL Reporting Services

EmpowerID is able to leverage the robust report design, email subscription and dashboarding capabilities of Microsoft SQL Reporting Services (SSRS). In order to provide a seamless security and authentication experience for end users, SSRS can be configured to leverage EmpowerID as its identity provider and SSO system. This gives users the ability to authenticate to SSRS using any type of identity beyond the stock Windows Authentication, while giving administrators the ability to add additional security features like device registration and 2nd factor authentication.

EmpowerID ships with a pre-configured SAML service provider connection for SSRS that can be edited with the specific information for your SSRS server. A SAML attribute configured for this connection will pass a list of the Person’s Management Roles to SSRS for its use in security much the same as AD group membership. EmpowerID comes pre-configured with four Management Roles for use in assigning security within SSRS. These Management Roles include: SSRSAdministrator, SSRSSecurityAdministrator, SSRSDeveloper, and SSRSViewer. When a user logs into SSRS through EmpowerID’s SSO facilities, a list of Management Roles will be passed as a SAML attribute statement to SSRS. SSRS checks if the user is a member of any of the four default SSRS Management Roles and if so, sets these for the user’s session. These roles can then be used to grant appropriate permissions within SSRS’s native user interface to reports and folders much the same as AD groups would be used if SSRS was configured for Windows Authentication.

Because Reporting Services is designed to work with Windows Authentication by default, the Reporting Services security system must be extended for SAML authentication before Single Sign-On between EmpowerID and Reporting Services can work. As such, a custom assembly with the appropriate libraries, as well as custom Login, ACS and SignOut pages must be installed on the Report Server, and the Reporting Services configuration files must be modified. To make this easy, EmpowerID provides an installer, the Reporting Services Authentication Extension installer. When this installer runs, it does the following:

  • It places the EmpowerID.ReportingServices.Security.dll in the bin folders of the Reporting Services ReportManager and ReportServer folders. This assembly contains the libraries for extending Reporting Services.
  • It adds Login.aspx, acs.aspx and SignOut.aspx pages to the Reporting Services ReportManager and ReportServer folders.
  • It adds the TheDotNetFactory key with the appropriate sub-keys to the registry.
  • It creates a backup of the ReportManager and ReportServer configuration files, placing those files in a new folder, the ConfigBackup folder. In this way, you can easily revert your configuration files back to their original state if needed.

This topic describes how to create a SAML application for Reporting Services and is divided into the following activities:

To add your Report Server settings to the Identity Warehouse

  1. Open the EmpowerID Configurator and click the Services tab.
  2. From the Services tab, do the following:
    1. Type the URL to your Report Server in the Report Server URL field.
    2. Type the of the report server folder in the Report Server Folder field.
    3. Type SSRS in the SAML Connection field.
    4. Click Save and then click OK to close the Settings have been saved!message box.

To edit the Reporting Services SAML Application

  1. From the Navigation Sidebar of the EmpowerID Web interface, navigate to SAML Single Sign On page by expanding Admin > SSO Connections and clicking SAML.
  2. From the SAML Connections tab of SAML SSO Manager, search for SQL Reporting Services.
  3. From the SAML Connections grid, click the drop-down arrow for the SQL Reporting Services record and click Edit.
  4. From the Service Provider Details section the General tab of the Edit SQL Reporting Services form that appears, locate the Assertion Consumer URL field and replace the IP Address of the report server with that in your environment.
  5. Scroll to the Certificates section and select the certificate used in your environment for signing SAML assertions from the Signing Certificate drop-down.
  6. Leave all other fields set to their default values and click Save.

To add the certificate to the Personal Certificate Store

  1. From MMC on the Reporting Services server, add the Certificates snap-in for the local computer.
  2. Expand the Certificates node, right-click Personal, point to All Tasks and click Import.
  3. In the Certificate Import Wizard that appears, click Next.
  4. Click Browse and locate the certificate you are using for the SSO application.
  5. In the Open window that appears, select your certificate and click Open.
  6. Back in the Certificate Import Wizard, click Next.
  7. Enter the password for the private key and click Next.
  8. Click Next again.
  9. Click Finish.

To install the EmpowerID Reporting Services Authentication Extension

  1. From your Reporting Services server, locate and open the Reporting Services Authentication Extension you received from EmpowerID.
  2. In the wizard that appears, click Next to begin.
  3. Accept the terms of the license and click Next.
  4. Select the destination folder for the application and click Next. If Reporting Services is installed in the default location, the installer locates the appropriate folder for the version of Reporting Services (2008R2 or 2012) on your machine. If you installed Reporting Services in a alternate location, you will need to specify that location by clicking the Change location and pointing the installer to the correct path.
  5. Type the following information in the appropriate wizard fields and click Next to continue.
    • EmpowerID Web Server - This is the FQDN or alias of the EmpowerID Web role server in your environment.
    • EmpowerID SAML SSO Connection Name - Type SSRS in this field. This is the name of the Reporting Services SSO Connection in EmpowerID.
    • SAML Verification Certificate Thumbprint - This is the thumbprint of the certificate used for signing the SAML assertion delivered to SSRS in your environment.
  6. Click Finish to close the installation wizard.
  7. If desired, you can verify that the settings have been written to the registry by opening registry editor and navigating to "HKEY_LOCAL_MACHINE\SOFTWARE\TheDotNetFactory\EmpowerID\SSRS."

To test the Reporting Services SSO Extension

  1. From your Reporting Services server, restart the SQL Server Reporting Services (MSSQLSERVER) Windows service.
  2. Open and navigate your Web browser to the Report Manager URL for your environment. Your browser should be redirected to the EmpowerID Login page.
  3. From the EmpowerID Login page, submit the credentials of an EmpowerID Person delegated the SSRSViewer Management Role.
  4. Your browser should be redirected to the Reporting Services home page.