troubleshooting Question

Custom assembly - Request for the permission of type 'System.Security.Permissions.FileIOPermission' failed.

Avatar of chinacat456
chinacat456 asked on
C#Microsoft SQL Server 2005SSRS
4 Comments1 Solution2748 ViewsLast Modified:
I have a Sql Server Reporting Services 2005 report that access a custom assembly that I wrote.  The custom assembly reads a .txt file for a connection string, and then makes calls to an Analysis Services data warehouse using ADOMD.Net.
I can run the report that calls the assembly locally without error.  However, when I deploy the report and dll to the report server, I am getting the following error:

Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

I have done the following::
1. Added permission assertions to the method in my assembly (one for reading the .txt file containing the connection string, and one for writing errors to)
        [FileIOPermission(SecurityAction.Assert, All = @"c:\ARA\WarehouseConnection.txt")]
        [FileIOPermission(SecurityAction.Assert, All = @"c:\ARA\Errors.txt")]
2. Added this to the AssemblyInfo.cs file:
        [assembly: AllowPartiallyTrustedCallers]
3. Strong named the assembly.
4. Copied the dll to the report server (C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin)
5. Copied the .txt file containing the connection string to a folder on the report server (C:\ARA)
6. Added the aspnet user account (with Full Control) to the security tab for the folder containing the .txt files (C:\ARA).
7. Modified the rssrvpolicy.config file on the report server to contain a new CodeGroup element for my dll (using the publickeyblob for my SN'd assembly):
      <CodeGroup class="UnionCodeGroup"
            <IMembershipCondition class="StrongNameMembershipCondition"

The strange thing is that my assembly writes to an Errors.txt file in the Catch block without any problem.  But it's writing the error shown above.  How could the code have a problem accessing the FileIOPermission object when it seems to be using it for writing out to the text file?
I have searched Google for the past 2 days for a resolution to this.  I am at my whits end!!  HELP!
My assembly code is below.

Thank you in advance!!
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.AnalysisServices.AdomdClient;
using System.IO;
using System.Security.Permissions;
using System.Data.SqlClient;
using System.Diagnostics;
namespace ARACustomReportFunctions
    public class CustomFormulaFunctions
        public static string GetPreviousQuarter(string currentQuarter, int previousCount)
            int counter = 1;
            int quarterNumber = Convert.ToInt32(currentQuarter.Substring(1, 1));
            int quarterYear = Convert.ToInt32(currentQuarter.Substring(currentQuarter.Length - 4, 4));
                switch (quarterNumber)
                    case 1:
                        quarterNumber = 4;
                    case 2:
                    case 3:
                    case 4:
            } while (counter <= previousCount);
            return "Q" + quarterNumber + " " + quarterYear;
        //[FileIOPermissionAttribute(SecurityAction.Assert, Read = "WarehouseConnection.txt")]
        //[FileIOPermissionAttribute(SecurityAction.Assert, Write = "ARAErrors.txt")]
        //[FileIOPermissionAttribute(SecurityAction.Assert, All = "c:\\ARA\\WarehouseConnection.txt")]
        //[FileIOPermissionAttribute(SecurityAction.Assert, All = "c:\\ARA\\Errors.txt")]
        //[FileIOPermissionAttribute(SecurityAction.Assert, Unrestricted = true)]
        //[FileIOPermissionAttribute(SecurityAction.Assert, Unrestricted = true)]
        [FileIOPermission(SecurityAction.Assert, All = @"c:\ARA\WarehouseConnection.txt")]
        [FileIOPermission(SecurityAction.Assert, All = @"c:\ARA\Errors.txt")]
        public static string QuarterlyReturn(string vehicleId, string formulaName, int yearlyFactor, string reportQuarter)
            //SqlClientPermission sqlPermission = new SqlClientPermission(PermissionState.Unrestricted);
            //FileIOPermission filePermission = new FileIOPermission(FileIOPermissionAccess.AllAccess, "c:\\ARA\\WarehouseConnection.txt");
            //FileIOPermission fileWritePermission = new FileIOPermission(FileIOPermissionAccess.AllAccess, "c:\\ARA\\Errors.txt");
            double quarterlyReturn = 0;
                //throw new Exception("Testing exception throwing");
                //build query string (selects all returns for the vehicle)
                StringBuilder query = new StringBuilder();
                query.Append("SELECT NON EMPTY { [Measures].[IV Eval Value] } ON COLUMNS, NON EMPTY { ([Investment Vehicle].[Investment Vehicle Alternate Key].[Investment Vehicle Alternate Key].ALLMEMBERS * [Formula].[Dim Formula].[Dim Formula].ALLMEMBERS * [Period].[Period Quarter].[Period Quarter].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Formula].[Dim Formula].[");
                query.Append("] ) ON COLUMNS FROM ( SELECT ([Investment Vehicle].[Investment Vehicle Alternate Key].&[");
                //open connection to OLAP
                AdomdConnection adomdConn = new AdomdConnection();
                //retrieve connection string from text file
                string connectionStr = "";
                using (StreamReader reader = new StreamReader("c:\\ARA\\WarehouseConnection.txt"))
                //using (StreamReader reader = new StreamReader("WarehouseConnection.txt"))
                    connectionStr = reader.ReadToEnd();
                adomdConn.ConnectionString = connectionStr; //"Data Source=http://SD031/olap/msmdpump.dll;Initial Catalog=ARA Data Warehouse";
                //adomdConn.ConnectionString = "Data Source=http://SD031/olap/msmdpump.dll;Initial Catalog=ARA Data Warehouse";
                //execute query
                AdomdCommand myCmd = adomdConn.CreateCommand();
                myCmd.CommandText = query.ToString();
                AdomdDataReader myReader = myCmd.ExecuteReader();
                double geometricallyLinkedReturn = 1;
                //int counter = 1;
                //int periodsSinceInception = 0;
                string quarterName = "";
                double quarterCounter = yearlyFactor * 4; //total # of quarters for the calculation
                while (myReader.Read())
                    //multiply all 1 plus returns together for the quarters included in the calc
                    if (!myReader.IsDBNull(0))
                        //Retrieve quarter name from record
                        quarterName = myReader.GetString(4);
                        //if yearly factor is 0, just return current quarter's return
                        if (yearlyFactor == 0)
                            if (quarterName.ToUpper() == reportQuarter.ToUpper())
                                geometricallyLinkedReturn = Convert.ToDouble(myReader.GetString(6));
                            for (int quarters = 1; quarters < quarterCounter; quarters++)
                                //if quarter name matches current quarter or any previous quarter included in the calculation, link returns
                                if (quarterName.ToUpper() == reportQuarter.ToUpper() ||
                                    quarterName.ToUpper() == GetPreviousQuarter(reportQuarter, quarters).ToUpper())
                                    //retrieve evalvalue, add 1, and multiply by current geometricallyLinkedReturn
                                    geometricallyLinkedReturn = geometricallyLinkedReturn * (Convert.ToDouble(myReader.GetString(6)) + 1);
                //calculate annualization factor
                double annualizationFactor = 4 / quarterCounter;
                //calculate return: (geometrically linked returns ^ annualization factor) - 1
                if (yearlyFactor != 0)
                    quarterlyReturn = (Math.Pow(geometricallyLinkedReturn, annualizationFactor) - 1) * 100;
                    quarterlyReturn = geometricallyLinkedReturn * 100;
            catch (Exception ex)
                //throw ex;
                //EventLog.WriteEntry("SSRS_ARA", ex.Message);
                //System.Diagnostics.EventLog appLog =
                //    new System.Diagnostics.EventLog();
                //appLog.Source = "ARACustomReportFunctions";
                using (StreamWriter writer = new StreamWriter("c:\\ARA\\Errors.txt"))
                //using (StreamWriter writer = new StreamWriter("ARAErrors.txt"))
            return quarterlyReturn.ToString("N2") + "%";
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;
using System.Security;
using System.Security.Permissions;
// General Information about an assembly is controlled through the following 
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle("ARACustomReportFunctions")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("SMART")]
[assembly: AssemblyProduct("ARACustomReportFunctions")]
[assembly: AssemblyCopyright("Copyright © SMART 2009")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
// Setting ComVisible to false makes the types in this assembly not visible 
// to COM components.  If you need to access a type in this assembly from 
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(false)]
// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid("d9de2e07-b23e-4981-84ec-c4c2d3671fc0")]
// Version information for an assembly consists of the following four values:
//      Major Version
//      Minor Version 
//      Build Number
//      Revision
// You can specify all the values or you can default the Revision and Build Numbers 
// by using the '*' as shown below:
[assembly: AssemblyVersion("")]
[assembly: AssemblyFileVersion("")]
[assembly: AllowPartiallyTrustedCallers]

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros