CODEWITHSUNDEEP
sql-server-2005sqlclr
Julius A
Julius A

Reputation: 39622

Sql 2005 CLR Integration - Is Dynamic Assembly Loading supported?

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.

Upvotes: 2

Views: 3232

Answers (4)

Matthew Abbott
Matthew Abbott

Reputation: 61599

I know this is a very old question now, but I have recently found a way to achieve what you want. When you try and using Assembly.Load(...) in a SQL CLR hosted assembly, it will explicitly fail, this is by design, even with PERMISSION_SET = UNSAFE. This is to ensure the stability of the database server.

The technique to loading your dynamic assemblies, is to register them first and then resolve the type using Type.GetType(...) passing in the fully qualified type name (including assembly version information.

Here are the steps:

1.Compile the type, and finalise on disk (i.e. do not create in-memory assemblies). The CompilerResults type will have a CompiledAssembly property and PathToCompiledAssembly property. Use the latter as accessing the CompiledAssembly property will attempt to use Assembly.Load.

2.Using the path, I call a stored procedure from my code (using new SqlConnection("Context Connection = true")) which I pass in the name of the assembly (which I have predetermined) and the compiled assembly path:

CREATE PROCEDURE re.CreateAssembly
    @name VARCHAR(100),
    @path VARCHAR(1000)
AS
BEGIN
    DECLARE @sql NVARCHAR(2000)
    SET @sql = N'CREATE ASSEMBLY [' + @name + '] AUTHORIZATION [DatabaseUser] FROM ''' + @path + ''' WITH PERMISSION_SET + SAFE';
    EXEC sp_executesql @sql;
END
GO

3.Using your predetermined name, you can use Type.GetType(...), e.g.:

string typeName = "MyCompiledAssembly.MyClass, MyCompiledAssembly, Version=0.0.0.0, Culture=Neutral, PublicKeyToken=null, ProcessorArchitecture=MSIL";
Type type = Type.GetType(typeName);

When the SQLCLR attempts to resolve the type, it should find it, because in step 2 you've already registered the assembly with Sql Server.

Upvotes: 1

stevehem
stevehem

Reputation: 671

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.

Upvotes: 2

DenNukem
DenNukem

Reputation: 8254

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].

Upvotes: 0

Greg Beech
Greg Beech

Reputation: 136637

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

Upvotes: 0

Related Questions