SQL Server - The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE

Hello people,
Good Morning!

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

A .NET Framework error occurred during execution of user-defined routine or aggregate “stpName_Da_SP”: System.ApplicationException: Error: 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 'meu_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 because of some update to the server .NET Framework without restarting the SQL Server service, and it gets lost.

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

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 terminators (post-compiled code), it really doesn't 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 “My_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 kind of error does not occur so we can easily fix this problem without having to republish our CLR.

If you have questions, suggestions or any criticism, please leave it here in the comments.
Thanks for stopping by and see you next post!

A .NET Framework error occurred during execution of user-defined routine or aggregate “stpName_Da_SP”: System.ApplicationException: Error: 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 'meu_assembly, Version = 0.0.0.0, Culture = neutral, PublicKeyToken = null' or one of its dependencies. Exception from HRESULT: 0x80FC0E21 System.IO.FileLoadException