Recently, we have troubleshoot a customer issue related to SQL CLR.  The problem is that the assembly is configured to use external_access but he kept getting an exception similar to this (this is a sample call stack):

Msg 6522, Level 16, State 1, Procedure sp_test, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_test":
System.MethodAccessException: Test..ctor() ---> System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.ReflectionPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
   at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
   at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
   at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
   at System.Security.PermissionSetTriple.CheckSetDemand(PermissionSet demandSet, PermissionSet& alteredDemandset, RuntimeMethodHandle rmh)
   at System.Security.PermissionListSet.CheckSetDemand(PermissionSet pset, RuntimeMethodHandle rmh)
   at System.Security.PermissionListSet.DemandFlagsOrGrantSet(Int32 flags, PermissionSet grantSet)
   at System.Threading.CompressedStack.DemandFlagsOrGrantSet(Int32 flags, PermissionSet grantSet)
   at System.Security.CodeAccessSecurityEngine.ReflectionTargetDemandHelper(Int32 permission, PermissionSet t
 ...
System.MethodAccessException:
   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache)
   at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache)
  ...

 

The issue turns out to be that customer has coded in a way that the object has a private constructor.   They use third party code to dynamically create the type using Activator.CreateInstance.  This requires fully trusted code. http://msdn.microsoft.com/en-us/library/he47tyc4.aspx has documenation on this.

From SQL CLR perspective, the solution is to use unsafe assembly or stop using private constructor.

 The following code will reproduce it if you set the assembly to be safe or external_access.  Unsafe will work.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Reflection;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void sp_test()
    {

        object obj = Activator.CreateInstance(typeof(Test), true);

    }

 


public class Test
{


    private Test()
    {


    }
}

};

 

 -------------------------------------------------------------------------------------------------

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support