Custom Assemblies in SQL Server 2005 Reporting Services

Introduction

In SQL Server reporting services you can use custom assemblies, which gives your report a feature to add more functionality using any language of your choice. This can be done by referring the custom assembly in the Report properties-> References window in the Report Designer. This article focuses on the problems you might face and the probable solution when using .NET custom assemblies. This article also gives in-depth details of modifying .NET assemblies for which you do not have the source code.

How Report Server and Report Designer work with custom assemblies

Let us first understand how Report Server and Report Designer call functions in custom assemblies.

Report Server invokes functions in custom assembly when the report is run after deploying it on server. The CLR uses the same technique to load an external assembly when you are working with Report Server as it uses for a .NET application. The custom assembly used in the report has to be placed in "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin" location in order to be loaded by CLR successfully. Whereas for the Report designer, you need to place the assembly in "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies" location, which is the local directory for Report Designer IDE.

The SQL Server reporting services uses Code Access Security in order to restrict the custom code to use server resources. The security model used by Report Designer and Report Server to run the report are different. Report Designer runs the custom assemblies with full trust security. So, you may not encounter security related issues when you are previewing the reports. In Report Server by default, custom code is run with a limited Execution permission as specified in the rssrvpolicy.config file. The custom code will be called by the Report Expression Host Assembly, which runs with a limited Execution permission. The Execution permission set enables code to run, but does not use protected resources. Any code that is called from this assembly also runs with Execution permission even if it is granted FullTrust. This is because CLR verifies the security for all the assemblies in the call stack hierarchy. If any one of the assembly is granted less permission, the call is terminated with a security permission exception. (However, you can override this by using the Security.Assert feature discussed in the subsequent sections). In some scenarios, you may need to implement appropriate security calls in custom assemblies to protect the resources.

Note: Report Server uses the security permission set from rssrvpolicy.config file settings for applying Code Access Security; it does not use .NET security CONFIG files that are set using .NET Framework Configuration MMC Snap-in.

Types of custom assemblies

Let us explore the different custom assemblies we use in our reports, which pose different problems in different scenarios. A couple of scenarios are discussed below:

• You can write your own .NET assembly and use it in the report. (If you are using third party .NET components with source code, then it falls into this category).
• You can use some .NET Interop Assembly of a third party COM component. (If you are using third party .NET components and do not have source code, then it falls into this category).

Using your own .NET assembly

You can write a custom assembly with source code and use it in your Report for availing some language specific features. The .NET assemblies can be private or it can be signed to use it from GAC (.NET Global Assembly Cache). For a .NET assembly to be used in reports, it need not be signed. However, if you are using Signed assemblies you need to mark the custom assembly with AllowPartiallyTrustedCallersAttribute. This is required because custom assemblies are called from a report expression that is part of the Report Expression Host Assembly which is not granted FullTrust; is a "partially trusted" caller. You can modify the policy configuration files to grant your assembly specific permissions. In order for your code to acquire the appropriate permission, you must assert the permission within your custom assembly code. For example, assume that a method is accessing a specific resource in the system; you can mark the method with the following security attribute:

• [System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert, Unrestricted = true)] or you can specify the permission you need. (This can be accomplished by writing security related code in the required methods of the assembly.)

The assembly needs to be placed in the folders, which is specified in the earlier sections in order for Report Server and Report Designer to load the assembly successfully. You also need to create a code group in rssrvpolicy.config file for the above created custom assembly. You can do this by copying and pasting the following XML into the CONFIG file in the appropriate location.

<CodeGroup
    class="UnionCodeGroup"
    version="1"
    PermissionSetName="FullTrust"
    Name="MyCustomAssembly"
    Description=
      "This group grants full permission for my custom assemblies">
    <IMembershipCondition
        class="StrongNameMembershipCondition"
        version="1.0.0.0"
        PublicKeyBlob="Your public key blob"/>
</CodeGroup>

For unsigned assemblies, you need to create the code group with URL membership XML and add it to rssrvpolicy.config file as given below:

<CodeGroup
    class="UnionCodeGroup"
    version="1"
    PermissionSetName="FullTrust"
    Name="MyCustomAssembly"
    Description=
       "This group grants full permission for my custom assemblies ">
    <IMembershipCondition
        class="UrlMembershipCondition"
        version="1.0.0.0"
        URL="Your custom assmbly path"/>
</CodeGroup>

Using .NET interop assembly from third-party COM components

You can also use .NET interop assemblies in your reports to utilize the features of third party COM components. This scenario is very tricky and you probably have very little knowledge on how the methods are implemented internally in order to grant specific permission set for the assembly. So, any resource access from this assembly needs the Report Expression Host Assembly to be granted FullTrust permission in rssrvpolicy.config file, which could be risky in internet report applications. Any malicious code run using Expression Host on the Report Server with FullTrust,can get your Report Server down.

So, one way to overcome this problem is to sign this interop and add AllowPartiallyTrustedCallers attribute to the assembly and grant the specific methods appropriate security permissions. This is a very tedious task as you may not have the source code for the component to apply required attributes / code. Interestingly, you can do this by manipulating the MSIL of the required assembly. This requires the knowledge of .NET tools and technology. If you are interested in doing this follow the steps given below:

Step 1: Create the .NET interop assembly for your COM, you need to specify the strong name key file (you can use sn.exe .NET tool to create key file) also in order to get the signed assembly.

Step 2: Get the IL code of the assembly by using ILDASM.exe .NET tool. (You need to open the assembly using this tool and use the Dump feature to get the MSIL.)

Step 3: Add AllowPartiallyTrustedCallers attribute to the MSIL code. This can be done by copying the following line of MSIL code within the .assembly YourAssembly section.

[Editor Note: Line breaks used to avoid scrolling]

.custom instance void 
   [mscorlib]System.Security.AllowPartiallyTrustedCallersAttribute::.ctor() = 
                                                                ( 01 00 00 00 )

Step 4: Add the assert security attributes to the methods you need to call for accessing resources. You can do this by copying the following MSIL code in each method you want to access:

.permissionset assert = 
    (3C 00 50 00 65 00 72 00 6D 00 69 00 73 00 73 00 // <.P.e.r.m.i.s.s.
    69 00 6F 00 6E 00 53 00 65 00 74 00 20 00 63 00 // i.o.n.S.e.t. .c.
    6C 00 61 00 73 00 73 00 3D 00 22 00 53 00 79 00 // l.a.s.s.=.".S.y.
    73 00 74 00 65 00 6D 00 2E 00 53 00 65 00 63 00 // s.t.e.m...S.e.c.
    75 00 72 00 69 00 74 00 79 00 2E 00 50 00 65 00 // u.r.i.t.y...P.e.
    72 00 6D 00 69 00 73 00 73 00 69 00 6F 00 6E 00 // r.m.i.s.s.i.o.n.
    53 00 65 00 74 00 22 00 0D 00 0A 00 20 00 20 00 // S.e.t."..... . .
    20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 // . . . . . . . .
    20 00 20 00 20 00 20 00 20 00 76 00 65 00 72 00 // . . . . .v.e.r.
    73 00 69 00 6F 00 6E 00 3D 00 22 00 31 00 22 00 // s.i.o.n.=.".1.".
    0D 00 0A 00 20 00 20 00 20 00 20 00 20 00 20 00 // .... . . . . . .
    20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 // . . . . . . . .
    20 00 55 00 6E 00 72 00 65 00 73 00 74 00 72 00 // .U.n.r.e.s.t.r.
    69 00 63 00 74 00 65 00 64 00 3D 00 22 00 74 00 // i.c.t.e.d.=.".t.
    72 00 75 00 65 00 22 00 2F 00 3E 00 0D 00 0A 00 ) // r.u.e."./.>.....

Step 5: Compile the MSIL code using ilasm.exe .NET tool. You can do so by using command ilasm /dll yourilcodefile.il.

Step 6: Once you get the DLL you need to resign the assembly to recalculate the hash. You can do so by using sn.exe .NET tool by specifying –R switch with the DLL and Key file.

Step 7: Install the assembly in the .NET Global assembly cache. You can do this by dragging the assembly and dropping it into C:\Windows\Assembly folder. You also need to copy the files onto the folders I have specified in the beginning of this article in order to make it work in Report Server and Report Designer.

Step 8: You also need to create a code group in rssrvpolicy.config file for the above created file. You can do this by copying and pasting the following XML into the config file in the appropriate location.

<CodeGroup
    class="UnionCodeGroup"
    version="1"
    PermissionSetName="FullTrust"
    Name="MyCustomAssembly"
    Description=
      "This group grants full permission for my custom assmeblies ">
    <IMembershipCondition
        class="StrongNameMembershipCondition"
        version="1.0.0.0"
        PublicKeyBlob="Your public key blob"/>
</CodeGroup>

Note: Be careful when you are working with XML configuration files. Make sure that it is well formed by opening it in Internet Explorer. Now you are ready to go and run your reports in SQL Server Reporting Services web with legitimate server resource usage.

This MSIL modification technique can also be used for the .NET assemblies for which you do not have source code and any other .NET requirement that needs assembly manipulation. As I have mentioned earlier, this needs very good knowledge of MSIL and .NET tools.

Summary

This article provides detailed knowledge of how the Report Server and Report Designers work internally in order to probe into the security related problems. It also provides how .NET assemblies can be modified without the source code in order to add some special attributes, which helps while using custom assemblies in reporting services and also in any .NET application development scenario which needs assembly modification.