Implementing Custom Security Extensions for SQL Server 2008 R2

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.

This entry was posted in Fun stuff with SQL Server, Technology and tagged , , , , , , , , , , , , , . Bookmark the permalink.

26 Responses to Implementing Custom Security Extensions for SQL Server 2008 R2

  1. Please help me I have deployed SSRS custom security extension however when someone tries to run a report we got the next error:

    An error has occurred during report processing. (rsProcessingAborted)
    An error occurred when invoking the authorization extension. (rsAuthorizationExtensionError)
    For more information about this error navigate to the report server on the local server machine, or enable remote errors

    Log:

    processing!ReportServer_0-1!1634!02/12/2013-15:21:10:: i INFO: DataPrefetch abort handler called for Report with ID=. Aborting data sources …
    processing!ReportServer_0-1!708!02/12/2013-15:21:10:: e ERROR: An exception has occurred in data set ‘MIMMetrics’. Details: Microsoft.ReportingServices.Diagnostics.Utilities.AuthorizationExtensionException: An error occurred when invoking the authorization extension. —> System.NullReferenceException: Object reference not set to an instance of an object.
    processing!ReportServer_0-1!708!02/12/2013-15:21:10:: i INFO: Some other thread has already aborted processing.
    processing!ReportServer_0-1!1634!02/12/2013-15:21:10:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: , Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.Diagnostics.Utilities.AuthorizationExtensionException: An error occurred when invoking the authorization extension. —> System.NullReferenceException: Object reference not set to an instance of an object.
    — End of inner exception stack trace —;

Leave a Reply