Reporting Services 2005 and Embedded Code

I’ve a got a report where a field must be populated calling a “cascading” stored-procedure from the main query.

There 2 ways to do it:

• To Create a sub-report and embedding it in the field just to retrieve the sp result (easy to deploy, but weak in performance)
• Use SSRS Embedded code with ADO.net to invoke the stored-procedure populating the field.

Obviously the second way is “more” pragmatic (it guarantees the best performance but… from a developer perspective it’s inelegant).

Well I’ve chosen the second way so I needed to setup the SSRS .Net CAS permission.

1. Open the Report Server application directory (something like C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager)
2. Open rsmgrpolicy.config
3. Add a new PermissionSet to allow execution of SQL code

                           <PermissionSet
                                   class="NamedPermissionSet"
                                   version="1"
                                   Name="Corra-Expression">
                               <IPermission
                                       class="SqlClientPermission"
                                       version="1"
                                       Unrestricted="true"
                               />
                               <IPermission
                                       class="SecurityPermission"
                                       version="1"
                                       Flags="Execution"
                               />

4. Modify the predefined Code Group (for Reporting Services expressions) after having backed-up the previous one:

                           <CodeGroup
                                   class="UnionCodeGroup"
                                   version="1"
                                   PermissionSetName="Corra-Expression"
                                   Name="Report_Expressions_Default_Permissions"
                                   Description="This code group grants default permissions for code in report expressions and Code element. ">
                               <IMembershipCondition
                                       class="StrongNameMembershipCondition"
                                       version="1"
                                       PublicKeyBlob="blablabla"
                               />

5. Now you’re able to invoke ADO.net from SSRE expressions.