Chapter 23. How to Create and Call a Custom Assembly from a Report

SSRS comes with a comprehensive set of functions that can be used within reports. However, you might need to add custom functionality that is not covered by the set of common functions or is too complicated for embedded code to handle efficiently. Second, if you, as a developer, are hard set on C# as a language of choice, a custom assembly is the way to go. A couple of examples of functionality that are better handled by a custom assembly are encryption and trend analysis.

Let’s start with a simple example and develop a function GetLibraryInfo(), which returns a single string with a library version information.

Start Visual Studio 2005 and create a new Class Library Project. Let’s call the project RSCustomLibrary; see Figure 23.1.

Figure 23.1. New library project.

Visual Studio creates a project with a single class Class1. Let’s rename the file Class1.cs in Solution Explorer to MainClass.cs. Note how Visual Studio changed the name of the class in the code.

Substitute code in the class module with the following code:

using System;
using System.Reflection; //Need to have this so we can get the Assembly information
namespace RSCustomLibrary
{
    public class MainClass
    {
        //Method GetLibraryInfo() returns this custom Assembly information
       //RSCustomLibrary, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
       public static string GetLibraryInfo()
       {
            return Assembly.GetExecutingAssembly().GetName().ToString();
       }

     }
}

The compiled assembly must be located in directories in which it is accessible by

• Report Designer (the default directory is C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies). This allows calling an assembly from reports in preview mode.

• Report Server (the default directory is C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerin). This allows calling assembly from reports deployed on Report Server.

Let’s now use our custom assembly in a report. You first have to reference the assembly:

1. Open the report properties using the Report, Report Properties menu.

2. In the properties dialog box, click the References tab.

3. Click “...” and navigate to the library. See Figure 23.2 for details.

Figure 23.2. Reference custom assembly.

Developers can navigate to any location where the library is present, such as the bin directory of the library project. This operation only records the reference to the assembly and not a specific location of this assembly. Report Designer adds the following RDL to reference an assembly:

<CodeModules>
       <CodeModule>RSCustomLibrary, Version=1.0.0.0,
              Culture=neutral, PublicKeyToken=null
       </CodeModule>
</CodeModules>

4. Enter a Class Name and an Instance Name. Filling in a Class Name and an Instance Name is optional for static methods. When you specify a Class Name and an Instance Name, Report Designer creates an instance of the specified class and, subsequently, you can access the class inside of a report using the class’ instance name. Report Designer adds the following RDL:

<Classes>
     <Class>
          <ClassName>RsCustomLibrary.MainClass</ClassName>
          <InstanceName>myMainClass</InstanceName>
     </Class>
</Classes>

When specifying a Class Name, you need to prefix the name of the class with its assembly, such as RSCustomLibrary.MainClass. Otherwise, the SSRS compiler returns an error: [rsCompilerErrorInClassInstanceDeclaration] Error in class instance declaration for class MainClass: [BC30002] Type ‘MainClass’ is not defined.

5. Call the assembly from one of the report’s expressions. A static method can be called as =<AssemblyName>.<ClassName>.<StaticMethodName>; in this case, =RSCustomLibrary.MainClass.GetLibraryInfo(). An instance method can be called as =<Code>.<InstanceName>.<PublicMethodName>; in this case,=Code.myMainClass.GetLibraryInfo(). A static method does not require an instance, but can still be accessed through the instance if so desired.

Now that you have referenced a custom assembly in a report and copied binaries to the Report Designer’s directory, the assembly can be called from the report in preview mode of the Report Designer. However, calling the assembly will not work if you try to debug or deploy this assembly to a Report Server if no additional steps are taken.

This chapter explains the additional steps needed to make a deployed assembly available to reports.

Sometimes, you might need to pass initialization parameters to a class in a custom assembly. This is done by overriding the OnInit() method of the Code object of a report. This can be done by editing the RDL directly or using the code editor. To open code editor, use the Report, Report Properties menu and click the Code tab in the Report Properties dialog box.

See Figure 23.3 for details.

Figure 23.3. Using the code editor to override OnInit() method.

To initialize a class, you can either create a new instance of the class inside of OnInit and pass parameters to a class constructor or write a public initialization method for a class and call this method from OnInit.

When you create a new instance of the class, make sure that the instance name used in the OnInit method does not conflict with the instance name you have created when you referenced an assembly.

<Code>
       Dim Public myMainClass1 As RSCustomLibrary.MainClass
       Protected Overrides Sub OnInit()
           myMainClass1 = new RSCustomLibrary.MainClass(Report.Parameters!Period.Value)
       End Sub
</Code>

To invoke this initialization method, from a report you can use the following expression: =Code.myMainClass1.GetLibraryInfo()

When you call a public initialization function, create an instance of the class using the Report, Report Properties menu and then click the References tab.

Then call the initialization function from OnInit. Make sure that the instance name used in OnInit corresponds to the instance name used when you referenced an assembly.

<Code>
       Protected Overrides Sub OnInit()
           myMainClass.InitializeClass(Report.Parameters!Period.Value)
       End Sub
</Code>

To invoke this initialization method, you can use the following expression: =Code.myMainClass.GetLibraryInfo()

Within the OnInit method, you can use items from the Globals, Parameters, and User collections. The Fields and ReportItems collections are not available when the OnInit method is invoked.

To take advantage of initialization, you need to add a constructor to the assembly. The updated assembly may have the following code:

using System;
using System.Reflection;
//Need to have this so we can get the Assembly information
namespace RSCustomLibrary
{
      public class MainClass
      {
          static int mPeriod = −1;
          public MainClass()
          {}
          public MainClass(int Period)
          {
              mPeriod = Period;
          }

          public void InitializeClass(int Period)
          {
              mPeriod = Period;
          }
          //Method GetLibraryInfo() returns this custom Assembly information
          //RSCustomLibrary, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
          //AND initialization status:
          //
          public static string GetLibraryInfo()
          {
               return Assembly.GetExecutingAssembly().GetName().ToString()
                   + (
                       (mPeriod != −1) ?
                         "Initialized with value=" + mPeriod.ToString()
                          : " Not initialized"
                      );
          }
      }
}

Note the operator “?” (question mark) usage in the code. If you are not familiar with this operator, it is similar to the IF function or the IF-THEN-ELSE statement.

It is very likely that the first deployed version of an assembly will not be perfect and one day you will need to update an assembly. You can update an assembly using one of four ways:

1. Maintain the same assembly attributes (such as Version and Culture) and replace an assembly in Report Designer and SSRS directories. Maintaining assembly attributes is a key for this method because the report’s RDL contains this information in the <CodeModule> descriptor. If an assembly’s attributes change, the reports can no longer call it. This method is the best for frequent assembly updates, while maintaining classes and method signatures. This method of updates is especially relevant during debugging and testing.

2. Update the assembly attributes and update all the references (using Report Designer or directly editing the <CodeModule> tags) to reference the new version of the assembly.

3. Create a strong-named assembly (see the next section, “Strong-named Custom Assemblies,” for more details) and store it in the Global Assembly Cache (GAC). The GAC allows multiple versions of an assembly. Reports can call any of the versions stored in the GAC. Thus, you can keep both versions of the assembly and refer to either one.

4. As in the previous method, create a strong-named assembly, store a version of an assembly in the GAC, and force SSRS to redirect all the calls to the new assembly. In this case, an administrator would need to modify the Web.config and ReportService.exe configuration files to add the following entry:

     <configuration>
           <runtime>
                <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
                      <dependentAssembly>
                            <assemblyIdentity name="RSCustomLibrary"
                                                 publicKeyToken="..."
                                                 culture="..." />
                            <bindingRedirect oldVersion="1.0.0.0"
                                                newVersion="2.0.0.0"/>
                      </dependentAssembly>
                </assemblyBinding>
          </runtime>
     </configuration>

Strong-Named Custom Assemblies

The .NET Framework allows sharing of assemblies through the GAC. The GAC is a %systemroot%assembly directory on a computer on which .NET Framework is installed. GAC can be managed through %systemroot% Microsoft.NETFrameworkversion GACUTIL.exe or the Assembly Cache Viewer extension of Windows Explorer; see Figure 23.4. Additional details of using this tool can be found at: http://msdn2.microsoft.com/library/34149zk3.aspx.

Figure 23.4. Assembly Cache Viewer.

Assemblies must have a strong name to be stored in GAC, so the .NET runtime can uniquely identify each assembly, even assemblies with the same name. A strong name is the combination of the assembly’s name, the four-part version number, the culture (if provided), a public key, and a digital signature stored in the assembly’s manifest.

Visual Studio 2005 made it very simple to sign an assembly through the Signing tab of the Project Designer. To access the Signing tab, select a project node in Solution Explorer, and then on the Project menu, click Properties. When the Project Designer appears, click the Signing tab.

By default, Reporting Services does not allow calls to strong-named custom assemblies directly from reports. This is probably a good thing because enabling SSRS to call strong -named assembly poses security risks.

To enable calls to strong-named custom assembly in Reporting Services, you can use one of the methods described in Table 23.1. Both methods described in Table 23.1 have security risks associated with them.

Table 23.1. Methods of Enabling a Strong-Named Assembly

Security risks are especially relevant to the strong-named custom assemblies that require more than Execute permissions (discussed in the later section, “Assemblies That Require Other Than Execute Permissions”).

.NET Security Primer for a SSRS Administrator

Although the details of .NET security are outside the scope of this book, a brief security overview will help you to better understand the security of SSRS assemblies. You can find more security-related topics in the Microsoft .NET Framework documentation (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/netframesecover.asp.

By default, custom assemblies are granted Execute permission in Reporting Services. The Execute permission set enables code to run, but not to use protected resources. For example Execute permission allows string manipulations, but not access to the file system.

The worst outcome of a malicious call to the assembly with Execute permission is a potential denial of service attack in which a malicious assembly causes excessive CPU and memory use, thereby impacting performance of other software components running on the computer on which such an assembly is installed.

After an assembly is enabled for additional permissions, the impact of a malicious call could be more dramatic, such as data loss.

.NET common language runtime (CLR) employs code access security, which allows an administrator to assign permissions to an assembly. When an assembly makes a call to a protected resource (for example, file IO), the runtime checks if an assembly has appropriate permissions to do so. During the call, CLR evaluates all the assemblies in the call stack for permissions. This prevents an AssemblyA with restricted permissions (such as Execute) to call an AssemblyB with less restrictions to perform an operation on a protected resource.

An administrator sets the security policy by editing XML configuration files. SSRS has three configuration files: rssrvpolicy.config, and rsmgrpolicy.config, rspreviewpolicy.config (outlined in Table 23.2). Depending on the end goal, one or more files should be edited.

Table 23.2. SSRS Configuration Files

Administrators can edit configuration files manually, using any text editor (possible, but not recommended method) or employ the help of the .NET Framework Configuration Utility (mscorcfg.mcs).

The easiest way to start this utility is from Control Panel, Administrative Tools, .NET Framework 2.0 Configuration. This tool is shown in Figure 23.5.

Figure 23.5. .NET Framework Configuration Utility.

The .NET Framework Configuration Utility edits the computer’s security policy file stored located at (make sure to adjust path for the proper version of.NET) C:WINDOWSMicrosoft.NETFrameworkv2.0.50727configsecurity.config.

Instead of editing SSRS configuration files manually, an administrator can use the .NET Framework Configuration Utility and then copy the <PermissionSet> and <CodeGroup> from the security.config file to an appropriate SSRS configuration file.

There are three levels of security policies:

• Enterprise level is the highest and applies to the entire enterprise.

• Machine policy applies to all code run on that computer.

• User policy applies the currently logged-in user.

A lower level can place further restrictions on the policy settings from the highest level, but cannot expand the permissions. As a result, code is granted the minimum set of permissions granted by any of the levels.

To set permissions for an assembly, follow these steps:

1. Create a permission set or use one of the predefined sets, such as Execution (each assembly in SSRS gets this set by default). Additional permission sets are FullTrust, Internet,None, and more.

2. Create a code group that maps a permission set to an assembly or, more specifically uses Membership Condition to perform this mapping.

Figure 23.6 shows security permission configuration objects (configured as an XML in .config files) for an assembly.

Figure 23.6. Assembly security permission objects.

Each permission set contains zero or more permissions. A permission is a logical subdivision of system functionality (or access control to a protected resource). Examples of permissions are Registry(controls access to the computer’s registry, as a whole or by an individual key), File IO(controls access to a single file or a whole file system), Printing (controls access to printing resources), SQL Client (controls access to SQL Servers), and more.

Each permission supports one or more properties that provide more details for a permission. For example, the File IO permission allows specifying an individual file that can be accessed for either one or all of the following: Read, Write,Append, and Path Discovery. Security is somewhat an obscure permission because its attributes are presented as security permissions.

Some of the permissions that Security allows to specify are Enable Assembly Execution (Execute permission) and Assert Any Permission That Has Been Granted. Assert Any Permission That Has Been Granted in turn enables an assembly to do exactly what the permission implies, that is, to assert security permissions, such as File IO.

A code group uses membership condition to associate an assembly with a permission set. The most commonly used membership conditions are of URL and Strong Name type. URL membership uses a path to an assembly and Strong Name uses exactly what it implies, the strong name of an assembly.

When SSRS calls an assembly, the assembly, in turn, tries to access a protected resource the .NET CLR checks if administrators have set up the appropriate permissions. The CLR then checks the call stack to evaluate permissions for each assembly and makes sure that the minimum combined permission is applied. This prevents a malicious assembly with Execute permission to do anything bad by calling another assembly with FullTrust permission.

Assemblies That Require Other Than Execute Permissions

A developer who wants to grant custom assemblies privileges beyond Execute permission needs to edit one or both configuration files: C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerrssrvpolicy.config and/or C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssembliesRSPreviewPolicy.config.

To have access to system resources, such as File IO, from the assemblies, you need to add the following to one or both rssrvpolicy.config and RSPreviewPolicy.config configuration files (refer to Table 23.2).

Correspondingly, this configuration setting ensures successful execution of the assembly deployed to the Reporting Services and assembly used by Report Designer.

<PermissionSet
       class="NamedPermissionSet"
       version="1"
       Name="MyPermissionSet"
       Description="A custom permission set to grant read access to a configuration  file.">
       <IPermission
             class="FileIOPermission"
             version="1"
             Read="C:configuration.xml"
             Write="C:configuration.xml"
       />
      <IPermission class="SecurityPermission"
             version="1"
             Flags="Execution, Assertion"
      />
</PermissionSet>

The preceding configuration section is a definition of a NamedPermissionSet with the name MyPermissionSet, which is located under the <NamedPermissionSets> tag in the configuration file (rssrvpolicy.config and/or RSPreviewPolicy.config). MyPermissionSet grants the assembly two security permissions:

• Execute—Allows executing code in the assembly. This permission was discussed earlier in this section.

• Assert—Allows asserting other defined permissions in the code. In this case, it allows asserting a FileIOPermission.

MyPermissionSet also grants the assembly a FileIOPermission permission to read and write configuration files. The named permission set is optional and used to create a fine granularity of permissions that are not already defined by built-in permissions.

In addition, the following <CodeGroup> configuration needs to be added to the configuration file (rssrvpolicy.config in this particular case).

   <CodeGroup class="UnionCodeGroup"
         version="1"
         PermissionSetName="MyPermissionSet"
         Name="MyCodeGroup"
         Description="A special code group for my custom assembly.">


   <IMembershipCondition class="UrlMembershipCondition"
         version="1"
         Url="C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerinRSCustomLibrary.dll"/>
</CodeGroup>

Note that CodeGroup refers back to MyPermissionSet. Alternatively, CodeGroup could have used a predefined set, such as FullTrust: PermissionSetName=“FullTrust”.

Note how the Url property of the UrlMembershipCondition condition points to the library that was deployed for use by SSRS. You might have spotted this because the rssrvpolicy.config configuration was edited and the library was deployed to SSRS’ binary directory (C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerin).

For Report Designer, the configuration file would have changed to RSPreviewPolicy.config and the deployment directory (and, thus, the value of the Url property) would be C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssembliesRSCustomLibrary.dll.

The position of CodeGroup is extremely important. It has to be positioned like the following code fragment:

...

   </CodeGroup>
       <CodeGroup class="UnionCodeGroup"
       version="1"
       PermissionSetName="FullTrust"
       Name="MyNewCodeGroup">
        <IMembershipCondition class="UrlMembershipCondition"
              version="1"
              Url="C:Program FilesMicrosoft Visual Studio PrivateAssemblies RSCustomLibrary.dll"/>
           </CodeGroup>
         </CodeGroup>
     </CodeGroup>
</PolicyLevel>

...

This code fragment gives the RSCustomLibrary.dll FullTrust or unrestricted permission. As mentioned previously, because FullTrust is a predefined PermissionSet, it does not require you to specify anything in the <NamedPermissionSets> section of the configuration file.

For a code to acquire the appropriate permission, it must first assert the permission:

FileIOPermission permission = new FileIOPermission(FileIOPermissionAccess.Read |
FileIOPermissionAccess.Write, @"C:configuration.xml");
try
{
      permission.Assert();
      XmlDocument doc = new XmlDocument();
      doc.Load(@"C:configuration.xml");
      ...
}

Alternatively, a method’s attribute can carry an assertion: [FileIOPermissionAttribute(SecurityAction.Assert, ViewAndModify=                                                         @"C: configuration.xml")]

The details of what happens during Assert are outside the scope of this book. You can find a very good explanation of Assert at http://blogs.msdn.com/shawnfa/archive/2004/08/23/219155.aspx.

What happens if you properly set all configurations, but did not do an Assert? In this case, .NET throws a SecurityException, such as the following:

Request for the permission of type
‘System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.

Debugging Custom Assemblies

The best debugging tool for debugging custom assemblies, no surprise, is Visual Studio. There are two debugging options:

• Debug with a single instance of Visual Studio. This is done by creating a single solution that contains both a Reporting Services project and a custom assembly project. You would set breakpoints in a custom assembly and run the report in DebugLocal mode. The easiest way to start DebugLocal is to right-click on the report that needs to be tested and select Run from the shortcut menu.

• Debug with two instances of Visual Studio: one has a report project and another custom assembly. Set breakpoints in the custom assembly, click debug, and from the list of processes select devenv.exe that corresponds to the report project. After you run a report that calls the assembly in preview mode, the debugging session will break at defined breakpoints.

Debugging with a single instance of Visual Studio (DebugLocal method) requires several setup steps:

1. Include both the report project and a custom assembly project in a single solution.

2. Set breakpoints in a custom assembly.

3. Set up the report project as a startup project by right-clicking the project in Solution Explorer and selecting Set as StartUp Project from the shortcut menu.

4. Set up a report as a start item. Right-click the project in Solution Explorer and select Properties from the shortcut menu, then from the Start Item drop-down, select a report that you want to debug, as shown in Figure 23.7.

Figure 23.7. Project Configuration dialog box.

5. Start debugging from the Debug, Start Debugging menu or by pressing F5. Use F11 to step through the report.

Running the GetLibraryInfo() example that was developed earlier in the chapter did not create any issues when you ran it in preview mode (Report Preview tab). This is because preview mode does not enforce security permissions. Let’s try to run GetLibraryInfo() in DebugLocal mode. Visual Studio breaks on the GetExecutingAssembly() call and shows an exception:

System.Security.SecurityException was unhandled by user code Message=“Request for the permission of type ‘System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.”

So, what happened with your code and why didn’t it work?

First, you have to check if the configuration was properly set. Reading of the configuration file was discussed earlier, but GetLibraryInfo() does not really read a configuration file. All it does is call the GetExecutingAssembly() function.

    public static string GetLibraryInfo()
    {
        return Assembly.GetExecutingAssembly().GetName().ToString()

            + (

                 (mPeriod != -1) ?

                    " Initialized with value=" + mPeriod.ToString()

                    : " Not  initialized "

                );

     }

The clue here is the fact that the assembly is trying to retrieve information about itself. So, it must mean that you need some kind of permission to that assembly file.

Earlier in this chapter, you learned that the GetExecutingAssembly() requires the PathDiscovery permission. To configure Report Designer for debugging of this library, copy the library to C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies or, better yet, set an output path for the library in Visual Studio. In Solution Explorer, right-click the library project, select Properties, click the Build tab, and enter C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies for the Output Path, as shown in Figure 23.8.

Figure 23.8. Library Properties interface.

The benefit of setting an output path is that Visual Studio realizes that it was using this library (through Report Designer) and, thus, is able to replace it. If Visual Studio compiles the library to an alternative location and a developer is trying to copy to the C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies directory, afterward he might not be able to do that. This is because Visual Studio will be “holding” this library during and after debugging. Visual Studio will require restart so it can release the “hold”  and so the library can be replaced.

The next step is to create a <PermissionSet> with PathDiscovery permission in C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssembliesRSPreviewPolicy.config:

   <PermissionSet class="NamedPermissionSet"

   version="1"

   Name="GetLibraryInfoPermissions"

   Description="A custom permission set to grant read access to a configuration file.">

  <IPermission

  class="FileIOPermission"

  version="1"

PathDiscovery="C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies"

  />

  <IPermission class="SecurityPermission"

  version="1"

Flags="Execution, Assertion"

/>

  </PermissionSet>

And in the same file, add the following:

  <CodeGroup class="UnionCodeGroup"

  version="1"

  PermissionSetName="GetLibraryInfoPermissions"

  Name="MyNewCodeGroup">

  <IMembershipCondition class="UrlMembershipCondition"

  version="1"

  Url="C:Program FilesMicrosoft Visual Studio 8 Visual Studio 8Common 7IDEPrivateAssembliesRSCustomLibrary.dll

</CodeGroup>

to this location:

...

ADD HERE (before the second CodeGroup above the PolicyLevel)

</CodeGroup>

  </CodeGroup>

</PolicyLevel>

The final step is to Assert the permission in the code, as follows:

public static string GetLibraryInfo()

{

try

  {

  FileIOPermission permission = new Perrmission

  (FileIOPermissionAccess.PathDiscovery,

  @"C:Program FilesMicrosoft Visual Studio 8Common7\IDEPrivateAssembliesRSCustomLibrary.dll");

  permission.Assert();

  }

  catch (SecurityException ex)

  {

  return ex.Message;

}

return Assembly.GetExecutingAssembly().GetName().ToString()

  + ((mPeriod != −1)?

         " Initialized with value=" + mPeriod.ToString()

             : " Not initialized");

   }

Summary

Custom assemblies greatly expand functionality (as compared to expressions and embedded code) that can be accessed from a report. A custom assembly can be written in any .NET language. Custom assemblies can leverage a full set of productivity enhancements available in Visual Studio .NET, such as Intellisense, source code management, full debugging capabilities, and much more.

Custom assemblies can be used for complex code, where usage of expressions and embedded code is no longer elegant. If a custom assembly requires more than default Execute permission, you need to have a good understanding of .NET security and SSRS configuration, modify configuration files to set assembly’s permissions, and assert the permissions in the assembly’s code.