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:
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…
Comments
Anonymous
January 18, 2006
The comment has been removedAnonymous
January 21, 2006
The comment has been removedAnonymous
February 14, 2006
We are using a web app with our own custom authentication using cookies. We want to be able to use that cookie to bypass the login screen when displaying a report to the user. Is this possible and if yes, how?Anonymous
February 20, 2006
Hi
It is a great tip.
It works fine.
One help required:
In Report Manager, how do you assign roles to groups with Role Manager enabled?
Presently, it is only taking usernames, but not accepting group names.
I have membership and roles enabled in my application calling the report server.Anonymous
February 22, 2006
Yes, you can do this and it will happen automatically (the login screen will only be displayed if the cookie doesn't exist). See:
http://blogs.msdn.com/bimusings/archive/2005/12/05/500195.aspxAnonymous
February 22, 2006
I haven't played with this, sorry.Anonymous
February 27, 2006
Hi,
it is great to interoperate SQLmembershipprovider with RSS2005.
But i need to hardcode the Membership.ApplicationName of the website somewhere in the RSS2005.
How if there are multiple website ( with different Membership.ApplicationName ) that need to access the same RSS2005 , but the RSS 2005 can only take one Membership.ApplicationName ?? ??
PLease help....i stuck in this..thanks
Anonymous
March 01, 2006
No, this wouldn't work...you essentially want o "multiplex" the user database, which we don't do...You couldn't do this in an ASP.NET app scenario, either.Anonymous
March 02, 2006
The comment has been removedAnonymous
March 02, 2006
Can you post the complete error message coming out of your log?Anonymous
April 20, 2006
Hi, I managed to get the FormsAuth to work from both (FormsAuth demo app) and the direct login to http://SERVERNAME/Reports.
But I can't get it to work to use the cookie from the web app to automatically login to RS. The custom Login.aspx is shown every time after I try to redirect to http://SERVERNAME/Reports.
I'm sure that I use only a single SQLServer tables users and I ca login with identical credentilas in both apps.
How can I validate if the cookie is passed from ASP to RS?
Is it possible to use the ReportViewer Control (ASP.net 2.0, ) in my ASP.net app and give the control the credentials from the forms auth?
Thanks a lot,
DirkAnonymous
April 21, 2006
We are very thankfull for your step by step approach. Everything works fine. But i have situation, while accessing the Report Builder, its asking again userid and password. How can i bypass that.Anonymous
April 21, 2006
Cannot find Readme_FormsAuthenication.html file and this directory. Can you post it?Anonymous
April 21, 2006
Sreeni -- There is no way to pass credentials to Report Builder if not using Windows Auth, so you will always be prompted for credentials in this scenario -- There is no way around it. Sorry.Anonymous
April 24, 2006
As i mentioned earlier i successfully implemented the forms authentication. But i am getting the following error while generating the report using Report Builder. "The permissions granted to user 'user1' are insufficient for performing this operation. (rsAccessDenied)".
Could you please help me out on this.Anonymous
April 24, 2006
The comment has been removedAnonymous
April 25, 2006
The comment has been removedAnonymous
April 26, 2006
The comment has been removedAnonymous
May 04, 2006
The comment has been removedAnonymous
May 08, 2006
How to add groups, after implementing the Forms Authentication? Is there any way where i can configure groups?Anonymous
May 12, 2006
I want to do authentication without using Login control. Is that possible, if so what would be the code for that?Anonymous
May 19, 2006
The comment has been removedAnonymous
June 20, 2006
The comment has been removedAnonymous
July 28, 2006
The comment has been removedAnonymous
August 01, 2006
Reporting Services 2005, as like its predecessor but now without edition limits, allows the developers...Anonymous
August 22, 2006
Hi !
I managed to get the custom authentication to work well for some time, but recently I've had a problem : it seems that the report server "loses" the informations about the user or it's profile after a random amount of time, and I get error messages like "The permissions granted to user '' are insufficient for performing this operation. (rsAccessDenied)" either with an empty user name, or with the username when it has just lost the role.
Would anybody have an idea about what is happening, and how to solve that ?Anonymous
September 18, 2006
Regarding the "object moved" error, if you are using ReportViewer you need to implement IReportServerCredentials. See the following article:
http://msdn2.microsoft.com/en-us/library/microsoft.reporting.webforms.ireportservercredentials.aspxAnonymous
October 19, 2006
Phil passed along a blog entry from one of our SQL colleagues which mentions how to support Forms AuthenticationAnonymous
November 09, 2006
Did anyone figure out how to post the connection string in the web.config instead of the machine.config?Anonymous
November 27, 2006
I have setup forms authentication for SQL 2005 RS and it works fine except for subscriptions. If I subscribe to a report using Report Manager when logged in as a user other than the "Admin" defined in rsreportserver.config, the subscription fails. I looked up the log files and found the following error:ReportingServicesService!library!d!11/27/2006-14:56:01:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Configuration.Provider.ProviderException: The Role Manager feature has not been enabled. at System.Web.Security.Roles.GetRolesForUser(String username)...It goes on to list the "CheckAccess" method of my CustomSecurity module.How does the subscription delivery driver work with forms auth?It appears that subscription delivery does not recognize that role manager is enabled when Report Manager and my application both work fine with role manager.Anonymous
November 30, 2006
PingBack from http://charliem.wordpress.com/2006/11/30/forms-authentication-with-reporting-services-and-asp-authentication-provider/Anonymous
December 20, 2006
Put ConnectionString below ConfigSection in web.config file of ReportServer.It worked for me.SSRS's web.config (found in C:Program FilesMicrosoft SQL ServerMSSQL.?Reporting ServicesReportServer)<configuration> <configSections> <section name="RStrace" type="Microsoft.ReportingServices.Diagnostics.RSTraceSectionHandler,Microsoft.ReportingServices.Diagnostics" /> </configSections> <connectionStrings> <add name="MyLocalSQLServer" connectionString="Initial Catalog=aspnetdb;data source=blre331;Integrated Security=SSPI;" /> </connectionStrings>Anonymous
February 05, 2007
I dont have time for a lengthy post, so here is some link-love for some great articles that have helpedAnonymous
March 19, 2007
I understand the authentication extension that has been done in the samples I've seen. However, I haven't understood the authorization framework in a way that makes it useful for a project my company is building. We'd like to use authorization from an external website's security model. We'll know the username with an authorization security extension in CheckAccess, GetPermissions, but want to use the authorization from an external source. All we seem to have access to in the IAuthorizationExtension interface is the ACL for a given item, not any other information about the item ( such as its path or name ) except for its type. How should the ACLs be managed for the items? I'd like to avoid the users using Reporting Services web site to have to manage a separate authorization system from the external website's authorization system.Anonymous
March 27, 2007
The comment has been removedAnonymous
April 16, 2007
How do you programmatically set permissions using the RSS2005 WebService? There is only a GetPermissions method and a SetProperties() method. But there is no 'security' property.I want to give write access to Report Builder users to save to their own folders and read access to the suborganization folder that they belong to etc.I cannot use the 'My Reports' folder feature because it restricts them to their own folder but does not grant them read access to the suborganization folder.Anonymous
April 16, 2007
Sorry I found that I was supposed to use SetPolicies to set permissions. My next questions is, I am using ReportingService.asmx, but there's another service called ReportingService2005.asmx. What is the difference?Anonymous
April 18, 2007
ReportingService.asmx is the SSRS 2000 Webservice, which was kept around for backwards compat. You shouldn't use it if you can avoid doing so, however as it will probably "go away" in the next version of SQL.Anonymous
April 22, 2007
Hi,I want do the Authorization in form Authentication.Only user can access this much reports that belonging in to the particular Reply.Anonymous
May 09, 2007
I have Forms authentication working fine, but when deploying Reports using Visual Studio it fails to display a login popup, with error "The permissions granted to user '' are insufficient for performing this operation.What's the solution?Anonymous
June 11, 2007
How to set the authentication mode to SSRS.Anonymous
July 10, 2007
HiNice article. I have a problem with report manager, when I login to it I get redirected back to the UILogin.aspx even though the CustomSecurity extension try to redirect to the Folder.aspx. Any ideas why this would happen? I can login to the Report Server with out any problem.ThanksAnonymous
July 10, 2007
Report Builder prompts for credentials every time it is launched. Is there any way to skip this since in my web app, the user has already entered his credentials once?Anonymous
July 11, 2007
SwissToni, I'm guessing you initially navigatge to SSRS using "http://localhost/Reports" instead of "http://machineName/Reports". If you don't use the machinename, you can see this behavior.Anonymous
July 11, 2007
Hi DS -If you use any sort of authentication mechanism beyond Windows in SSRS, Report Builder will always prompt for credentials. There are discussions of this elsewhere on the blog.Anonymous
July 11, 2007
The comment has been removedAnonymous
July 12, 2007
Hi Russell,That was the problem. Many thanks.Anonymous
July 16, 2007
The comment has been removedAnonymous
July 17, 2007
You're not in SharePoint Integrated mode, are you? Also, are you calling LogonUser before you call the other SSRS web service methods?Anonymous
July 18, 2007
Thanks a lot for the reply.No, I was not in the SharePoint Integrated mode but I know that for the same issue in the SharePoint Integrated mode with the Forms Authentication, Microsoft has given a hotfix (KB 939942) on 16th july 2007.For more information, visit the following link:http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278056Now coming back to my problem, I got this one resolved. I discovered that the credentials/cookie of my ReportingServices2005 object were not getting set properly. This are getting set properly now and things are working fine.DSAnonymous
August 03, 2007
The comment has been removedAnonymous
August 06, 2007
I've successfully implemented a forms custom security extension to authenticate against our LDAP server.However, deployment via Visual Studio fails due to insufficient permissions and does not prompt for login credentials to the server.Is there any way to deploy directly from Visual Studio while using Forms authentication?Anonymous
October 10, 2007
I am using Sql server 2005 reporting services. I have created reports on server. I want -- to connect reportserver from client's PC and Reports with Records should come logged in User wise. This I want to place on different panes, as this happens with Google Analytics and msn sites.Anonymous
October 10, 2007
Hi - Reporting Services isn't an analytics suite, it's a reporting tool. You're not going to find all the fun dynamic drag-and-drop stuff because it's not meant for that purpose.Anonymous
October 18, 2007
Hi, I'm not using the SQL membership provider im using an LDAP membership. Will this solution work with this also?Anonymous
October 19, 2007
Sure, but you'll have to re-write your code to talk to the LDAP provider.Anonymous
October 21, 2007
Hi thereI am trying to follow steps above, but at step 3, where i compile the sample application I get and wrror when browsing to the report server : http://<report server>/reports. The error is "loginURL is not a valid site element"What am i doing wrong?thanks in advanceKrishniAnonymous
October 22, 2007
hi thereI have managed to get the application working....yay. BUT i can only log on with the admin credentials while if I use a normal user then I get the following error when trying to use normal user credentialsSystem.Web.Services.Protocols.SoapException: Logon failed. ---> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Logon failed. at Microsoft.ReportingServices.WebServer.RSCustomAuthentication.LogonUser(String userName, String password, String authority) at Microsoft.ReportingServices.WebServer.ReportingService2005.LogonUser(String userName, String password, String authority) --- End of inner exception stack trace --- at Microsoft.ReportingServices.WebServer.ReportingService2005.LogonUser(String userName, String password, String authority)any help will be appreciatedKrishniAnonymous
October 25, 2007
I have successfully completed form authenication with and without LDAP provider.How do i call the function SetPolicies?When someone logs into the system, I want to set there role to browser. but I can't get it to work. Any ideas? Dim test As Microsoft.Samples.ReportingServices.CustomSecurity.ReportingService2005 Dim Item As String = "/" Dim Policies(0) As Microsoft.Samples.ReportingServices.CustomSecurity.Policy Policies(0) = New Microsoft.Samples.ReportingServices.CustomSecurity.Policy Policies(0).GroupUserName = TxtUser.ToString 'Policies(0).Roles = New Microsoft.Samples.ReportingServices.CustomSecurity.Role(0) {} Policies(0).Roles(0) = New Microsoft.Samples.ReportingServices.CustomSecurity.Role Policies(0).Roles(0).Name = "Browser" Policies(0).Roles(0).Description = "May view folders, reports and subscribe to reports." test.SetPolicies(Item, Policies)Anonymous
November 14, 2007
The comment has been removedAnonymous
November 29, 2007
Hi RussellI would like to thank you for this nice blog post. This has been helping me a lot and saving tons of my time.I am wondering whether you have any time to answser/comment on the below questions.How can we deploy a SSRS 2005 Report Model or a Report (RDL) to the Report Manager using Visual Studio 2005 while FORMS AUTHENTICATION + SQL MEMBER PROVIDER is on ?How can I upload the Report Model using Report Manager (I tried your sugggestions but no luck), Can you please provide more specific instructions on how to merge these files? Thanks in advance.PavaniEmail: pvajjala@doubleknot.comAnonymous
January 04, 2008
I tried to edit the connection string into both web.config in ReportManager & ReportServer. Its been two days I have been trying to input a connection string until i read your"MAJOR LAMENESS ALERT"I got it compile, but during run time when my object trying to grab the connection string. It is not there.Anonymous
January 18, 2008
Hi, nice discussion...I would also ask a question about the security context with wich a deployment is made from VS2005, which challenged me once and now those credentials are stowed somewhere and I so want to change the user, because I have subsequently created a security model to suit my situation. If I deploy with my "currently stowed away user credentials" I have to add that account to the Publisher role, which is not what I want. I would like to change the user? Howz this done? Thanks in advance!Anonymous
March 13, 2008
The comment has been removedAnonymous
March 13, 2008
Just a question for everyone: Anyone figured out how to deploy Data Sources and Report Models from Visual Studio 2005 when SSRS is in Forms Authentication mode? I get prompted to enter user name and password but it's not able to connect to the report server.Thanks,KripAnonymous
May 28, 2008
PingBack from http://mhinze.com/links-today-2008-05-28/Anonymous
June 06, 2008
I am able to authenticate to reporting service using the logonuser method. I call this methode from the reportproxy object while loggin in my custom application. But when I try to access the report catalog using the listchildren method it gives me an "Object moved to here" error. Any help will be appreciated.Anonymous
June 25, 2008
Great article. Will be trying soon...Anonymous
July 16, 2008
Generic lexapro. Lexapro. Lexapro focalin xr.Anonymous
November 19, 2008
registered Admin, cannot login.Russell, do you still check this article/blog?If so, I can give more detail.Thanks,JonAnonymous
February 04, 2010
Russel, I was getting an error "object moved" while working with forms authentication in both. The issue was I din't configure the machine key. Thanks a lot.Anonymous
August 06, 2010
Hi, I have done the same.. but getting the problem at " string cookieName = response.Headers["RSAuthenticationHeader"];" . This particular key name is not existed in the "Headers" and given null value. I am not getting this one. Please help me out in this issue. I followed the steps you mentioned. Let me know if i miss any...Anonymous
January 16, 2011
Hi, actualy I've got this working. Accessing the ReportManager Website with a browser everything works fine. But: Using ReportViewer or accessing a webservice the result is that not text/xml is received but text/html Any suggestions? SQL Server 2008 R2Anonymous
October 16, 2011
I want publish ssrs report in cliam based authentication.. can any one help me..