Make assembly from SQL 2016 work in SQL 2017 (ex: Calling Math Parser from SQL Server 2017)

Question

I have a stored procedure that needs some complex math expression evaluation. There is a formula and this stored procedure evaluates its value.

It calls an UDF defined as below:

ALTER FUNCTION [dbo].[udfComputeMath]
    (@inputString [NVARCHAR](MAX))
RETURNS [NVARCHAR](4000) 
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SMP_Assembly].[SuperMathParser.MathParser].[ComputeMath] 

This is quite old code, it worked just fine since SQL Server 2008 R2 (at least), up to 2016.

However now we try to update our DB to SQL Server 2017, and this no longer works.

I get the following error:

Msg 10314, Level 16, State 11, Procedure GetPermitTypeFeesByPermitID, Line 88 [Batch Start Line 0]
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted. 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 'supermathparser, 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)

Any idea how can make this work again in SQL Server 2017?

Thank you

Answer 1

My assumption is that the database where you try to call this on has just been restored to a SQL Server 2017 database, is that correct? If so, the function you try to call is either developed by yourself (company) or is a third party assembly - it is not a built-in MS assembly (the assembly id - 65536 - indicates this as well).

My guess is that the problem is what digital.aaron points to in his comments, it has to do with the changed CLR security model in SQL Server 2017. You can read more about it in this blogpost, and here a way to easily:ish fix it.

Hope this helps!

Niels