Configuring SQL Reporting Services to use ADFS Authentication

Special thanks to Rahul Shelar and Sachin Mundra from the ADFS and SQL teams for working with me on this latest issue.  Without their help, I would still be banging my head against the wall.  I learned quite a bit about SSRS along the way and also learned what the stupid checkbox in IIS really meant.

This document assumes SSRS is installed and working properly using Windows Integrated Authentication. SQL Reporting Services is installed on a R2 member server named SRS.Treyresearch.net and the SQL 2005 database server is located on another R2 member server named SQL.Treyersearch.net

The following is a screenshot of IIS Manager after initial installation and configuration of SSRS.

The App Pool Identity can be either Network Service (default) or a domain service account

Steps to configure the site for ADFS

1.  Enter the FQDN and path to the federationserverservice.asmx page of the FS-R on the web sites node of IIS.

2.  Enable the ADFS agent on the default web site

3. Add the token based application to ADFS.MSC on the resource federation server

4.  On the Reports Virtual Directory, add the ADFS ISAPI extension (c:\windows\system32\ifsext.dll)

a. Properties of Reports Virtual Directory in IIS Manager

b. Configuration button on the Virtual Directory tab

c. Type in or browse to c:\windows\system32\ifsext.dll

d. deselect the checkbox labeled “Verify file exists”

e. Choose Insert 

5. Verify the Directory Security Permissions are set to anonymous on the Reports Directory

6. Repeat steps 4 and 5 on the ReportServer Virtual Directory. When adding IFSEXT.DLL to the Application Wildcard Maps area – you must choose “Move Up” to ensure it is at the top of the list. The 2.0 Framework ISAPI is present on the ReportServer virtual directory, but not on Reports.

This means that the Reports directory will only have IFSEXT.DLL specified, but the ReportServer directory will have IFSEXT.DLL specified first, then ASPNET_ISAPI.DLL listed second. Again – it is crucial that the “Verify File Exists” checkbox is NOT selected.

7.  Modify the RSWebApplication.config file located at c:\program files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportManager and modify/add the following lines

<ReportServerUrl>https://srs.treyresearch.net/ReportServer</ReportServerUrl>

<CustomAuthenticationUI>

<PassThroughCookies>

<PassThroughCookie>_WebSsoAuth</PassThroughCookie>

<PassThroughCookie>_WebSsoAuth0</PassThroughCookie>

</PassThroughCookies>

</CustomAuthenticationUI>

The file should look like this:

More information on the RSWebApplication.config settings can be found here:

Configuring Report Manager to Pass Custom Authentication Cookies 

https://msdn2.microsoft.com/en-us/library/ms345241.aspx

8.  Since the ADFS Token based agent writes an Local impersonation NT Token, you cannot impersonate user credentials to access the SQL database residing on a different machine than SSRS

a. In SSRS Configuration Manager, go to the Database Setup section and specify an account to be used to contact the SQL database.  You many use Windows Credentials or SQL Credentials here

9. Test accessing the ReportServer and Reports with ADFS enabled. You must use the FQDN in the address – i.e. https://srs.treyreserach.net/Reports or https://srs.treyresearch.net/ReportServer

a. https://srs/Reports will no longer work as it did under Windows Integrated Auth. You must use the URL specified on the ADFS Web Agent tab and in ADFS.MSC.