How to use Forms Authentication with SSRS

SSRS out-of-the-box supports Windows Authentication.  Microsoft, however, has baked in an extension model that allows you to plug in your own authentication scheme.  So it’s fully possible to change over to Forms authentication.  This is particularly useful if you want to run reports from inside of your own website that uses Forms Authentication.

When you convert SSRS over, all use of the system will then use Forms Authentication.  So this includes:

• The web portal (Report Manager)
• The power user reporting tool (Report Builder)
• API access (Web Services use of Report Server)
• SQL Server Mangement Studio (when connecting to SSRS)

So I will say you end up with a robust solution.  But getting there is not for the faint of heart!  There are a number of steps to take including writing some custom code, adding some pages, and changing a bunch of settings in a number of config files.  I keep hoping Microsoft will one day make this a lever we just pull!  I’ve done these steps on more than one enterprise project in SQL Server 2005.  From what I see things haven’t changed much on this front in SQL Server 2008 R2 but feel free to drop me a line if you’ve got some notes there.

You can configure SSRS to use the very same aspnet database that your website uses and that’s probably where the real value lies.  So you’re actually passing through the same credentials from your web app through to SSRS.  If you do the extension right, you can even make use of ASP.NET Roles when granting access to reports and folders in SSRS.  That will reduce the administration burden and IT will thank you.

So here’s the steps in detail: Security Extension Sample for SQL Server 2005.  And if you’re using SQL Server 2008 R2, have a look at Implementing a Security Extension.

If you’re going to have to do the changeover several times (and I’d be surprised if you didn’t), do yourself a favour and write a tool to do it.  We did that on our team and it was a lifesaver, particularly since more than one group was involved in installations, and we had dozens to do!

-Krip