SSRS Custom Assembly Permissions issue

Question

I am trying to create a custom SSRS assembly that formats the style of a report based on the value of an SQL table.

I need to be able to preview this locally and deploy to the reporting server.

This would allow me to roll out style changes to a series of Paginated Reports from a central place.

Example expression: =ProjectName.ClassName.getItemStyle("Header", "Font-Family") The expression parses the values I'm looking for then goes off and returns a value from the SQL table, in this case "Verdana".

I've been through various steps to get this working as far as I have and have now reached a permissions problem. The assembly works fine so long as I don't try to connect to an SQL server.

In which case I receive this error:

The Value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ contains an error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

I have tried adding in assembly permissions and editing the rssrvpolicy config file to no avail, but I feel like I am missing something and I'm not too sure I understand the Microsoft documentation on this.

I have also signed the assembly and installed the certificate.

Code I've used:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using System.Security;
[assembly: AllowPartiallyTrustedCallers]

namespace CC_rpt
{   
    public class lib
    {
        public static string getItemStyle(string parse)
        {
            string val;
            val = "1";

            try
            {              
                using (SqlConnection oConn = new SqlConnection("Data Source=DBServer;Initial Catalog=DBName;Integrated Security=SSPI"))
                {
                    var oCmd = new System.Data.SqlClient.SqlCommand();
                    oCmd.Connection = oConn;
                    oCmd.CommandText = "select singleValue FROM Table WHERE Column = @Param";
                    oCmd.Parameters.AddWithValue("@Param", parse);
                    val = oCmd.ExecuteScalar().ToString();
                }
            }

            catch (System.Data.SqlClient.SqlException e)
            {
                val = e.ToString();
            }

            return val;
        }
    }
}

OS: Windows 7 Enterprise Version 6.1 (Build 7601: Service Pack 1)

Visual Studio: Microsoft Visual Studio Professional 2017 Version 15.9.13

Server SSRS Reporting Services 2016

Installed Products:
SQL Server Reporting Services 15.0.1528.0 SQL Server Integration Services 15.0.1300.371

.Net libraries I'm primarily trying to use: System.Data.SqlClient targeting .Net Framework: 4.6.1

Paths I'm copying the DLL to (Via a post build event)*:

C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\CommonExtensions\MSBuild C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\PrivateAssemblies C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\Reporting Services C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\CommonExtensions\Microsoft\SSRS C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin

Assembly security settings: I have signed the assembly created the string key file (pfx) and installed it.

Nestor

I should add that the permissions issue isnt an issue connecting to the actual server as I have permissions to CRUD and have tested this prior. – Nestor

I cant believe no one has come across this before? – Nestor

Answer 1

Okay. Same problem, 30 Firefox Tabs later:

My jurney starts here: http://bryantlikes.com/WritingCustomCodeInSQLServerReportingServices.aspx

You need to add a CodeGroup to this file: c:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer\rssrvpolicy.config

Couldn't really get it to work with a Custom Permission Set, so I used FullTrust. Maybe not the cleanest?

My last version of the CodeGroup looks as follows:

<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="DBAccessPermissionCodeGroup" Description="A special code group for my custom assembly.">
    <IMembershipCondition class="UrlMembershipCondition" version="1" Url="c:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer\bin\Your_Custom_DLL_Name.dll" />
</CodeGroup>

By the way: Check the url of the dll, might seem obvious but yeah, don't ask me how I know..

Then you gotta mark your Methods (and/or maybe Classes?) as [SecuritySafeCritical], because SSRS's Expression thingy runs as SecurityTransparent. (https://mcmap.net/q/1175015/-check-acive-directory-group-membership-form-within-ssrs-report-custom-code)

If you don't do that, you will get an error that says something like Cannot perform CAS Asserts in Security Transparent methods

And you also want to add the following Attribute to the Method that uses the SQL Stuff [SqlClientPermission(System.Security.Permissions.SecurityAction.Assert, Unrestricted = true)]

If you leave this out, you will get exactly the error you are experiencing.

And in my AssemblyInfo.cs I added [assembly:AllowPartiallyTrustedCallers] (https://mcmap.net/q/1175016/-code-access-security-exception-in-restricted-appdomain)

I don't know if it is necessary, but my .dll is signed.

When finished, I enabled TCP/IP in my SQL Server and created a new user (and enabled SQL-Server authentication).

By the way: to debug #Error messages, I used prodcump. ./procdump.exe -f "" -l -e 1 ReportingServicesService.exe

https://learn.microsoft.com/en-us/sysinternals/downloads/procdump

I hope this helps someone, because that was not funny to research. But now my custom assembly finally works.

My connection string: Data Source=localhost,1433;Initial Catalog=database;User Id=user;Password=pass;

This PermissionSet did NOT work for me:

<PermissionSet class="NamedPermissionSet" version="1" Name="DBAccessPermissionSet" Description="A special permission set that grants access to Databases.">
  <IPermission class="SqlClientPermission" version="1" Unrestricted="true" />
  <IPermission class="SecurityPermission" version="1" Flags="Execution, Assertion" />
</PermissionSet>

It somehow also throws the Cannot perform CAS Asserts in Security Transparent methods Error.

Strathspey

Wow thankyou very much :) I havnt yet tried this but it makes sense - I've been searching ages too and its a pitty microsoft dont make this clear in the docs. – Nestor

Answer 2

If you are having issue with the Visual Studio preview, then the solution from @bergerb is correct but you are looking at different folder/file

1. The folder should be something like C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\CommonExtensions\Microsoft\SSRS

2. The file you need to edit is RSPreviewPolicy.config in the same location

3. Add this code from @bergerb

<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="DBAccessPermissionCodeGroup" Description="A special code group for my custom assembly.">
    <IMembershipCondition class="UrlMembershipCondition" version="1" Url="c:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer\bin\Your_Custom_DLL_Name.dll" />
</CodeGroup>

Hushhush