Julian Jelfs’ Blog

Forms Authentication for SSRS using SqlMembershipProvider

Posted in SSRS by julianjelfs on February 6, 2013

Sql Server 2008 includes a sample solution explaining for to write a security extension that will enable SSRS to work with Form Authentication. By default you will find this sample in the C:\Program Files\Microsoft SQL Server\100\Samples\Reporting Services\Extension Samples\FormsAuthentication Sample directory.

It’s reasonably easy to follow the instructions for this sample and the general idea is simple. You tell SSRS that you’re going to take care of authentication yourself and then you implement some code to capture and verify the user’s credentials.

When you’re trying to implement Forms Authentication you might be forgiven for thinking that you would just somehow use one of the membership providers already present in the .Net framework. That’s what I was expecting, but when you look at the sample code you see a class called AuthenticationUtilities containing all sorts of hideous manual Ado.Net queries (complete with hard-coded connection strings), and all sorts of nasty scary password hashing code. For example, the VerifyPassword method in this class does the following:

  • Retrieves the hashed password from the UserAccounts db
  • Hashes the supplied password using the same salt (though it is not necessarily using the correct hash algorithm
  • Compares the two hashes to see if they match

Not too much wrong with that, but if you decompile the SqlMembershipProvider you will find that it does pretty much exactly the same thing, but the code is a more robust.

This means that you can replace the contents of VerifyPassword with a call to Membership.ValidateUser(user, password). All you have to do to make this work is to set it up in the Report Server web.config exactly like you would in a normal web application:

<connectionStrings>
    <add name="UserAccounts" connectionString="server=*******;database=UserAccounts;uid=**;pwd=*****;Connection Reset=false;" />
</connectionStrings>

<authentication mode="Windows" />

<authentication mode="Forms">
      <forms loginUrl="Logon.aspx" 
             name="SqlAuthCookie" 
             timeout="90"
             enableCrossAppRedirects="true"
             path="/" >
      </forms>
</authentication>

<identity impersonate="false" />

<authorization>
      <deny users="?" />
</authorization>

<membership defaultProvider="SqlMembershipProvider">
      <providers>
        <add applicationName="MyApp" 
             connectionStringName="UserAccounts" 
             name="SqlMembershipProvider" 
             minRequiredPasswordLength="1" 
             minRequiredNonalphanumericCharacters="0" 
             requiresQuestionAndAnswer="true" 
             requiresUniqueEmail="false" 
             type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
      </providers>
</membership>



Note that you can and should use aspnet_regiis to encrypt that connection string.

In all the hours of googling involved in getting this to work I didn’t find any reference to the fact that it was perfectly possible to use the SqlMembershipProvider just like you would in a normal web application and leave all the nasty password handling to the experts. I’ve no idea why they decided to write the samples in this way…