Reporting Services Single Sign On (SSO) Authentication – Part 2


The first post in this series focused on creating some core validation logic to validate a user request.  With the core validation completed the next step is to wire up all the pieces required by SQL Reporting Services.  This includes implementation of the security extension interfaces and configuration files.  The IAuthenticationExtension interface requires implementing the GetUserInfo, IsValidPrincipalName, LogonUser and SetConfiguration methods.  Leveraging the ValidationManager logic from the previous post the logic for these methods becomes simple.  A lot of the logic below is from the Forms Authentication sample.  IsValidPrincipalName and LogonUser use the ValidationManager.

   1: public class Authentication : IAuthenticationExtension {
   2:  
   3:     #region IAuthenticationExtension Members
   4:     /// <summary>
   5:     /// Required by IAuthenticationExtension. The report server calls the 
   6:     /// GetUserInfo methodfor each request to retrieve the current user 
   7:     /// identity.
   8:     /// </summary>
   9:     /// <param name="userIdentity">represents the identity of the current 
  10:     /// user. The value of IIdentity may appear in a user interface and 
  11:     /// should be human readable</param>
  12:     /// <param name="userId">represents a pointer to a unique user identity
  13:     /// </param>
  14:     public void GetUserInfo(out System.Security.Principal.IIdentity userIdentity, out IntPtr userId) {
  15:         // If the current user identity is not null,
  16:         // set the userIdentity parameter to that of the current user 
  17:         if ( HttpContext.Current != null
  18:               && HttpContext.Current.User != null ) {
  19:             userIdentity = HttpContext.Current.User.Identity;
  20:         } else {
  21:             // The current user identity is null. This happens when the user attempts an anonymous logon.
  22:             // Although it is ok to return userIdentity as a null reference, it is best to throw an appropriate
  23:             // exception for debugging purposes.
  24:             // To configure for anonymous logon, return a Gener
  25:             System.Diagnostics.Debug.Assert( false, "Warning: userIdentity is null! Modify your code if you wish to support anonymous logon." );
  26:             throw new NullReferenceException( "Anonymous logon is not configured. userIdentity should not be null!" );
  27:             //userIdentity = new GenericIdentity( "anon" );
  28:         }
  29:  
  30:         // initialize a pointer to the current user id to zero
  31:         userId = IntPtr.Zero;
  32:     }
  33:  
  34:  
  35:     /// <summary>
  36:     /// The IsValidPrincipalName method is called by the report server when 
  37:     /// the report server sets security on an item. This method validates 
  38:     /// that the user name is valid for Windows.  The principal name needs to 
  39:     /// be a user, group, or builtin account name.
  40:     /// </summary>
  41:     /// <param name="principalName">A user, group, or built-in account name
  42:     /// </param>
  43:     /// <returns>true when the principle name is valid</returns>
  44:     public bool IsValidPrincipalName(string principalName) {
  45:         ValidationManager mgr = new ValidationManager();
  46:         return mgr.ValidatePrincipalName( principalName);
  47:     }
  48:  
  49:     /// <summary>
  50:     /// Indicates whether a supplied username and password are valid.
  51:     /// </summary>
  52:     /// <param name="userName">The supplied username</param>
  53:     /// <param name="password">The supplied password</param>
  54:     /// <param name="authority">Optional. The specific authority to use to
  55:     /// authenticate a user. For example, in Windows it would be a Windows 
  56:     /// Domain</param>
  57:     /// <returns>true when the username and password are valid</returns>
  58:     public bool LogonUser(string userName, string password, string authority) {
  59:         ValidationManager mgr = new ValidationManager();
  60:         return mgr.ValidateUserInfo(HttpContext.Current.Request.Headers);
  61:     }
  62:  
  63:     #endregion
  64:  
  65:     #region IExtension Members
  66:     /// <summary>
  67:     /// You must implement LocalizedName as required by IExtension
  68:     /// </summary>
  69:     public string LocalizedName {
  70:         get { 
  71:             //throw new NotImplementedException();
  72:             return null;
  73:         }
  74:     }
  75:  
  76:     /// <summary>
  77:     /// You must implement SetConfiguration as required by IExtension
  78:     /// </summary>
  79:     /// <param name="configuration">Configuration data as an XML
  80:     /// string that is stored along with the Extension element in
  81:     /// the configuration file.</param>
  82:     public void SetConfiguration(string configuration) {
  83:         //XmlDocument doc = new XmlDocument();
  84:  
  85:         //doc.LoadXml(configuration);
  86:         //if ( doc.DocumentElement.Name == "SecurityConfiguration" ) {
  87:         //    foreach ( XmlNode child in doc.DocumentElement.ChildNodes ) {
  88:         //        if ( child.Name == "ConnectionString" ) {
  89:         //            _userValidationConnectionString = child.InnerText;
  90:         //        } else {
  91:         //            throw new FormatException( "Security configuration element missing from config file" );
  92:         //        }
  93:         //    }
  94:         //} else {
  95:         //    throw new FormatException( "SecurityConfiguration element expected" );
  96:         //}
  97:  
  98:     }
  99:  
 100:     #endregion
 101: }

The other extension is the IAuthorizationExtension.  The implementation here is the same as that from the Forms Authentication sample, so it won’t be repeated here.  There is also an anonymous version of this extension that exists and was documented by James Wu in this blog post.

The last piece is configuration and changing the various configuration files in SSRS so that the custom security extension works.  Most of this is documented in the forms authentication sample, but will be repeated here.  The core configuration files are the Report Manager and Report Server web.config files and the rsreportserver.config file.  Report Manager requires changes to how impersonation is handled and, in our scenario, some additions to the appSettings section.  If you have also implemented an HTTPModule in order to validate the request and redirect to an authentication server that would have to be registered as well.  The changes to the web.config file are shown below.  This is located by default in the C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager directory. 

Note that we set identity impersonate to false.  The various <appSettings /> keys are used throughout the code modules for various things.  The important ones here are the AuthKey, which is a key added to our header to ensure that the request is coming from a trusted source.  Again, there are more secure methods to use than this, but has been placed unencrypted in the configuration file for this example.  The ReportMainConnectionString is used to connect to the user database and validate the UserToken that is passed in the header.

   1: <?xml version="1.0" encoding="utf-8"?>
   2: <configuration>
   3:     ...
   4:   <system.web>
   5:     ...
   6:     <identity impersonate="false" />
   7:     ...
   8:     <httpModules>
   9:       ...
  10:       <!-- REGISTER YOUR HTTP MODULE HERE IF REQUIRED -->
  11:     </httpModules>
  12:   </system.web>
  13:   <appSettings>
  14:     ...
  15:     <!-- your-server-name should be replaced with the value for your server -->
  16:     <add key="ReportServer" value="your-server-name" />
  17:     <add key="ReportServerInstance" value="RS_MSSQLSERVER" />
  18:     <add key="ReportServerWebServiceUrl" value="http://your-server-name/reportserver" />
  19:     <add key="AuthKey" value="1010101010" />
  20:     <add key="ReportMainConnectionString" value="SERVER=your-server-name;Initial Catalog=UserAuthenticationStore;Integrated Security=SSPI;Trusted_Connection=Yes" />
  21:   </appSettings>
  22:     ...
  23: </configuration>

Similar changes are made to the Report Server web.config file.  By default this is located in the C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer directory.  See the sections that change below.

   1: <?xml version="1.0" encoding="utf-8"?>
   2: <configuration>
   3:   <system.web>
   4:     ...
   5:     <authentication mode="Forms" />
   6:     <authorization>
   7:         <deny users="?" />
   8:     </authorization>
   9:     <identity impersonate="false" />
  10:    ...
  11:   </system.web>
  12:   <appSettings>
  13:       <add key="ReportServerWebServiceUrl" value="http://you-server-name/reportserver" />
  14:       <add key="AuthKey" value="1010101010" />
  15:       <add key="ReportMainConnectionString" value="SERVER=your-server-name;Initial Catalog=UserAuthenticationStore;Integrated Security=SSPI;Trusted_Connection=Yes" />
  16:   </appSettings>
  17:     ...
  18: </configuration>

Configuration changes also need to be made to the rsreportserver.config file.  This is the core configuration file for reporting services.  The changes for that file are shown below.  There are two sections that may need additional explanation.  In the UI/CustomAuthenticationUI/loginUrl element the page used to redirect an unauthenticated request is provided.  In the example here the UILogon.aspx page from the previous post will handle taking an unauthenticated request, validating the data and issuing the authentication token. 

The Extensions/Security section details the security extension developed for handling SSO security in the sample.  The first section provides the authorization extension.  The AdminConfiguration/UserName element contains the username that will be considered the System Administrator on the SQL Reporting Services instance.  Review of the Authorization extension shows that the extension reads in this configuration element and then makes decisions based on the value.  This provides a means for someone to enter other users that could be considered System Users or System Administrators.  The Extensions/Authentication section defines the authentication extension to use for the SSRS instance.

   1: <Configuration>
   2:     ...
   3:     <Authentication>
   4:         <AuthenticationTypes>
   5:             <Custom/>
   6:         </AuthenticationTypes>
   7:         ...
   8:     </Authentication>
   9:     ...
  10:     <UI>
  11:         <CustomAuthenticationUI>
  12:             <loginUrl>/Pages/UILogon.aspx</loginUrl>
  13:             <UseSSL>False</UseSSL>
  14:         </CustomAuthenticationUI>
  15:         <ReportServerUrl>http://your-server-name/reportserver</ReportServerUrl>
  16:         <PageCountMode>Estimate</PageCountMode>
  17:     </UI>
  18:     <Extensions>
  19:         ...
  20:         <Security>
  21:             <Extension Name="Forms" Type="Sample.ReportingServices.Security.Authorization, Sample.ReportingServices.Security, Version=1.0.0.0, Culture=neutral, PublicKeyToken=d04ec164f4506a18">
  22:                 <Configuration>
  23:                     <AdminConfiguration>
  24:                         <UserName>administrator@sample.com</UserName>
  25:                     </AdminConfiguration>
  26:                 </Configuration>
  27:             </Extension>
  28:         </Security>
  29:         <Authentication>
  30:             <Extension Name="Forms" Type="Sample.ReportingServices.Security.Authentication, Sample.ReportingServices.Security, Version=1.0.0.0, Culture=neutral, PublicKeyToken=d04ec164f4506a18" />
  31:         </Authentication>
  32:         ...
  33: </Configuration>

The final piece is deployment.  Deployment consists of deploying the assembly, deploying our custom page and changing additional configuration files.  The page and assembly are easy.  Deploy the assembly to the Report Manager\bin and Report Server\bin directories.  Deploy the UILogon.aspx page to the Report Manager\Pages directory.

Code Access Security policy, CAS, also needs to be addressed.  By default assemblies in the bin directory run with limited trust.  To grant full trust to your assembly open the rssrvpolicy.config file.  This file is located in the Report Server directory of the SSRS installation.  In the file find the <CodeGroup /> element that has a Url membership of $CodeGen and add the following <CodeGroup /> element afterwards.  This example uses a Url condition.  Best practice would dictate using a strong name membership condition by using a public key blob.  To extract the public key blob (not the public key token) use the Secutil.exe tool as follows: secutil.exe -hex -s MyAssemblyName.dll.

   1: <CodeGroup
   2:     class="UnionCodeGroup"
   3:     version="1"
   4:     Name="SecurityExtensionCodeGroup"
   5:     Description="Code group for the custom security extension"
   6:     PermissionSetName="FullTrust">
   7:     <IMembershipCondition 
   8:         class="UrlMembershipCondition"
   9:         version="1"
  10:         Url="C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\Sample.ReportingServices.Security.dll"
  11:     />
  12: </CodeGroup>

The Report Manager policy file, rsmgrpolicy.config, also needs to be updated.  This file is located in the Report Manager installation directory.  In this file the MyComputer zone should be updated to have a FullTrust PermissionSetName.  

   1: <CodeGroup 
   2:     class="FirstMatchCodeGroup" 
   3:     version="1" 
   4:     PermissionSetName="FullTrust"
   5:     Description="This code group grants MyComputer code Execution 
   6:     permission. ">
   7:     <IMembershipCondition 
   8:         class="ZoneMembershipCondition"
   9:         version="1"
  10:         Zone="MyComputer" />

That’s it.  Your sample should now work.  A full Visual Studio 2010 project is available for download and modification.

Comments (81)
  1. Roberto says:

    Hi Cliff, I would like to congratulate you.

    Very good article.

  2. TA says:

    Our developer implemented custom code in order to access report manager via Webshere role base security. We currently use 2008 R2 enterprise edition. All is working as it should except for one major issue which is that I am not able to gain access to the actual Reporting Services Server directly in order to modify/create a custom role (task) – I can easily access it and make the modifications before MS Authentication was changed to Forms Authentication. Now I cannot access the Reporting Services Server. Accessing the DB server is still ok.

    My question is- can the RS server still be accessible without breaking the link and rolling back to MS AD Authentication – can the code be modified to recognize a valid password?

    Your assistance is greatly appreciated. Please don’t hesitate to ask if you would like additional information. Thank you

  3. TA,

    Are you saying that you can't access the Reporting Services Server or that you can't access Report Manager?  Accessing the server is unrelated to accessing Report Manager.  The LogonUser method is used to validate the user.  In this method you can validate the user's username and password.  In this case we wanted to validate the hash in the header and the username with the knowledge that the router has already validated the user.  So the code flow here is a little different than your scenario.

    In LogonUser you would want to authenticate the credentials against Webshere and return true/false based on the response.

    You should be able to roll back to Windows AuthN by resetting the web.config and other configuration files back to their original state.

  4. Dale says:

    I am getting this error when I wire everything up. The solution is letting me through but does not seem to be passing my credentials => '' I am running Sql srv 2008 R2

    User '' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

  5. Dale,

    I am not sure where your problem lies without more information.  Have you assigned the user System User rights in SSRS and Browser rights for the root folder?

  6. Dale says:

    Yes sir. I made back ups of all the config files and can place the new ones in or bypass all the given code by placing the old ones back into play. When old ones are in place everything works as it used to. The part that gets me is the '' … Does that indicate that my user information is getting passed to ssrs as '' ? I should have admin privileges.  In AD I am in the Admin role and I have assigned my username to the root folder on its own as well.

    Thank you for your reply,

    Dale

  7. You should be able to place a break point in your code and see what is getting passed in.  Are you expecting the user to be the same?  One is windows the other is what?  I would imagine your new user would be something else other than the windows account and you would need to ensure that user has system user rights to SSRS.

  8. Dale says:

    I am adding users that are members of our AD enviroment. I know that they are assigned because SSRS gives a hickup if it can not find a user or a group… I am not sure if i am answering your question correctly. I am just adding know users to the folder security area. If i allow SSRS to ask for username/password (nativiely) I can log on and all shows up and functions as expected.

  9. Dale,

    If you are already using AD then why the custom security model?  If you want it to be SSO then just add the SSRS site to your trusted sites so the credentials are automatically passed.

  10. Dale says:

    oh… I didn't know that would work.

  11. Saeid says:

    With regards to active directory single sign on, setting ssrs site as trusted site would require going to end user machine and enable site as trusted site.

    Is there any other way to bypass authentication window?

  12. Saeid says:

    addition to my comments above:

    The site is internal site

  13. You can use group policy to set the trusted site or set the site as an intranet site on each user's machine.  You could also change the user's security permissions in the browser to automatically send user name and password, but I would set the site as a intranet site using GP.

  14. Saeid says:

    not all users are windows users. some are macintosh users. not all of them would be using internet explorer. This might not go well with company by updating all users machine.

    My workaround would be to use the code provided on this site and connect it to active directory. would that be possible?

  15. Saeid says:

    can i create one dummy user ( with the code above) with only browser role( only to view reports) and embed it in my asp.net application inorder to bypass the authentication?

    my goal is to bypass authentication and give users to view only reports.

  16. Saeid,

    If you are embedding in your application why not create a single user as you have suggested and leverage the web service to access reports or use the report viewer control.  If they aren't connecting directly to SSRS then what you have suggested is certainly a way to integrate.

  17. Saeid says:

    thanks cliff for the reply.

    i am not looking at report viewer option as there are so many parameters.

    Do i need to implement forms authentication or should i create a dummy user in active directory and try to login into web service with AD user? is the second option possible? and can i have single sign on with this option? are there any examples out there?

  18. Saeid,

    You can create a service account in AD and use that.  There is no reason to implement forms in this case.  You won't need SSO because you are using a single user to connect.

    msdn.microsoft.com/…/ms154673(v=sql.105).aspx

  19. Saeid says:

    After login, can i redirect users to a report page (e.g. redirecturl to report link)?

  20. Saeid,

    No.  If you do that then they will be connecting to SSRS as themselves because they are connecting using their browser from their machine, not your impersonated user on the web server.  You can provide them the reports via the web service as I mentioned through your application, but there is a lot of work there to gather parameters, etc. If you want the user to connect directly then you have to authenticate them to the ssrs server.

    If you are using AD and you want SSO the easiest way is to get the browser to automatically pass the current credentials to the site.  If this isn't a possibility then perhaps a custom authentication extension will help, although you will need a way to seamlessly gather the user's credentials.  Forms authentication is going to still prompt the user to enter their username and password unless you have a way to capture that without user intervention.

    If your authentication store is AD then you probably can't do this without prompting the user to gather their credentials unless you put some passive authentication in front of it like ADFS.

  21. Saeid says:

    I will ask network team about ADFS Option.

    Can i override logonuser to automatically logon as dummy user? if yes. is this option available through AD or forms?

    The problem with adding site to list of intranet sites is that users need to be using internet explorer. I think this configuration can be done programmatically. However we have end users who use different browser or different operating system such as macintosh.

  22. Saeid,

    Yes you can override it, but by doing that with a single user you won't be able to apply permissions to any of your reports.  Everyone will have the same access to everything.  I don't recommend that.

    Your problem is this…you don't want to prompt your user for a username and password.  If you don't prompt then, then how do you know who they are?  Does your network provide a means of tracking the user and determining if they have been valid?

    If you don't prompt and you opt for single sign on then you still need to know who the user is and validate that they have been authenticated properly.  If you go to forms then you still need a way authenticate them.  If they aren't passing that validation via a cookie or SAML token or something else through passive authN then you have to get their username and password and this is typically through some prompt.

    You and your network team need to sit down and figure out how this can happen.  Ultimately how it works is up to you and SSRS is flexible enough to handle the majority of cases.  SSO doesn't work to silently authenticate unless your network and infrastructure support a way to do this.

    Good luck.

  23. Saeid says:

    Cliff,

    How about overriding getuserrole() to alqays return browser role?

    My website is integrated with another application which will provide username to my site. This username is required to run the reports in hidden field with some twist. if no username is provided, the website will ask user to login through AD in the website form. Do you think this is good option?

  24. Saeid,

    If you do that how do you deploy reports?  They need more than browser role.

    If you are comfortable with a username provided from another application from a security standpoint then try it.  This is something you and your network team need to figure out.  I don't have enough information.

  25. DK says:

    I seem to have resolved most of the issues experienced when getting the extension installed and configured. I now get the logon screen, get logged in and all the reports are available. however I don’t seem to get a cookie as my next visit (close browser and revisit reports page) requires me to logon again. Is this actually a signle sign on solution or is this simply forms authentication?.

    In my event log I get the following after every logon.

    Application popup: Assertion Failed: Abort=Quit, Retry=Debug, Ignore=Continue : Warning: userIdentity is null! Modify your code if you wish to support anonymous logon.

    at Authentication.GetUserInfo(IIdentity& userIdentity, IntPtr& userId)

    at WebServiceHelper.ConstructRSServiceObjectFromSecurityExtension()

    at Global.ConstructRSServiceFromRequest(String item, Boolean checkSharePointAcces)

    at Global.GetRSService(Boolean checkSharePointAccess)

    at Global.DispatchRequest()

    at Global.Application_AuthenticateRequest(Object sender, EventArgs e)

    at SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()

    at HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

    at ApplicationStepManager.ResumeSteps(Exception error)

    at HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)

    at HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)

    at HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)

    at HttpRuntime.ProcessRequest(HttpWorkerRequest wr)

    at RsHttpRuntime.ProcessRequest(IRsHttpPipeline rsHttpPipeline)

    Any ideas anyone?

  26. DK,

    How you do SSO is up to your situation.  At the end of the day SSRS will issue an FBA ticket to support authN within the SSRS application.  Your task is really to write code to integrate your SSO solution with SSRS.  

    In your case if you want a persistent FBA ticket issued you will need to change the parameter that gets passed when the ticket is issued to create a persistent cookie.  The default is a session based cookie.

  27. Hi Cliff,

    I Implemented the sample exactly how you mentioned, But when I try to access the report manager I get error saying "The report server encountered an unhandled exception in HttpApplication. (rsUnhandledHttpApplicationError) Get Online Help

    The file '/ReportServer/login.aspx' does not exist." And when i try to access the reports url. I get an error saying that "User '' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed". Please let me know what could be problem or did I miss anything.

    Thanks,

    Darsan.

  28. Barefoot says:

    A. I had the same issue as Darsan.Net. After some research, I discovered that "login.aspx" is the SSRS default page. This is addressed by inserting "<forms loginUrl="/ReportServer/Pages/UILogon.aspx" name="sqlAuthCookie" timeout="60" path="/"></forms>" in the web.config file and "<UI> <CustomAuthenticationUI> >loginUrl>/Pages/UILogon.aspx</loginUrl>" in the rsreportserver.config file.

    But even after that, I still can't get this to work. How the heck does the AuthToken operate as the user's password? (UILogon.aspx.cs, the line beginning with "server.LogonUser")

  29. @Barefoot

    If you read the first article you will note that this was something the customer had implemented in their environment that the load balancer added to requests.  So it is part of the header and specific to their implementation.  You may have no need for it or another way that your SSO gets implemented.  You will need to adjust the code for your specific scenario.

    Thanks!

  30. Hi ,

    I implemented the forms authentication sample using custom security extension. Single sign on is working fine..I deployed few reports in report server. When trying to chek out the reports i am getting the below error

    "An error has occurred during report processing. (rsProcessingAborted)

    Cannot impersonate user for data source 'AdSource'. (rsErrorImpersonatingUser)

    This data source is configured to use Windows integrated security. Windows integrated security is either disabled for this report server or your report server is using Trusted Account mode. (rsWindowsIntegratedSecurityDisabled)"

    I found few sources that since the authentication type is configured as Integrated security I am getting the above error. So, I again installed a new instance of reporting server with mixed mode authentication(Initially i installed report server with windows authentication mode only since I read somewhere that the custom security extension only works when report server is initially configured as Windows authentication,, not sure how far this is true). I made the configuration and code changes and when trying to restart the service. The service is not started.  Please advise.

    Thanks.

  31. @darsan.net

    you'll want to look at the ssrs service logs and potentially turn up the verbosity of the logging level to get to root cause.

  32. Yes, Thank you for suggestion. I didn't exclude the two tags in in Reportserver config file which was hindering my services. Now I am able to run the services. When I tried to run the Report server or Report manager URL I am getting "Service Unavailable Error". Please clarify me whether custom security forms authentication sample can be run in mixed mode installation. With respect to the link below under Considerations heading it is mentioned as.

    technet.microsoft.com/…/aa902691(v=sql.80).aspx

    "It is not possible to run a report server under a mixed-mode security system (for example, both Windows and Forms Authentication). This is true of any ASP.NET application"

    I am confused whether because of this reason I am getting service unavailable error.

    Thanks,

    Darsan.

  33. @darsan.net

    What that is referring to is the security method of SSRS, not SQL.  You can install SQL and SSRS under mixed mode, but as far as your security for how users authenticate to SSRS you have to choose.  There are two types of security for SSRS.  How the user authenticates to SSRS and how SSRS authenticates to the potential data source.  The data source can authenticate in multiple ways, regardless of how the actual user connects to SSRS.  This is shown in the UI where you have to select whether you are using windows or some other authentication mechanism.  As long as you aren't using pass through with forms auth (custom) you can choose how you connect to the data source.  Hope that makes sense.

  34. Great Explanation. Thanks. But, If in the case that the data source can authenticate in multiple ways, irrespective of how user connects to SSRS, then when I am trying to access the repors(which are created to use windows integrated security) I should be able to see the reports, but I get an error in reports "This data source is configured to use Windows integrated security. Windows integrated security is either disabled for this report server or your report server is using Trusted Account mode. "

    In this case if I either change the windows integrated security to true or connect to data source via sql credentials I should be able to see the reports right??

    Thanks,

    Darsan.

  35. @darson.net

    Correct.  The type of authentication you are using to the datasource should not impact the authentication mechanism used for report server.

  36. Andrea Ravasi says:

    Hi Cliff,

    first of all thanks a lot for this extensive sample.

    I am struggling with with getting the basic wire-up working. I have written custom authorization & authentication extensions for other software products in the past, so I guess I have a good understanding of the fundamentals, but I seem to struggle with the basic wire-up here:

    • I assumed that one could get authorization working without custom authentication (extending only the IAuthorizationExtension interface and leaving authentication still to the AD. I am trying but startup fails with "rsServerConfigurationError" as soon as the Security Extension is in the with rsreportserver.config. Is this even possible or do I have to also implement custom authentication in this case?
    • I am trying to do this with SQL Server Reporting services 2008 (not R2) – IS this even possible?

    • Having reporting services installed only on a dev server and visual studio locally is there any way I can hook up a local debugger or at least get more error details?

    • In this company SSO works by an RSA encrypted cookie allowing user identification. Basically the idea is to pass this cookie along with each request. An HttpHandler that validates it and does redirects already exists. Is this setup of passing along the cookie even possible with SSRS (I mean that impersonation is not possible is clear, but given that we could extract the userName is this scenario in general possible with SSRS?)

    I realize this are pretty basic & many questions, I would be thankful even for simple search-hints or links to relevant resources (I am not asking for long explanations)

    Best regards,

    Andrea Ravasi

  37. @andrea

    Sounds like there may be something wrong with the configuration file.  You can setup a debugger through Visual Studio against the reportserverservice.exe.  That should break into your code, but it doesn't sound like you are even getting that far.

    In your case, are you sure you don't need something to validate the authN cookie?  If not you could implement the authN piece by just bypassing it and returning true, but I think you would want to validate it in some form and also extract the relevant pieces so that you know who the user is.  Where is the HttpHandler?  It would need to be on the report server also.

    authZ can be handled in SSRS without a custom extension unless you have an external system that would do that, which in case you would need to write the logic around that.

  38. Jason says:

    Cliff, great article.  I have a web application running on Azure and I want to take them to a reporting server on a different domain so I implemented your custom solution above.  However, rather than going to the report manager I want to take them directly to the report url.  Is this possible?  If so what configs would be necessary?

  39. @jason

    If the report url is sent with the query string you could just redirect the user request after the FBA token is set in code.  You shouldn't need any configuration changes, just a code change.  If you look at the Page_Load event code there is redirect logic there to do what you want, but you may have to change it to meet the way it works in Azure.

  40. kumar says:

    Hi Cliff,

    Thanks for the great article on SSO security extension with SSRS. I am working on the same kind of implementation in my organization. In my requirement, authentication for the user is done though web service. My authentication provider exposes one web service. I should call this web service by sending userID to authenticate my user. I get true or false return value. Please let me know which part of your code should be changed for my requirement.

    Thanks in advance

    Kumar

  41. @kumar – LogonUser is the method you want to target,

  42. Nathan WS says:

    Hi Cliff,

    I followed all the steps as you suggested.

    Do we really need to create a user accounts database? since i am using the reportserver database and validating the user and I am getting an error "Authorization ticket not received by LogonUser" even i am returning true in Validation manager.

    1. I am passing the user information in query string and validate the user information and redirect
    2. I am not getting any message even i put response.write line by line.

    Please let me know if you could help

  43. @Nathan

    Your implementation will depend on your requirements and how you handle SSO.  This article is just an example of a particular implementation.  You can debug and step through the code by attaching to the ReportServerService.exe service in Visual Studio.

  44. Joe says:

    great article. almost working, but stuck in one place. in ValidateUserInfo method,  headers["UserToken"]  is always null. who sets this token?

  45. @joe,

    for this article it was the load balancer that set this token internally for the customer.  Your scenario will likely be different.  The idea is that your SSO tool sets some value that SSRS can validate to determine the current user and ensure they should have access to SSRS.

  46. Joe says:

    @Cliff – Thanks for quick response. Need your help!

    we have ssrs 2012 on server2. we have asp.net portal on server1. user login to server1 ( http://server1/somepage.aspx). I have compiled customsecurity sample and uploaded in the server2 and also configured as mentioned in your article and other relevant article. I am also able to debug in ssrs server.

    everything seems fine except I do not know a way to pass those two headers – authToken and UserToken from server1. Please help!

  47. @joe — you may not need them.  really it depends on how sso is implemented within your company.  what values does your sso solution provide that can be read by ssrs?  if you don't have a centrally managed sso (authentication store) then you will need to pass SSRS some information to let them know the user is authenticated with your authN system and allow SSRS a means to validate that.  what are you using for authentication?

  48. Joe says:

    @Cliff –

    here is aspx page from server1.  this is loading reportviewer from server2.

    server2 has custom forms authentication (I followed your article, just modified to use aspnet membership provider to authenticate ).

    <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">  

              <rsweb:ReportViewer ID="rvReports" runat="server"          

                   Font-Names="Verdana" Font-Size="8pt" ProcessingMode="Remote" Height="100%"  Width="100%">

                   <ServerReport ReportServerUrl="http://server2/ReportServer&quot;  >                

                   </ServerReport>

               </rsweb:ReportViewer>

    </asp:Content>

    how to tell server2 that user is already authenticated? need your help….

  49. @joe – forms authentication in and of itself isn't SSO.  Your problem is FBA is embedded into the ASP.NET application.  It isn't a service that provides authentication for multiple applications.  That is SSO.  If you have that you have a way to pass information around and validate it across applications.  In your case the authN is a silo within the ASP.NET app.  

    You can reuse the FBA Membership provider within SSRS, but you need a secure way to pass the user information to SSRS.  That means you can't use the query string (clear text and easily replayable) and probably not the ReportViewer control to do that.

    One option may be to authenticate the user to both systems when they sign into you asp.net application by making a request to SSRS.  This might set the cookie in the browser so when report viewer makes a request the cookie is passed along with the request.  A lot of detail beyond the scope of the comments here.

    Lots of "it depends" here bc I don't know your environment, requirements, etc.

  50. joe says:

    @cliff – thanks! You have understood what we are trying to do here. perfect!

    can you suggest or guide me on custom/open source/microsoft sso that I can use in this scenario.

    thanks again!

  51. @joe – active directory 🙂  or ADFS.

    There are several out there that will need you to evaluate based on your requirements.  Not knowing those I can't make a recommendation.

    I think the report viewer control may be a blocker here, because of the way it works and abstracts some key capabilities away from you, one of those passing additional data to SSRS securely.

  52. Joe says:

    @cliff – do you know of any client side report viewer control or asp.net control? thanks!

  53. @joe – I don't, outside of the one you have.

  54. Omar says:

    Hi Cliff,

    Thank you for the wonderful articles on SSO. I am trying to customize the example for an ASP.NET Web Api situation but cannot get the Authentication token from the Web Api to be passed to the Custom Security module. Basically this is what am trying to do:

    Users will log in through my Web Api login method and once they have successfully logged I will generate an Encrypted token and return it as a cookie and setting the Reporting Server as the Domain (http://machinename/ReportServer_XYZ) in the cookie which is different from my Web Api domain (localhosts:25363).

                   var cookie = new CookieHeaderValue("AUTHTK", "Test");

                   cookie.Expires = DateTimeOffset.Now.AddHours(1);

                   cookie.Domain = "mycomputer";

                   cookie.Path = "/";

                   response.Headers.AddCookies(new CookieHeaderValue[] { cookie });

    I can see that the Web Api login method successfully returned the cookie but when i try to access the report server the cookie is not sent with the request. I have tried this on Chrome, IE and Firefox.

    I have also tried setting the cookie in the headers without success.

    Is there a way to pass a token from my Web Api to the Custom Security module?

    Regards

    Omar

  55. @omar – My understanding is that a cookie is only valid in the domain in which it is set.  I would add a header value to the response from the web api sso instead of a cookie.  That would allow the report server to fetch the value, decrypt it, validate it, etc.

  56. Omar says:

    Hi Cliff,

    Thanks for your reply.

    I can set the headers in my Login action and they are returned correctly to the client. My challenge is sending these headers when a user tries to open a report on the report server.

    On my client I am calling Javascript window.open(url) and this does not allow me to set the headers. That was why I was trying to use a cookie hoping that the browser will automatically transmit the cookie.

    What am thinking now is to send the token as part of the url and Access it on query string. This seems to work fine for Report Server url but not Report Manager.

    Is it possible to have Report Server on Custom Authentication and leave Report Manager on Windows Authentication?

    Once again thank you for the help.

    Regards

    Omar

  57. @omar,

    That clarifies it a bit.  I think what you want to do is pass a token that ssrs can validate with the web api.  The token would have some sort of time period associated with it to prevent replay and validation could return the actual token used for authN.

    Report Manager should be able to do the same thing.  Report Server and Manager should match authN methods.

  58. Scott says:

    Cliff, great solution!

    Can you tell me if this will work with Report Builder?

  59. @scott

    it should … technet.microsoft.com/…/ms365173.aspx, but I haven't tested it.

  60. Gagan Sawant says:

    Hi Cliff – Thanks for this great solution. I am having a small issue though and need your help on it.

    I had configured RS with Windows Auth and had created many users. This setup was working just fine. Then I implemented the SSO with your solution. Now, when I login with the AdminConfiguration, I see everything just fine but my existing users.

    When I try to re-create the users, the system does not recognize the user. It gives the error: "the user or group name 'username' is not recognized. (rsUnknownUserName)".

    Now, when I try to create a user that was previously configured, this user is created (A new user in the users table with AuthType = 3). Unfortunately I don't see any folders or reports configured for this user. After login, I see an empty page with reporting services header content in it.

    How do I get all existing users working with all their preconfigured folders and reports?

    Gagan

  61. Gagan Sawant says:

    Hi Cliff – Thanks for this great solution. I am having a small issue though and need your help on it.

    I had configured RS with Windows Auth and had created many users. This setup was working just fine. Then I implemented the SSO with your solution. Now, when I login with the AdminConfiguration, I see everything just fine but my existing users.

    When I try to re-create the users, the system does not recognize the user. It gives the error: "the user or group name 'username' is not recognized. (rsUnknownUserName)".

    Now, when I try to create a user that was previously configured, this user is created (A new user in the users table with AuthType = 3). Unfortunately I don't see any folders or reports configured for this user. After login, I see an empty page with reporting services header content in it.

    How do I get all existing users working with all their preconfigured folders and reports?

    Gagan

  62. @gagan,

    Are you using AD?  If so, why use a forms based solution?  AD can be SSO.

    To answer your question, when you add a user IsValidPrincipalName is called, so maybe there is code there that is causing it to return false?

  63. Gagan Sawant says:

    Cliff – I have a third party forms auth based application. I want to have a link in that application, when clicked logs me into RS using the AuthToken and UserToken passed in the request.

    Not exactly but something like: $.ajax({ url: 'http://rs_server/reports&#39;, headers: { 'AuthToken': '1010101010', 'UserToken': rsuser' } }); We can then redirect the response to a new page.

    About your reply, you are right about the IsValidPrincipalName. That is why I was able to create the users as I am pointing to the Users table of ReportServer DB using my custom LookupUser SP.

    The issue now is that, I gave this new user access to some reports. When I login as this new user, I don't see the reports.

    Please help.

    Gagan

  64. Gagan Sawant says:

    @Cliff – Is it something to do with the authorization?

  65. @Gagan – sounds like the authN is working since you are able to login effectively.  I would set some breakpoints in the authZ CheckAcces() code and ensure the username is being captured correctly and that the logic is working properly.  Sorry I am unable to point you in the right direction.

  66. Gagan Sawant says:

    Thanks for the lead Cliff. I did put some breakpoints in the AuthZ class and figured that for non admin users the permissions collection is always zero.After AceCollection acl = DeserializeAcl(secDesc); code is executed the acl collection always has a count of 0 when the user is not an admin.

    Hope this gives you a lead in providing a solution.

  67. Gagan Sawant says:

    @Cliff – Is it that the permissions are not being given when the forms user is getting created? If so, where are these permissions being read from?

  68. Gagan Sawant says:

    @Cliff – One more thing I forgot to mention.. the SID in the user table is null. Could this issue be because of that?

  69. @Gagan – It sounds like you have given users permissions to browse reports, have you also made them system users?  There are two permissions that should be granted in SSRS for users to gain permission to access reports.

    msdn.microsoft.com/…/ms156034.aspx

  70. Gagan Sawant says:

    Hi Cliff – I had given permissions to the folder but not to the report. I was expecting to see the folder as I had given the required permissions. I am surprised as I don't see the menu as well. (New Folder, New Data Source, Upload File, Report Builder).

    I would like to send you some screenshots of what I see. I guess that will help to get to the solution quicker

  71. @Gagan,  There are two places to setup a user to have permissions.  One is as a System User or System Admin, the other is to a folder or report item.  Have you done both of these?

  72. Gagan Sawant says:

    @Cliff – Yes, I have.

  73. Gagan Sawant says:

    @Cliff – I figured why those pages were not showing. I had not given the correct permissions to the HOME page. All other permissions were setup successfully.

    Thanks a lot for all the help.

    Gagan

  74. @Gagan – great to hear!!

  75. Gourav Verma says:

    i could not find what steps will ask me user name and password in this example, means which page , as UILogOn.aspx , doesnt have user name and password controls.

  76. Gourav Verma says:

    in other words , i wanted to understand the steps ,

    1. how it will ask me login credentials.

    thanks in advance , and article is very simple to understand , i am new to SSRS Admin .

  77. Its SSO, so the authN is handled by another service (ADFS, SiteMinder, LDAP, etc.).  SSRS simply redirects to the authentication service if the right tokens are not provided.

  78. Gourav Verma says:

    Please let me know if my below understandings are correct.

    1. ok, so what i understand is , the token needs to be passed from login App, and if SSRS doesnt find the token, it will be redirected to login page agian.
    2. the token passed from login service(Web App in my case) , will be evaluated every time user comes back to Report Manager.

    3. even user copy pastes the URL of report manager in browser , it will still be checking token passed ….?

    this point is important for us , as we dont know , how to prevent user skipping login app, and restrict him copy pasting Report Manager  URL in browser.

  79. @gourav – yes, although what is more accurately happening is this …

    SSRS relies on ASP.NET for the UI layer.  If ASP.NET doesn't see an authentication cookie it redirects to the logon page referenced in the web.config.  In this case UILogon.aspx.  The UILogon page checks to see if a token is present and validates that token.  Once validated it sets the authentication cookie, which then ASP.NET references to check if request is being made by an authenticated user.  On subsequent requests, if the cookie is there the user is allowed in, we don't check the token after the cookie is created, because the cookie let's ASP.NET/SSRS know that the user is valid and has been authenticated.  If the cookie isn't there, the user gets redirected to UILogon, regardless of what page in report manager you try to access.

  80. Gourav Verma says:

    The below code , checks the token in Users table , where the script of below table , and where the connection string of the same.

    IsValidUserToken(string userToken) {

            //assume not valid
    
            bool isUserValid = false;
    
            if ( userToken != null &amp;&amp; userToken.Length &gt; 0 ) {
    
                string sql = &quot;SELECT username, [role] FROM [dbo].[USERS] WHERE username = @username AND [role]=&#39;Reports&#39;&quot;;
    
                //cre
    
  81. connection string is in the config file.  I don't know that I created a table script.  Remember, this is just an example for a specific scenario.  It does not mean that you should implement it this way given your requirements.  You may have different requirements that lead to a different means of implementing SSO.  You may not need a table, you may trust a response from your authN system or be able to validate through that system with a web call rather than checking a database.

Comments are closed.

Skip to main content