Summary

SQL Server 2000 Reporting Services is a server-based reporting solution that can deliver interactive Web-based reports. This article describes a problem that occurs when you access a custom assembly in your Reporting Services report that does not have sufficient permissions on the resources in your environment. The article also provides simple steps to grant permissions to the custom assemblies that are used in the reports.

Introduction

After you create a Microsoft .NET custom assembly and then you access the custom assembly in your report and preview the report in Report Designer mode, the report will render successfully. However, if you run the same report in DebugLocal mode in the VS IDE or if you access the report by Report Manager or Report Server or Web Interface the following error will be rendered in the report.

#Error

First let us start with creating a simple custom assembly and using it in Reports and then move to a complex one which needs permission to run successfully

Using Simple custom assembly in reporting services report

Step 1: Create a custom assembly

Step 2: Creating a Report

Create a Business Intelligence Project and add a Report to the project in the layout section drag and drop a text box.

Step 3: Adding an Assembly Reference to a Report

Right click the report designer area and choose properties and select the References tab and browse and select the custom dll from the location.

Step 4: Using Custom assembly in Reports

In order to use your custom code in your assembly in a report expression, you must call the member of a class within the assembly. This will be done differently depending on how the method was declared.

If the method is defined as static it is available globally within the report. You access it by namespace, class, and method name

=SimpleDLL.SimpleClass.returnString()

Step 5: Writing expression to the text box

Come back to Report layout tab and now right click the textbox and choose expression and enter the expression.

 

Step 6: Copying DLL to Report Designer and Report Server folders

Before previewing or deploying you need to deploy your custom assemblies to the application bin folders.

• For the Report Designer C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer.
• For the Reporting Server C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin.

Step 7: Preview Report and deploying report

 
Now you can deploy the report to the report server and view it in the browser. To deploy the report, right click the report and choose deploy, before deploying set the target folder in the project properties.

Using Complex custom assembly in reporting services report

So far we have seen simple custom assembly in reports, now if you actually want to do something interesting like fetch data from a text file or database or use web services or registry access then we need to modify the report server policy config file and Assert permissions in your custom assembly.

Here are the steps to do that.

Step 1: Assert Permission

You must assert the required permissions in your custom assembly code, for example if you access a text file in your custom assembly then you need to add the following code to the custom assembly and add

using System.Security.Permissions.

FileIOPermission fileReadPerm = new FileIOPermission (FileIOPermissionAccess.Read, "C:\SampleFile"); fileReadPerm.Assert();

Step 2: Mark the custom assembly with the AllowPartiallyTrustedCallersAttribute

Mark the custom assembly with the AllowPartiallyTrustedCallersAttribute. This is required because custom assemblies are called from a report expression that is a part of the report expression host assembly, which by default is not granted FullTrust, thus is a 'partially trusted' caller. You can do this in your assembly attribute file AssemblyInfo.cs in C# before doing this add

using System. Security

Now compile the project and copy the dll to the following folders

C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin.
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer.

Step 3: Adding an Assembly Reference to a Report and View the Report

Remove the existing reference of the assembly and add the new reference to the report  and then view the report in the Preview window, you will see the data from the text file displayed.

Step 4: View Report in Debug local mode

Now when you view the report in DebugLocal mode now, instead of see the text from the text file you will see a message #Error in the text box. To switch to debuglocal mode just change the mode of execution to DebugLocal and right click Project properties and set the StartItem with the report you want to debug.

Now press F5, you will see this message in the screen.

If you preview the report in Report Designer, the report runs all the report expressions by using the FullTrust permission set. The report does not use the security policy settings. But when you run the report in DebugLocal mode or deploy report to the report server and view it in report manager you will receive the message #Error, This problem may occur when the custom assemblies that are referenced in your report do not have sufficient permission

Step 5: Code Access Security settings for your Custom Assembly

We need to modify the report server policy configuration files in order to grant the custom assembly the required permissions.

Open the rssrvpolicy.config file in the C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer folder

Note: Before making a change to the config file it is recommended making a backup copy of the original file

In the config file we need to add the code group we can add the following code. We have to place this under the correct CodeGroup node in order to work correctly. So you will need to put it under the last code group.

<CodeGroup
class="FirstMatchCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyCustomAssemblyCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\Simple.dll"/>
</CodeGroup>           

rssrvpolicy.config

Step 6: View Report with Data

Now when you run the report in DebugLocal mode and you will see the data from the text file, before executing the report, restart the Report Server service.

Conclusion

This article provides the steps to use custom assembly in Reports. Now you should have a basic understanding of how to use custom code with reporting services.