Custom Web App and Reporting Services Integration: Part 1, Authentication

Preface:
The following will be a series on integrating your custom web applications with SQL Server Reporting Services. It is intended to provide useful information for other developers posed with the same problem of integrating these two seamlessly.

Situation:
A new project came up at work, where we had to develop our own custom web application where users will be prompted to easily select reports, their parameters, and view them seamlessly all from one interface. This means, that we couldn't just direct the users to the interface provided by Microsoft and assume they'd know what to do. Also, due to requirements by the client, the reports should be available to only certain users with authentication resorting back to Windows / Active Directory.

Notes:
In part 1, I will discuss the method we took to integrate these two applications seamlessly by only being prompted once for windows credentials, or no prompt at all (using your signed on domain credentials). In our situation, the client is still running Novell, so Active Directory integration was out of question, so windows authentication will need to be handled by the reporting / web server.

An important item to note is when integrating the two items on different servers and not using active directory, seamless integration is not possible unless you manage to keep the users / groups in sync with each server (a challenge upon its own). Therefore, due to this requirement, the rest of the article will be based on single server or Active Directory authentication.

Authentication Methodology:
When developing your application, it is best to use windows groups to control authorization to the various reports and different sections of your web application. In our instance, we needed two different groups - Users and Admins. Users in the Admins group would have access in our application the ability to enter new information, while users in both groups had the ability to view all the reports.

Our first step was to create our groups and add the various users to the appropriate groups through the standard windows management console. Once our groups were setup, configuration of Reporting Services to allow the two groups access to view reports only was our next step (Note, this entry will not describe the methods for doing this as there are lots of resources to help you). Now that we're all ready to go, and the authorization to the reports have been tested properly using the Microsoft user interface in Reporting Services, its now onto configuring our custom web application to use windows authentication (again, we will not go into too much detail, but examples will be shown).

Okay, so open your Web.Config file for your custom web application, and add the following entries under your system.web node:

<authentication mode="Windows" />
<authorization>
    <allow roles="<SERVER_NAME or DOMAIN>\Users" />
    <allow roles="<SERVER_NAME or DOMAIN>\Admins" />
    <deny users="*" />
</authorization>
<identity impersonate="true" />

For any subdirectories that you wish to have different authorization on, follow the same template listed above, but leaving out the "authentication" and "identity" elements. In our instance, we needed a subdirectory called "Administration" that only users in the Admins group can gain access. So, after creating the sub directory, we created another Web.Config file in that subdirectory and added the following to the system.web node (Note that there are other methods of doing this type of configuration using a single Web.Config however for ease of example two Web.Configs are used):

<authorization>
    <allow roles="<SERVER_NAME or DOMAIN>\Admins" />
    <deny users="*" />
</authorization>

To briefly explain what we've done for authentication is told our web application to use the Windows mode of authentication. We then authorized only users in the Users and Admins roles access, and denied everyone else. Lastly, the application will impersonate the user that is currently logged in. This is a key concept since we'll be using credentials in part 2 and sending them with the Reporting Services API.

So, now our custom web application is setup - however there are still some settings within IIS that we need to set. The following settings were those that I found to work with our single server setup with no Active Directory verification, so you may need to fiddle with these if using Active Directory. To set the IIS permissions properly, open up IIS, find your Web Application, and view the properties. Click on the "Directory Security" tab, and hit the "Edit..." button for Authentication control. Once the new dialog has opened, uncheck "Anonymous access", ensure "Basic authentication" is checked, and "Integrated Windows authentication" is checked.

Launch your favorite flavor of an internet browser, go to your custom web application and see the magic work. You should immediately be prompted (unless using Active Directory & Digest mode, in which case if your currently logged onto the domain will pass directly through) for your username and password. After typing your credentials, you should be able to log in successfully to your web application. So, go ahead, take a deep breath and pat yourself on your back.

Part 2 will discuss using the Reporting Services API and your custom web application to provide that seamless look to your application.