Sql 2005 CLR Integration - Is Dynamic Assembly Loading supported??

Question

I have a static class which loads a .NET assembly dynamically (using Assembly.LoadFile method) I get the following error message:

Msg 6522, Level 16, State 2, Line 3
A .NET Framework error occurred during execution of user-defined routine or aggregate "MySQLCLRUDFFunction": 
System.TypeInitializationException: The type initializer for 'MyClassName' threw an exception. ---> System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed

.

When I try assign CAS security using this declaration

[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Demand, Name = "FullTrust")]

I instead get this exception

Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "MySQLCLRUDFFunction": 
System.TypeInitializationException: The type initializer for 'MyClassName' threw an exception. ---> System.Security.SecurityException: Request failed.

Note: I have given my SQL Server service account "Full Access" to my dynamic assemly file on disk. I copied my dyamic assembly using syntax:

create Assembly TestAssembly
    From 'C:\MyTestAssembly.dll';
--Alter Assembly to copy dynamic assembly file
Alter Assembly TestAssembly add file from 'C:\mydynamicassembly.dll';

After turning TRUSTWORTHY ON and setting PERMISSION_SET = UNSAFE I now get this exception

Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "MySQLCLRUDFFunction": 
System.TypeInitializationException: The type initializer for 'MyClassName' threw an exception. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

Answer 1

I'm guessing you've got the PERMISSION_SET set to SAFE when you did CREATE ASSEMBLY (this will be the default if you didn't specify it). You'll need to change it to EXTERNAL_ACCESS or UNSAFE if you want to do this.

http://msdn.microsoft.com/en-us/library/ms189524.aspx

Answer 2

I was one the developers @ Microsoft who worked on SQL-CLR integration, so I may be able to help.

To achieve what you want you need to do two things:

1. Mark data base as TRUSTWORTHY
2. Mark assembly as UNSAFE (database must be trustworthy)
3. The account under which SQL Server runs must have permissions to access the file on the the file system. Often times the file is on a network share but SQL Server is run under local account that has no permission to access network, so this is often a problem. SQL Express in itse default installation does not have rights to C:.

Note that doing all those things has several negative side-effects:

1. serious security implications - you are effectively ceding control over the instance to the code in unsafe assembly as it can now use raw pointers to access/change anything in the engine.
2. Stability - you're also ceding stability guarantees for the same reasons.
3. Portability and disaster recovery - if you database has to move elsewhere for load-balancing or is restored from backup after machine failure you will not have the mydynamicassembly.dll on the new machine.

If at all possible, consider redesigning your app so that all needed assemblies are pre-loaded into the database itself.

[EDIT: if none of the above helps it best to ask on MSDN forums].

Answer 3

As the error message states, dynamic assembly loading is completely disallowed by SQL Server - even under unsafe. The only way for Assembly.Load calls to succeed is if the assembly is already loaded in the database via CREATE ASSEMBLY or in the GAC and on the list of supported ("blessed") assemblies. There is another post on this on the sqlclr blog.