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.