Using SQL Reporting Services 2005 and Forms Authentication with the Whidbey/2.0 SQLMembershipProvider

I saw a question last week asking if one could use the new 2.0 SQLMembershipProvider to build a Forms Auth solution with SSRS, so I created a prototype to find out. Long story short, you can.

 

Here's a walkthrough which will leave you with the following:

 

  • A make-believe web app which utilizes the VS 2005 Login controls to do Forms Authentication
  • SSRS configured to use Forms Auth, too
  • A modified SQL Reporting Services Forms Auth security extension which will use the SQLMembershipProvider
  • SSRS and the web app able to "share" auth cookies -- Logon one place, and you automatically are authenticated on the other application

 

Disclaimers, warnings, and assumptions:

 

  • I have not tested the "final product" of this walk-though in the real world. The only thing I can say is that it seems to work. You must perform your own due diligence to make sure my suggestions work for you, and that they make sense in your security environment.
  • This walkthrough also assumes you have done nothing with Membership on your machine yet...If you've already set it up, some of these steps won't apply.
  • You do have all the SSRS samples installed, right? If not, do not pass go.

 

Part 1: Basic Setup

 

After you're done with this "Basic Setup" section, both your web app and SSRS will utilize Forms Authentication. However, the two applications will *not* be integrated in any way (yet).

 

1. Work through this handy PAG paper to create your sample web app, configuring it to use SQLMembershipProvider:

https://msdn.microsoft.com/en-us/library/ms998317.aspx

 

NOTE: In step 4 of this article ("Create a New User"), make sure you write the username/password down -- you'll actually use them later when you're playing around with SSRS.

2. Test your work and make sure everything works per the article.

 

3. Browse to the C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Extension Samples\FormsAuthentication Sample folder, and follow the instructions in Readme_FormsAuthenication.htm to configure Forms Auth for SSRS

 

NOTE: In the "Testing the Sample" section of the instructions, pay attention to steps 3 and 4. Use the *same* username and password you created when setting up your sample web app just a little while ago.

 

4. Test SSRS and make sure that Forms Auth works.

 

FYI, in the previous two steps you're going to be doing some work that actually is wasted -- Namely, setting up a database / table that the SSRS security extension will use to store user names and passwords. Ultimately, the extension is going to leverage the database you created in step 1. But, go ahead, bite the bullet, and do this anyway -- you need to be 100% sure that SSRS Forms Auth is working BEFORE you try and integrate the SQLMembership provider.

Part 2: Modify SSRS to use SQLMembershipProvider

 

1. Crack open the Forms Authentication project (C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Extension Samples\FormsAuthentication Sample) again.

 

2. Add a reference to System.Web

 

3. Open AuthenticationExtension.cs, and add a using statement to the top of the class:

 

     using System.Web.Security;

  

   Next, find the LogonUser() method.

 

4. Replace the code in the method with this:

 

  if(Membership.ValidateUser(userName, password))

  {return true;}

   else

  { return false; }

         

5. Find IsValidPrincipalName()... replace the code you see with this:

 

  MembershipUser mUser;

  mUser= Membership.GetUser(principalName);

  if (mUser == null)

  {

    return false;

  }

  else

  {

    return true;

  }

         

6. Save your work, build the project, and re-deploy the security extension and its PDB file to same locations you copied it to while following the instructions in Readme_FormsAuthenication.htm.

 

7. Copy the <membership> section you used in Part 1, step 1 (setting up the web app) to your clipboard.

 

8. Backup the web.config file found in C:\Program Files\Microsoft SQL Server\MSSQL.?\Reporting Services\ReportServer folder.

 

    (? will equal a number between 1-X, representing where you installed SSRS...I don't know where that is)

 

9. Open web.config and paste the <membership> section right below the <authorization> section. Save it.

 

10. Navigate to C:\WINNT\Microsoft.NET\Framework\v2.0.50727\CONFIG (or wherever framework 2.0 is installed)

 

11. Backup machine.config

 

MAJOR LAMENESS ALERT

   

You've probably noticed that while you pasted in the <membership> element to web.config, you didn't also paste in the connection string that <membership> needs to connect to the user store. For some reason I just could not get this to work. If I pasted a connection string in, Report Manager would fail.

   

If you have the time or inclination to figure out if/where you can paste the connect string in web.config, let me know and I'll post your findings: This should work, but I just don’t have the patience to put the time in.

   

As a fallback, I'm just pasting the connect string into machine.config

12. Open up machine.config. Seach the file for an existing <connectionStrings> section. Mine was between the <runtime> and <system.data> sections. In my case, there was already a single connection string name "LocalSQLServer" defined.

 

13. Add the same connect string you used in Part 1, Step 1. My completely modified section looked like this:

 

  ...<runtime />

 

  <connectionStrings>

   <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated

    Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />

   <add name="foo" connectionString="data source=.;uid=sa;pwd=leave.MeAlone;database=aspnetdb" />

  </connectionStrings>

 

  <system.data>...

 

14. Save machine.config.

 

Browse to https://localhost/reports. If you copied everything correctly, SSRS should start and you should be provided with the SSRS login dialog. Go ahead and logon to make sure your changes work...You might also want to do something like rename the "SQL" users table you created in Part 1, Step 3, while setting up SSRS Forms Auth…by whacking this table, you can be sure that we're going against the SQLMembershipProvider store.

 

OK, so now you have SSRS working against the SQLMembershipProvider. We're almost home free.

 

Part 3: Allow SSRS and a web app to share cookies.

 

1. Read this:

 

   https://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformsauthenticationacrossapplications.asp

  

2. Now that you've read the article above, you know that we need to modify web.config for both the web app and SSRS. You'll also be very aware that:

  •  

      We'll make sure all the values in the <forms> section of each web.config file are identitcal

  •    We'll add a <machinekey> element to both web.config files which contains the same value

  •    We'll make sure that <machinekey> lives right under the <authentication> section.

   Here's the applicable portion of SSRS's web.config (found in C:\Program Files\Microsoft SQL Server\MSSQL.?\Reporting Services\ReportServer)

  

<authentication mode="Forms">

   <forms loginUrl="logon.aspx" name="sqlAuthCookie" timeout="60"

               path="/"></forms>

 </authentication>

   <machineKey validationKey="C50B3C89CB21F4F1422FF158A5B42D0E8DB8CB5CDA1742572A487D9401E3400267682B202B746511891C1BAF47F8D25C07F6C39A104696DB51F17C529AD3CABE" decryptionKey="8A9BE8FD67AF6979E7D20198CFEA50DD3D3799C77AF2B72F" validation="SHA1"/>

  

...and here's what the web.config for my web app looks like:

 

<authentication mode="Forms">

            <forms name="sqlAuthCookie" timeout="60"/>

</authentication>

<machineKey validationKey="C50B3C89CB21F4F1422FF158A5B42D0E8DB8CB5CDA1742572A487D9401E3400267682B202B746511891C1BAF47F8D25C07F6C39A104696DB51F17C529AD3CABE" decryptionKey="8A9BE8FD67AF6979E7D20198CFEA50DD3D3799C77AF2B72F" validation="SHA1"/>

 You’re done!

 

I’d suggest you modify the default.aspx page you created for your web app and put a command button on the form which will allow you to response.redirect to SSRS.

 

After you’ve done that, hit your web app and login. After you have logged in, you’ll redirected to default.aspx. Click the button on default.aspx, and voila, you’re inside SSRS…