Authentication with the Report Server

SQL Server Reporting Services (SSRS) offers several configurable options for authenticating users and client applications against the report server. By default, the report server uses Windows Integrated authentication and assumes trusted relationships where client and network resources are in the same domain or in a trusted domain. Depending on your network topology and the needs of your organization, you can customize the authentication protocol that is used for Windows Integrated authentication, use Basic authentication, or use a custom forms-based authentication extension that you provide. Each of the authentication types can be turned on or off individually. You can enable more than one authentication type if you want the report server to accept requests of multiple types.

All users or applications who request access to report server content or operations must be authenticated before access is allowed.

When Windows Integrated Authentication does not meet the requirements

In one of our projects we had a scenario where Windows Authentication would not help us to meet the project requirements.

The project was using ADFS to authenticate users from different organizations in a single MVC Application, once the users were authenticated, they should access SSRS based on their ADFS Username and Role claims.

Per Microsoft definition SSRS does not support Single Sign On technologies, so what to do? To solve this problem a Custom Authentication was implemented.

Custom Authentication Flow

Before we start going through the technical implementation is important to understand the authentication flow we used for this scenario.

User Registration Process

See below how the User Registration Process works:

  1. User authenticates in the AD FS
  2. AD FS returns Organization, Role and Username claims
  3. The App will look for the Organization in the Organization Table
  4. The app will create the user in the Users table linked to the Organization
  5. The app will get the AD FS roles and look for them in the OrgGroupRole table
  6. For each role found in the OrgGroupRole Table, the app will map them to SSRS Roles based on the OrgGroupRoleSSRS
  7. Once the mapping is done the app will call SSRS web services to set the policies for the user in the Organization`s folder (each organization will have its own folder in SSRS)

Database Model

Let’s look at the Database model we created to map AD FS Claims to SSRS.

This model is used to create users and map the organization roles to real SSRS roles. See the table’s details below:

  • Organization Table – As it was told in earlier, AD FS will integrate with different organizations. This table will store the Organizations and the SSRS Folder path for the organization
  • Users Table – This table will store the users of the organizations
  • OrgGroupRole Table – This table will store the organization roles provided by AD FS claims
  • OrgGroupRoleSSRS Table – This table will relate the organization roles to real SSRS roles


Continue Reading Part 2