Hey guys,
Good morning!

Today I'm going to talk about a problem that can occasionally occur with CLR assemblies that use unsupported DLLs (Ex: System.DirectoryServices) and were compiled with the UNRESTRICTED (UNSAFE) or EXTERNAL ACCESS permission. When trying to use an SP or function that requires this type of access, we are faced with the following error message:

A .NET Framework error occurred during execution of user-defined routine or aggregate “stpNome_Da_SP”: System.ApplicationException: Erro : An error occurred in the Microsoft .NET Framework while trying to load assembly id 65591. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly ‘my_assembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. Exception from HRESULT: 0x80FC0E21 System.IO.FileLoadException:

This type of error usually occurs due to some update to the server's .NET Framework without restarting the SQL Server service, and it ends up getting lost.

Fortunately, the solution to this type of problem is very easy, and just change the assembly permission and return to the original permission. In my case, this assembly uses the UNRESTRICTED permission, so I set the permission to EXTERNAL_ACCESS and then return to UNRESTRICTED:

USE [master]
GO
GRANT UNSAFE ASSEMBLY TO [Seu_Usuario]
GO

USE [CLR]
GO

ALTER ASSEMBLY Meu_Assembly
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

ALTER ASSEMBLY Meu_Assembly
WITH PERMISSION_SET = UNSAFE
GO

This assembly really needs the UNRESTRICTED permission, since I imported the SharpZipLib library to use compression routines in this CLR and because this library uses code finalizers (post-compiled code), it doesn't really work with the EXTERNAL_ACCESS permission.

Therefore, if you try to make these permissions changes from the Management Studio screen, you will come across an error message like this:

ALTER ASSEMBLY failed because type “ICSharpCode.SharpZipLib.Tar.TarArchive” in external_access assembly “Meu_Assembly” has a finalizer. Finalizers are not allowed in external_access assemblies. (.Net SqlClient Data Provider)

For some reason, when you make this change through the line of code (with the commands listed above) this type of error does not occur and so we can easily fix this problem without having to publish our CLR again.

If you have questions, suggestions or criticism, please leave them here in the comments.
Thanks for visiting and see you in the next post!

A .NET Framework error occurred during execution of user-defined routine or aggregate “stpNome_Da_SP”: System.ApplicationException: Erro : An error occurred in the Microsoft .NET Framework while trying to load assembly id 65591. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly ‘my_assembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. Exception from HRESULT: 0x80FC0E21 System.IO.FileLoadException