none
Adding System.Data to Code tab in SSRS 2008 R2

    Question

  • SSRS 2008 R2 question:

    I have a query/stored procedure that I want to execute via .Net in the Code tab from SSRS. For both System.Data.OleDb and System.Data.SqlClient (tried both) I need to add System.Data to the references:

    So for no problem, but when I try to execute the code I only see #Error in the field where I call my function. And I get the following error for SqlClient:

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

    or when I use OleDb:

    Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox6.Paragraphs[0].TextRuns[0]’ contains an error: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

    In SSRS 2005 (long time ago) it was sufficient to set the PermissionSetName to FullTrust in the CodeGroup Report_Expressions_Default_Permissions (file:rssrvpolicy.config), but that doesn't work for 2008 R2.

    Any suggestions? All the posts about Code Access Security in Reporting Services are about custom DLL's but this isn't a Custom DLL...


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    • Edited by SSISJoost Monday, March 19, 2012 8:25 PM
    Monday, March 19, 2012 2:00 PM

Answers

All replies

  • and the function example:
        Public Function GetDelta(ByVal parameter1 As String, ByVal parameter2 As String, ByVal parameter3 As String) As Integer
            Dim Connection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection("Data Source=MyServer;Initial Catalog=MIS_BLA;Integrated Security=True;")
            ' Create query 
            Dim query As String
            query = "SELECT [Delta] FROM ......"
            Dim cmd As New System.Data.SqlClient.SqlCommand(query, Connection)
            cmd.Connection.Open()
            Dim Delta As Integer
            Dim objDataReader As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
            If objDataReader.HasRows Then
                Do While objDataReader.Read()
                    Delta = objDataReader.Item("Delta")
                Loop
            Else
                Delta = 0
            End If
            objDataReader.Close()
            cmd.Dispose()
            Return Delta
        End Function


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    • Edited by SSISJoost Monday, March 19, 2012 2:18 PM
    Monday, March 19, 2012 2:18 PM
  • Hi SSISJoost,

    Thanks for your post.

    Based on the error message you mentioned above, the custom assembly you referred requires more permissions than the default Execution level permissions, you must make some code access security changes to enable the reporting services to call to this assembly. So I suggest you to create a new CodeGroup for your custom assembly, and then grant full trust permissions. To do this, open the <Reporting Services Install path>\ReportServer\rssrvpolicy.config file, and then add the following code.
    <CodeGroup class="UnionCodeGroup"
       version="1"
       PermissionSetName="FullTrust"
       Name="MyCodeGroup"
       Description="Code group for my data processing extension">
          <IMembershipCondition class="UrlMembershipCondition"
             version="1"
             Url="C:\pathtocustomassembly\customassembly.dll"
           />
    </CodeGroup>

    Please replace the Url path to your own assembly path. Moreover, we recommend that you create a more specific permission set instead of assigning this permission set to your code group. This example is intended to get you up and running with custom assemblies, and this requires more detail.
    More information about this topic, please refer to the articles below:
    How to use custom assemblies or embedded code in Reporting Services: http://support.microsoft.com/kb/920769
    How to grant permissions to a custom assembly that is referenced in a report in Reporting Services:
    http://support.microsoft.com/kb/842419

    If you have anything unclear, please feel free to let me know.

    Thanks,
    Bill Lu


    Bill Lu

    TechNet Community Support


    Tuesday, March 20, 2012 8:23 AM
    Moderator
  • Hi Bill Lu,

    Thanks for the reply! But it isn't a custom assembly. It's a standard .Net dll: C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll
    Does that require creating a codegroup?

    In the SSRS Code tab I added the method above (not in a assembly) and because it uses System.Data.SqlClient (or System.Data.OleDB), I need to add a reference to System.Data.

    Joost


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, March 20, 2012 8:58 AM
  • Hi SSISJoost,

    Thanks for your feedback.

    An easiest way is to open the <Reporting Services Install path>\ReportServer\rssrvpolicy.config file, and then find the CodeGroup Report_Expressions_Default_Permissions, and then modify its PermissionSetName to FullTrust:
         <CodeGroup
                                    class="UnionCodeGroup"
                                    version="1"
                                    PermissionSetName="FullTrust"
                                    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"

    After you complete the below modification, you can rerun the report in report server or report manager web site, it would work just as you expected.

    Thanks,
    Bill Lu


    Bill Lu

    TechNet Community Support



    Tuesday, March 20, 2012 10:02 AM
    Moderator
  • We have chosen an other solution with the Lookup which is new in SSRS 2008 R2:
    http://msdn.microsoft.com/en-us/library/ee210531.aspx


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Marked as answer by SSISJoost Monday, March 26, 2012 9:40 AM
    Monday, March 26, 2012 9:40 AM