Introduction

This article has been contributed by Daniel Klionsky and based on the work we have done within our team allowing single sign-on integration of SQL Server Reporting Services within existing application.

I want to mention and praise great help provided by Carlos Sereno who was able to clarify many questions for us.

Please enjoy the reading and let us know if it did indeed work for you as well.

SSRS

SQL Server Reporting Services provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization, as well as programming features that enable you to extend and customize your reporting functionality.

Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. Reporting Services includes a complete set of tools for you to create, manage, and deliver reports, and APIs that enable developers to integrate or extend data and report processing in custom applications.

Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.

SSRS Security Models

To effectively secure a Reporting Services installation, you must evaluate your security needs from end-to-end, taking into account the environment in which the server is deployed, the types of reports you are hosting, user access requirements, and distribution.

Reporting Services provides an authentication subsystem and a role-based authorization model that determines access to the report server and to items that are managed by the report server. Authentication is based on Windows Authentication or a custom authentication module that you provide. Authorization is based on roles that you assign to users or groups in your organization.

Reporting Services handles all authentication functions for HTTP requests through either the Windows Authentication extension that is installed with the server or a custom authentication extension that you deploy.

By default, Reporting Services uses a Windows-based security extension to authenticate the identities of users on the system. You have to replace the default security to accommodate custom security in your enterprise.

In addition, there is no native support for single sign-on technologies (SSO) in Reporting Services. If you want to use a single sign-on technology, you must create a custom authentication extension.

Custom Security Extension

Reporting Services provides architecture that allows you to plug in custom or forms-based authentication modules. You might consider implementing a custom authentication extension if deployment requirements do not include Windows integrated security.

The most common scenario for using custom authentication is to support Internet or extranet access to a Web application. Replacing the default Windows Authentication extension with a custom authentication extension gives you more control over how external users are granted access to the report server.

In practice, deploying a custom authentication extension requires multiple steps that include copying assemblies and application files, modifying configuration files, and testing.

Per Microsoft, creating a custom authentication extension requires custom code and expertise in ASP.NET security. For more information about custom authentication architecture, see Implementing a Security Extension.

Custom Security Extension on SSRS 2008 R2

For this article, the system configuration consisted of SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64) Developer Edition installed on Windows 7.

A sample of Custom Security Extension, is readily available on codeplex.com [2] .

Unfortunately, the above code sample seems to work for the versions up to 2008 and not for sql 2008 r2. Several install attempts ended up with the same error:

‘Service Unavailable’ – HTTP Error 503. The service is unavailable.

Posting the message in the SQL Server Reporting Services Forum, Thread Title `Service Unavailable` Error while installing Custom Security Ext on sql 2008 r2 did not help to resolve the issue, as moderator informed that there were no official sample for sql 2008 R2. It was very discouraging since sql 2008 r2 contains some very handy features such as Report Writer 3.0 and going back to SQL Server 2008 was very undesirable.

While checking out for other reporting solutions on the market, Carlos Sereno posted a message forum indicating that, after all, it was possible to have Custom Security Extension on ssrs 2008 R2 and Carlos has already done that. So after plugging in his tips and after several attempts, it finally started to work.

Steps

  1. Compiling the sample project in Visual Studio 2010
    • The codeplex example is using ReportService2005 endpoint. In 2008 R2, however, the Report Server Web service provides a new ReportService2010 endpoint which includes functionality of the ReportService2005 endpoint. You will need to use ReportService2010 endpoint in your .net project. You can add a reference to 2010.asmx either by adding ReportingServices2010.cs, a proxy class provided here [5] or by adding a webService reference to http:///reportserver/reportexecution2005.asmx?wsdl (though a resulting file will be larger). Instructions can be found at [6]
    • You will need to remove ReportingServices2005.cs that is included with Custom Security Extension project by default.
      With the use of ReportService2010.asmx, in your .net project, AuthenticationUtilities.cs module don’t forget to replace

      private const string rsAsmx = @"/ReportService2005.asmx";
       with
       private const string rsAsmx = @"/ReportService2010.asmx";
      
  2. After the sample is compiled, copy the DLLs and the ASPX pages to the appropriate subdirectories for your Report Server installation.
    • Copy Microsoft.Samples.ReportingServices.CustomSecurity.dll and Microsoft.Samples.ReportingServices.CustomSecurity.pdb to the \ReportServer\bin directory.
    • Copy Microsoft.Samples.ReportingServices.CustomSecurity.dll and Microsoft.Samples.ReportingServices.CustomSecurity.pdb to the \ReportManager\bin directory.
    • Copy the Logon.aspx page to the \ReportServer directory.
    • Copy the UILogon.aspx page to the \ReportManager\Pages directory.

Modify the RSReportServer.config file

  1. Open the RSReportServer.config file with Visual Studio 2010 or a simple text editor such as Notepad. RSReportServer.config is located in the \ReportServer directory.
  2. Locate the <AuthenticationTypes> element and modify the settings as follows:
    <Authentication>
      <AuthenticationTypes><Custom/></AuthenticationTypes>
      <EnableAuthPersistence>true</EnableAuthPersistence>
      <RSWindowsExtendedProtectionLevel>Off</RSWindowsExtendedProtectionLevel>
      <RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>
    </Authentication>

Both RSWindowsExtendedProtectionLevel and RSWindowsExtendedProtectionScenario are required entries for SSRS 2008 R2 [3].

  1. Locate the <Security> and <Authentication> elements, within the <Extensions>, and modify the settings as follows:
    <Security>
      <Extension Name="Forms"
    Type="Microsoft.Samples.ReportingServices.CustomSecurity.Authorization,
    Microsoft.Samples.ReportingServices.CustomSecurity" >
    <Configuration>
    <AdminConfiguration>
    <UserName>username</UserName>
          </AdminConfiguration>
        </Configuration>
      </Extension>
    </Security>
    <Authentication>
      <Extension Name="Forms"
    Type="Microsoft.Samples.ReportingServices.CustomSecurity.AuthenticationExtension,
    Microsoft.Samples.ReportingServices.CustomSecurity" />
    </Authentication>
    
  2. Locate the <UI> element and update it as follows:
    <UI>
    <CustomAuthenticationUI>
    <loginUrl>/Pages/UILogon.aspx</loginUrl>
    <UseSSL>True</UseSSL>
    </CustomAuthenticationUI>
    <ReportServerUrl>http://<server>/ReportServer</ReportServerUrl>
    </UI>
    

Modify the RSSrvPolicy.config file

  1. Open the RSSrvPolicy.config file located in the \ReportServer directory.
  2. Add the following element after the existing code group in the security policy file that has a URL membership of $CodeGen as indicated below and then add an entry as follows to RSSrvPolicy.config:
    <CodeGroup class="UnionCodeGroup" version="1" Name="SecurityExtensionCodeGroup" Description="Code group for the sample security extension" PermissionSetName="FullTrust">
    <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.Samples.ReportingServices.CustomSecurity.dll"
    />
    </CodeGroup>
    

Modify the Web.config file for Report Server

  1. Open the Web.config file in a text editor. By default, the file is located in the <install>\ReportServer directory.
  2. Locate the <identity> element and set the Impersonate attribute to false.
    <identity impersonate="false" />
  3. Locate the <authentication> element and change the Mode attribute to Forms.
  4. Add the following <forms> element as a child of the <authentication> element and set the loginUrl, name, timeout, and path attributes as follows:
    <authentication mode="Forms">
    <forms loginUrl="logon.aspx" name="sqlAuthCookie" timeout="60" path="/"></forms>
    </authentication>
    
  5. Add the following <authorization> element directly after the <authentication> element.
    <authorization>
    <deny users="?" />
    </authorization>

    This will deny unauthenticated users the right to access the report server.
    The previously established loginUrl attribute of the <authentication> element will redirect unauthenticated requests to the Logon.aspx page.

Modify the Web.config file for Report Manager

  1. Open the Web.config for Report Manager. It is located in the <install>\ReportManager directory.
  2. Disable impersonation by locating the section <identity impersonate= “true” /> and changing it to the following <identity impersonate=”false” />
  3. Add the following keys to the <appSettings> element.
    <add key="ReportServer" value="<Server Name>"/>
    <add key="ReportServerInstance" value="<Instance Name>"/>
    

    Change the <Server Name> value to the name of the report server and the <Instance Name> value to the name of the instance the report server is associated with.

Creating the UserAccounts Database

The sample includes a database script, Createuserstore.sql, that enables you to set up a user store for the Forms sample in a SQL Server database.

To test the sample

  1. Restart the Reporting Services service by running the following commands at the command prompt:
    net stop "SQL Server Reporting Services ()"
     net start "SQL Server Reporting Services ()"
    
  2. Open Report Manager. You can do this from the Reporting Services program menu or by accessing the Reports virtual directory from your browser.
  3. Enter a user name and password and click Register User to add the user to the accounts database.
  4. Open the RSReportServer.config file. Locate the <Security> element and add the previously registered user name as follows:
    <Security>
    <Extension Name="Forms" Type="Microsoft.Samples.ReportingServices.CustomSecurity.Authorization, Microsoft.Samples.ReportingServices.CustomSecurity" >
    <Configuration>
    <AdminConfiguration>
    <UserName>username</UserName>
    </AdminConfiguration>
    </Configuration>
    </Extension>
    </Security>
    
  5. Return to the UILogon.aspx page, re-enter the user name and password, and then click Logon. You should have access to Report Manager and the report server with no restrictions. The administrator user that you create has equivalent permissions on the report server to those of a built-in administrator account on the local computer. For the purpose of this sample, you can only have one user designated as an administrator. After you have a built-in administrator account, you can register additional users and assign them roles on the report server.

Links

[2]. Readme_Security Extension Sample
[3]. Required configuration for Extended Protection in Reporting Services
Extended Protection for Authentication with Reporting Services
How to: Configure Windows Authentication in Reporting Services
[4]. Service Unavailable` Error while installing Custom Security Ext on sql 2008 r2
[5]. Create a Web Service Proxy for ReportingService 2010
[6]. Creating the Web Service Proxy

Further reading

Enjoy.


26 Comments

BCH · Dec 4, 2011 at 16:15

This is awesome!
Can you help me to get this working on an “Anonymous” SP2010 site, I mean an internet site.
I need to deploy the SSRS on the Public facing internet site.
Your help would be highly appreciated.

Regards,
BCH

    Serguei Dosyukov · Dec 5, 2011 at 12:23

    As there could be many variables in play, you need to present a use case

      BCH · Dec 8, 2011 at 11:22

      Serguei,
      I am trying to setup a SP 2010 web site as a Public facing site. This means that I have to enable “Anonymous” access to this web app, so internet users can visit the pages without the need to sign-in.
      Next, I need to publish my SSRS 2008 R2 reports on to this site for all to view (pl note that these reports will be view-only with some report-parameters selection).
      I am new to SSRS, but looking at your post above I understand that we need to implement this security extension. But as your scenario relates to a FBA site, I needed some help to do this same for Anonymous sites.
      Any help would be appreciated.
      Thanks,
      BCH

        BCH · Dec 8, 2011 at 13:34

        Sorry, my earlier reply was intended for Dánechka.
        But anyone there…. please let me know if someone has done this for anonymous users.

        BCH

rharper · Dec 7, 2011 at 07:52

great compilation of resources here…like BCH i am in the process of trying to set up a reporting environment for a client and i can present a use case, and what i am trying to accomplish if needed…

long story short: i am trying to implement the forms authentication extension and have a question: have you successfully been able to get a forms authentication setup going, and if so, did you simply follow the instructions you outline here?

also, i am wondering, when you compiled the c# example and copied the files over to the report server directories, did you compile that directly on the server, or did you do it on a development machine and then move the files over to the report server?

i am missing something. i can get the user database to accept a user when i register them, but then it will not log in properly, and i am getting a report server url invalid parameter error.

anyway, thanks again for a great page on this subject.

    dklionsky · Dec 7, 2011 at 09:34

    pls see my reply below

dklionsky · Dec 7, 2011 at 09:31

rharper
we compiled on the same machine… review the server names entries in your config files as described above.
Can you please post the error message you are getting?

Cheers

    rharper · Dec 7, 2011 at 11:58

    thanks for your reply…i am just getting back on looking at this for today, and i am trying desperately to get some better error messages than the one i am getting.

    “An error occurred while attempting to get the ReportServer Url. Invalid Parameter”

    i’m starting to get to the point where i’m forgetting what i have tried and haven’t tried…what i’ve changed and haven’t changed…if you know that feeling and i’m sure we all do.

    i am going to try and compile the dll’s again and get the pages in there.

    another thing i do not get is the webservice in the example project – does that have to be changed at all, or stay at the “localhost” as it is when you download it?

    i will get back as soon as i can with hopefully some better info. thanks again!

      rharper · Dec 7, 2011 at 12:02

      i should point out:

      i am getting that error on the:

      Reports/Pages/UILogon.aspx page after i try and log in.

      also, there is no redirect if i simply go to the Reports directory directly even though there is a login page setting in the config file.

      i am running this on mssql 2008r2 sp1 cumulative update 3 applied, windows 2008 server…forgot to mention all that before.

        Dánechka · Dec 7, 2011 at 14:04

        An error message from the log may be more informative.
        Can you please post it ? SSRS Error log can be found here :

        C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\Logfiles\ReportServerService__xxxx.log

        Also, other than changes described above, we did not have to make any other modifications to the provided c sharp example project.

        Regards,

          rharper · Dec 8, 2011 at 12:30

          i finally got an error to show up more than what was appearing.

          the report manager UILogon.aspx page says:

          Client found response content type of ”, but expected ‘text/xml’. The request failed with an empty response

          the logfile from Reporting Services includes:

          1:: i INFO: Appdomain:4 ReportServer_MSSQLSERVER_0-2-129678495212866439 started.
          appdomainmanager!DefaultDomain!7ec!12/08/2011-15:25:22:: e ERROR: AppDomain ReportServer_MSSQLSERVER_0 failed to start. Error: Request for the permission of type ‘System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.
          library!DefaultDomain!7ec!12/08/2011-15:25:22:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: Failed to create HTTP Runtime, Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: An internal or system error occurred in the HTTP Runtime object for application domain ReportServer_MSSQLSERVER_0. —> System.Web.HttpException: Request for the permission of type ‘System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed. —> System.Web.HttpParseException: Request for the permission of type ‘System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed. —> System.Security.SecurityException: Request for the permission of type ‘System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.
          at System.Reflection.Assembly._GetType(String name, Boolean throwOnError, Boolean ignoreCase)
          at System.Web.UI.Util.GetTypeFromAssemblies(ICollection assemblies, String typeName, Boolean ignoreCase)
          at System.Web.UI.TemplateParser.GetType(String typeName, Boolean ignoreCase, Boolean throwOnError)
          at System.Web.UI.TemplateParser.ProcessInheritsAttribute(String baseTypeName, String codeFileBaseTypeName, String src, Assembly assembly)
          at System.Web.UI.TemplateParser.PostProcessMainDirectiveAttributes(IDictionary parseData)
          The action that failed was:
          InheritanceDemand
          The type of the first permission that failed was:
          System.Web.AspNetHostingPermission
          The first permission that failed was:

          The demand was for:

          The granted set of the failing assembly was:

          The assembly or AppDomain that failed was:
          ReportingServicesWebServer, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
          The Zone of the assembly that failed was:
          MyComputer
          The Url of the assembly that failed was:
          file:///C:/Program Files/Microsoft SQL Server/MSRS10_50.MSSQLSERVER/Reporting Services/ReportServer/bin/ReportingServicesWebServer.DLL

Dánechka · Dec 9, 2011 at 13:07

`Client found response content type of ”, but expected ‘text/xml’ –> is a general error that can point to different things.
In my case it pointed to RSSrvPolicy.config file, where several typo-s were found ( Groups ). I retyped the settings from the start; rebooted SSRS and it started to work. .. so I recommend to verify the entries in this file.

    rharper · Dec 12, 2011 at 16:04

    thanks for the info. i really am trying everything on this to get it working. the application event log also shows an asp.net 2.0 error with the error being “Request for the permission of type ‘System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089? failed.” so the failure in reporting services log is also showing up as asp.net in the computer’s main event log. this is confusing to me because since 2008r2 reporting services does not use IIS, and all my experience with working with asp.net involved IIS, i have no idea where to look for the fix for this failure.

    i am right now reinstalling mssql, so i am going to give it another shot from the beginning and see where i end up.

    many thanks for your attention to my posts…

    rharper

rharper · Dec 13, 2011 at 18:02

i finally got it working. this was a typical microsoft implementation project – third…or fourth time is the charm!

there is a step i missed one or two of the install attempts:

make sure you edit the rsmgrpolicy.config file in the following way…

change:

PermissionSetName=”Execute”

to

PermissionSetName=”FullTrust”

on the codegroup for the MyComputer zone.

also of note, the codeplex download of the sample authentication project for x64 2008r2 now contains the correct reference to ReportService2010.asmx and there should be no need to adjust that. however, to be sure, do a search on the entire project for ReportService2005.asmx and make sure nothing shows up. you should have one reference to ReportService2010.asmx in the AuthenticationUtilities.cs file on or about line 42.

so now that i finally have a windows authentication version and a forms authentication version, i am reading that ReportBuilder will only work with windows authentication only. do you also know that to be true, or am i mistaken on that.

again, many thanks, and i appreciate the opportunity to be able to post on your blog.

rharper.

AJ · Feb 24, 2012 at 14:35

I have a question about step 5 in “to test the sample”. I have administrator user created and entered in rsreportserver. That works great, but all other users that are not entered into rsreportserver.config getting error “User ” does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”
What would be the issue?

    Dánechka · Feb 27, 2012 at 09:28

    Did you add your users to the UserAccounts Database ?

    DK · Jan 19, 2013 at 07:56

    Hi Al

    I experienced the same issue as you. I was using the debug compiled version of the DLL’s. Turns out I missed the Read me message about the project when compiling debug version of DLL, you need to modify AuthenticationExtension.cs to comment out some code. Otherwise compile the release version of the DLL’s, which already have this done. I used the release version and this resolved my issue. Although I now have a new error to deal with, I got a step further. Good Luck

    Regards DK

some one · May 2, 2012 at 01:04

the section EnableAuthPersistence is not correct below value true is repeated. one of them should be removed

KL · Aug 17, 2012 at 07:31

When accessing the report server path, I get this error message.

The report server encountered an unhandled exception in HttpApplication. (rsUnhandledHttpApplicationError) Get Online Help
File does not exist.

I have no problem accessing the report manager.

Help please.

Thank you.

    DK · Jan 9, 2013 at 12:04

    Hi KL.

    I had the same problem you describe. I updated SQL to cumlative update 3 and this issue went away, however I now have a new problem. accessing reports url gives the below error.

    User ” does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
    report server url gives the following

    The permissions granted to user ‘user@microsoft.com’ are insufficient for performing this operation. (rsAccessDenied) Get Online Help

    good luck

      Cem Uney · Mar 12, 2013 at 08:40

      did you find any solution for rsAccessDenied error.
      thanks

        DK · Mar 15, 2013 at 03:11

        Hi Cem

        The main challenges are getting the configuration in the .config files right. Once its correct, the access denied and vairous other errors go away. I did get this working but was not what I was expecting. If it helps I can send you a working copy of my config files for comparrision.

        See my comments on the 19th of JAN 2013.

Tony Aguanno · Sep 10, 2012 at 07:53

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 – I want to access it so I can 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 user name and password?
Your assistance is greatly appreciated. Please don’t hesitate to ask if you would like additional information. Thank you

DK · Jan 19, 2013 at 14:15

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?

Leave a Reply