.NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

Question

I am migrating a database application from Windows 2008 R2/SQL Server 2008 R2 to Windows 2012 R2/SQL Server 2016 that uses a third party .NET CLR assembly to parse strings.

The error I get is:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65540. 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 'clrsplit, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

System.IO.FileLoadException: at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection) at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.Load(String assemblyString) [SQLSTATE 42000] (Error 10314).

Error: 10314, Severity: 16, State: 11.

The database TRUSTWORTHY bit is set:

name             is_trustworthy_on
msdb             1
SimplusStaging   1

PERMISSION_SET is set to UNSAFE. Assembly is marked UNSAFE because those were the vendor installation instructions. Have tried dropping the Assembly and related T-SQL objects and re-creating them. No change. The DBO user is the SA login. And, this is the only CLR assembly we use for this database/server.

This is a new install using detach/attach. Did the KB2919355 install because otherwise SQL Server 2016 wouldn't install.

I would love to use the new STRING_SPLIT function in 2016 except that the application is supported by a third-party and they built everything for 2008 R2. Wanted to take advantage of some of the new features in 2016 where "it just runs faster".

The SID of the db owner is the same for both the original install and the new install. Both of the following queries return 0x01 when run in the context of the problem database:

SELECT [sid] FROM sys.database_principals WHERE [name] = N'dbo';
SELECT [owner_sid] FROM sys.databases WHERE [database_id] = DB_ID();

Do I need to recompile the CLR assembly for a new .Net Framework or should it just work?

Doing this same migration to SQL 2012 and 2014 on Windows 2008 R2 worked without issue.

Answer 1

An error in the form of:

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id #####. 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 '{_assembly_name_}, Version=#.#.#.#, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxxxx' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

means that the Assembly, which is currently marked with a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE is not allowed to make use of that permission level until the second part of the SQLCLR permissions setup is taken care of. That second part is to do one of the following:

• The very much preferred approach

  1. Sign the Assembly when compiling it (and give it a password!). This is sometimes referred to as giving it a Strong Name.

     If the Assembly isn't signed, and you don't have the source code to recompile it, and it doesn't have any other Assemblies that reference it, then you can still sign it by following the instructions in the first half of this blog post: http://ianpicknell.blogspot.com/2009/12/adding-strong-name-to-third-party.html

  2. Create an Asymmetric Key in [master] from the DLL of that Assembly
  3. Create a Login from that Asymmetric Key
  4. Grant that Login one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY (the UNSAFE ASSEMBLY permission also allows Assemblies to be marked as EXTERNAL_ACCESS, so you don't need both permissions)
  5. Create the Assembly in whichever Database(s) it should exist in
  6. Do NOT set the TRUSTWORTHY property of the Database(s) containing this Assembly to ON. TRUSTWORTHY can remain as OFF.

• The NOT preferred approach

  1. Alter the Database(s) where the Assembly should exist to be TRUSTWORTHY ON
  2. Make sure that the Login of the owner of the Database(s) containing this Assembly have one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY.
     
     If the owner of the Database(s) is sa, or any other Login that is in the sysadmin fixed server Role, then you don't need to worry about having one of these two permissions explicitly set as they are implied by the sysadmin Role.
  3. Try to avoid this approach, if at all possible :-)

Answer 2

_{Community Wiki answer generated from a comment by the question author}

Found the answer. Turns out, the CLR Assembly was used in 2 different databases. Needed to make sure that both had trustworthy bit turned on. Also, it is obviously doing something other than string splitting because I needed to create the assembly as UNSAFE because it says that it can't use partially trusted callers.