none
ssrs report with custom code to validate parameters

    Question

  • Hello,

    I am new to SSRS report administration...

    Here is my question....

    I have created SSRS report with custom code in which I connected to a database and get the data to validate against the report parameters. It worked fine when i run the report from BIDS. In order to run this I need to change the configuration file's Code Group property by setting to "FullTrust". But when I deployed the report to report manager, it is giving me this error...

    Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. (rsRuntimeErrorInExpression).

    Can some one help this out why there is issue with report server....

    Thanks for your help in advance

    Friday, March 16, 2012 5:17 AM

Answers

  • Hi,

    For security reason, you should change following two groups toFullTrustin three configure files, please follow these steps to allow executing the custom code:

    1.Open these three files:

    1).RSPreviewPolicy which located at C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

    2).rsmgrpolicy which located at C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager

    3).rssrvpolicy which located at C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

    2. Change the PermissionSetName of the following 2 groups from “Execution” 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.">

    </CodeGroup>

    <CodeGroup

    class="FirstMatchCodeGroup"

    version="1"

    PermissionSetName="FullTrust"

    Description="This code group grants MyComputer code Execution permission. ">

    <IMembershipCondition

    class="ZoneMembershipCondition"

    version="1"

    Zone="MyComputer" />

    </CodeGroup>

    3. Specify a execution account for Reporting Services using Report Services Configuration Manager.

    Please get some reference from this similar thread http://social.msdn.microsoft.com/Forums/is/sqlreportingservices/thread/90bc9167-af50-42ac-be5a-4f10dc7cc652

    For more information, please see: Introducing Code Access Security in Reporting Services:http://technet.microsoft.com/en-us/library/ms154658(SQL.90).aspx


    Challen Fu

    TechNet Community Support

    Monday, March 19, 2012 8:40 AM
    Moderator
  • You should have the Admin permission on the server to achieve this. Please contact your system administrator to help this.

    Challen Fu

    TechNet Community Support

    Wednesday, March 28, 2012 4:55 AM
    Moderator

All replies

  • Please check the service account of Reporting Services. I think it's a permission issue.

    Regards, Nighting Liu

    Friday, March 16, 2012 9:36 AM
  • Hi,

    For security reason, you should change following two groups toFullTrustin three configure files, please follow these steps to allow executing the custom code:

    1.Open these three files:

    1).RSPreviewPolicy which located at C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

    2).rsmgrpolicy which located at C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager

    3).rssrvpolicy which located at C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

    2. Change the PermissionSetName of the following 2 groups from “Execution” 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.">

    </CodeGroup>

    <CodeGroup

    class="FirstMatchCodeGroup"

    version="1"

    PermissionSetName="FullTrust"

    Description="This code group grants MyComputer code Execution permission. ">

    <IMembershipCondition

    class="ZoneMembershipCondition"

    version="1"

    Zone="MyComputer" />

    </CodeGroup>

    3. Specify a execution account for Reporting Services using Report Services Configuration Manager.

    Please get some reference from this similar thread http://social.msdn.microsoft.com/Forums/is/sqlreportingservices/thread/90bc9167-af50-42ac-be5a-4f10dc7cc652

    For more information, please see: Introducing Code Access Security in Reporting Services:http://technet.microsoft.com/en-us/library/ms154658(SQL.90).aspx


    Challen Fu

    TechNet Community Support

    Monday, March 19, 2012 8:40 AM
    Moderator
  • Hi Nighting,

    Yes , it is a permission issue and I edited configuration files by changing the Permission set name to 'Full Trust'. It did work for me.

    Thank you very much..

    Tuesday, March 20, 2012 5:13 AM
  • Hi Challen,

    Thank you very much. It is very helpful information. I followed the same and it worked for me.....

    Thanks,

    Tuesday, March 20, 2012 5:15 AM
  • Hi Challen,

    It did work fine when I change the settings in the configuration files in our dev/qa, but on our production server, i was not able to see ReportManager/ReportSErver folders and hence policy configuration files. Can you help me out what if I don't have those directories? how should I get access to those installed?

    Thanks

    Tuesday, March 27, 2012 8:25 PM
  • You should have the Admin permission on the server to achieve this. Please contact your system administrator to help this.

    Challen Fu

    TechNet Community Support

    Wednesday, March 28, 2012 4:55 AM
    Moderator
  • Challen,

    Sorry to reply you back..BUSY WITH PRODUCTION RELEASES LAST WEEK. Yes it is, I got the admin permissions and hence the configuration files.

    Thank you very much...

    Friday, April 06, 2012 5:02 AM
  • Hi Challen Fu,

    I have created a custom assembly using .NET Framework 3.5. In this assembly, we call Microsoft translator webservice (http://msdn.microsoft.com/en-us/library/ff512421.aspx) to translate Chinese string to English.

    When we call the DLL from SSRS textbox expression in the designer (SSDT), report returns correct results. But when I deploy the rdl in the reportserver, report returns #Error.

     Below are the things we have performed.

    1. Created the DLL using >NET 3.5 class library.
    2. In the class library AssemblyInfo.cs, included System.Security namespace and [assembly: AllowPartiallyTrustedCallers()]
    3. Build the DLL
    4. Copied the DLL to “C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies”
    5. As you mentioned, modified the RSPreviewPolicy.config file 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"
                                        PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100512C8E872E28569E733BCB123794DAB55111A0570B3B3D4DE3794153DEA5EFB7C3FEA9F2D8236CFF320C4FD0EAD5F677880BF6C181F296C751C5F6E65B04D3834C02F792FEE0FE452915D44AFE74A0C27E0D8E4B8D04EC52A8E281E01FF47E7D694E6C7275A09AFCBFD8CC82705A06B20FD6EF61EBBA6873E29C8C0F2CAEDDA2"
                                />
                            </CodeGroup>
                            <CodeGroup 
                                    class="FirstMatchCodeGroup" 
                                    version="1" 
                                    PermissionSetName="FullTrust"
                                    Description="This code group grants MyComputer code Execution permission. ">
    <IMembershipCondition 
                                        class="ZoneMembershipCondition"
                                        version="1"
                                        Zone="MyComputer" />

     6. Copied the DLL to “C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin”

    7. Modified the rsreportserver.CONFIG file PermissionSetName to “FullTrust"

    8. Modified the rsmgrpolicy.Config file PermissionSetName to “FullTrust"

    9. In the SSRS report, have added the references.

    10)  10. Calling the DLL method from the SSRS textbox expression like below.

    11) =MicrosoftTranslatorSdk.HttpSamples.LanguageTranslaterLibrary.DetectMethod(“东银座商城”)

    Please help me to identify this issue. 

    Thanks,

    Ramesh M

    Friday, October 04, 2013 12:23 PM