SSRS 2008 - Enabling Forms Authentication with ActiveDirectoryMembershipProvider

SQL Server Reporting Services is a very powerful reporting tool however, by default, it uses Windows Authentication (NTLM) to authenticate users. This is fine if you're running over an intranet but if you're not then you need to do a little bit of work to enable forms authentication.

Fortunately, SSRS does support forms authentication in the form of a Security Extension. To give you some background, SSRS allows you, the developer, to extend it's functionality by the means of extensions. There are four main extension types:

1. Data Processing Extension - This allows you to define how to access data from a specific data source type not currently supported by SSRS.
2. Delivery Extension - Once a report has been generated it can be "delivered" to various locations, for example, it can be sent to someone via an e-mail address. This allows you to code for locations not currently supported by SSRS.
3. Rendering Extension - Currently, you can render a SSRS report in a wide array of formats, within a PDF or HTML are just two for example. A rendering extension allows you to extend this to support even more formats that aren't supported by SSRS.
4. Security Extension - This is the one we're interested in here. Security extensions allow you to precisely define how a user is authenticated and what permissions that user has. By default, this is set to work with Window authentication.

With the ability to write our own security extension, we can set up SSRS to use forms authentication rather than Windows. Fortunately, Microsoft provide a very good example of how to do this and that can be found here: http://msdn.microsoft.com/en-us/library/aa902691(SQL.80).aspx

This sample however, does not tell you how to use a MembershipProvider, in particular, I imagine the one most people will want to use is the ActiveDirectoryMembershipProvider which will validate the username and password provided by a user with an ActiveDirectory membership store. In my particular case, I want to be able to validate against an ActiveDirectory store but I also want to perform just a little bit more validation, so, I'm going to extend the ActiveDirectoryMembershipProvider to achieve this.

Fortunately, this is all very simple to set up. As all authentication is done via a web service, you, as a developer, can treat it as its own web application and so, with a couple of entries within the report server's web.config file, a couple of lines of code within your security extension and a class that extends the ActiveDirectoryMembershipProvider, then you're good to go. Here, I explain the changes required. 

First off, lets change the web.config file. We need to create a connection string that'll link to our ActiveDirectory store, to do this, just above the system.web tag, we need to add the following:

---

<connectionStrings>
    <add name="ADConnectionString" connectionString="LDAP://SERVERNAME:389" />
</connectionStrings>

---

Following on from the Microsoft sample, you should have changed the authentication tag to look something like this:

---

<authentication mode="Forms" >

<forms loginUrl="logon.aspx" name="sqlAuthCookie" timeout="60" slidingExpiration="true" path="/" />

</authentication>

<authorization> 

    <deny users="?" />

</authorization>

---

Under this tag, you'll need to add the following:

---

 <membership defaultProvider="MembershipADProvider">

    <providers>

        <add

          name="MembershipADProvider"

          type="MyNamespace.CustomADMembershipProvider, CustomADMembershipProvider"

          connectionStringName="ADConnectionString" 

          connectionUsername="DOMAIN\admin" 

          connectionPassword="Password"

          enableSearchMethods="true"

          attributeMapUsername="sAMAccountName"

          connectionProtection="None"/>

    </providers>

</membership>

---

Obviously, you'll need to change the connection username and password to be the admin username and password which will have the correct permissions to be able to read from the membership store.

That will register the membership provider with the web application so that we can then access it from code.

You'll notice I've changed the type to relate to our custom membership provider which we've yet to write. Lets do that now...

---

namespace MyNamespace 

{

    public class CustomADMembershipProvider : ActiveDirectoryMembershipProvider

    {

         public override bool ValidateUser(string username, string password)

        {

            bool isValid = base.ValidateUser(username, password);

            if (isValid)

            {

                // Extra validation, for example, may we don't want anyone with the username
                // of "BadUser" to have access.

                if(username.ToUpper() == "BADUSER"){

                    isValid = false;

                }

           }

            return isValid;

        }

    }

}

---

Just to give you a quick run down of what's going, we're overriding the ActiveDirectoryMembershipProvider so that we can use the base implementation to help us validate against the ActiveDirectory store that we defined within the web.config file. However, we now override the ValidateUser method so we can add in our own custom validation code. The first line of the ValidateUser method just ensures that the user is a valid ActiveDirectory user. If they are then we perform our custom validation, in this code I've given a very simple example of saying that if the username is some form of "BadUser" then we should not allow them access.

If we then compile that class and put the resulting DLL file into the bin directory of the ReportServer directory, it will now be accessible from the ReportServer.

Finally, we need to modify the security extension to use this membership provider. By this stage, I'm assuming you've atleast read over the Microsoft sample on how to enable forms authentication. If so, then you should know what I mean when I say that we need to modify the LogonUser method of the authentication extension. This method is the method that all logons will go through. It doesn't matter how you're logging on to the ReportServer, be it through a web service or through ReportsBuilder, this method will always be hit. We need to modify it so it uses our CustomADMembershipProvider. This is very simple now that we've modified the web.config file and placed our CustomADMembershipProvider DLL within the bin directory of the ReportServer, infact it's so simple, it only requires a single line of code, as shown below.

---

public bool LogonUser(string userName, string password, string authority)

{

    return Membership.ValidateUser(userName, password);

}

---

And that's it, in theory, you're good to go. Within SSRS 2008 Release 1, this worked first time, however, in release two, it didn't and I had to install an update (I installed Cumalative Update 5), doing this opened up a few more problems which I had to overcome before I could actually run reports from the Reports Manager. More about that in my next blog!