I have written a few CLR integrated user-defined function, one of which has needs to call a PInvoke method to a C++ dll in an absolute path.
[DllImport(@"C:\FullPath\CppCode.dll"...
I have deployed this on SQL server 2008 R2 (no service packs) and I am able to sucessfully call this UDF and recieve query results. (I enabled CLR, loaded the CLR assembly with permission set UNSAFE and set trustworthy to true. I have also full admin priviliges on the machine as well as the SQL server instance)
however, when I do the exact same thing on another machine with SQL Server 2012 installed (Operating system and almost all configuration on this machine is identical to the other) I can call all the CLR functions except for the PInvoke function, which gets the following error message
Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "MyUserDefinedFunction":
System.ArgumentException: System.Security.Policy.PolicyException Execution permission cannot be acquired.
at UserDefinedFunctions.MyMethodWithPInvoke()
...(more stack trace)
---> System.Security.Policy.PolicyException: Execution permission cannot be acquired.
System.Security.Policy.PolicyException:
... (more stack trace)
System.ArgumentException:
at UserDefinedFunctions.MyUserDefinedFunction( ...
I have worked with our database people and we tried granting admin permissions to all accounts we could think of (even the service accounts for all the SQL Server services in the SQL Server configuration manager) and we are at our wits end trying to figure out what kind of policy change was made between SQL Server 2008 and 2012.
Does anyone know what needs to be done to allow a SQL CLR function to call PInvoke to a DLL on disk in SQL Server 2012? It works fine in 2008 R2
edit:
I threw in a try/catch around the PInvoke call and got was able to find That The PolicyException has the following Properties
Message: "Execution permission cannot be acquired."
Source: "mscorlib"
I was able to figure out the problem. The dll I was using in sql 2008R2 was a .NET managed C++ dll. This appears to be allowed to be pinvoked into from sql 2008R2 but not in 2012
copying the code to a pure C++ dll allowed it to be called from 2012.