Issue loading clr assembly into sql server that uses system.text.json

Question

Have a clr assembly that references System.Text.Json (.net 4.8 with SQL Server 14), when I try to load it into SQL Server (CREATE ASSEMBLY) I get:

Assembly 'my assembly' references assembly 'system.text.json, version=4.0.1.0, culture=neutral, publickeytoken=cc7b13ffcd2ddd51.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

Updated the server with the latest .net, obviously loading the entire .net framework into sql server isn't a good idea :)

Answer 1

I got it to work for both Newtonsoft.Json and System.Text.Json on SQL 2019 on Windows 2019. It doesn't require Trustworthy ON.

For Newtonsoft.Json:

For most recent version:

https://gist.github.com/ltctech/2c6189dac73e7fa0967d6b23ef478790

/*

# Assumes SQL 2019 on Windows Server 2019
# Trustworthy ON not required

# Get SHA512 hashes using powershell
# (Get-FileHash 'c:\temp\some-assembly.dll' -Algorithm SHA512).Hash

$assemblies = @(
    'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\SMDiagnostics.dll'
    'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ServiceModel.Internals.dll'
    'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll'
    'C:\temp\newtonsoft\Newtonsoft.Json.dll'
)

foreach ($assemblyPath in $assemblies) {
    $hash = Get-FileHash $assemblyPath -Algorithm SHA512
    Write-Output "$($assemblyPath): $($hash.Hash)"
}

*/

select * from sys.trusted_assemblies

--this is server wide, don't run for each DB

-- C:\Windows\Microsoft.NET\Framework64\v4.0.30319\SMDiagnostics.dll
EXEC sp_add_trusted_assembly 0x6E0972CD090A43CE6A69529B17E6AD2D9C5647D44A440558DD73BB048D4ACA12A58B08916E7BA49EAF81439E5BE33A373DBD97AD53DE4F177AC55F8C49396808, N'SMDiagnostics-NET4.8';

-- C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ServiceModel.Internals.dll
EXEC sp_add_trusted_assembly 0xAE9FA012C5E524760CABEADB3F89EAB5EB02B273E9A9E00087C58E6865C3704C9B1E866F24C9058DF3BE278A3AC7C76BBF64B03548908EE20D823AFA08958139, N'System.ServiceModel.Internals-NET4.8';

-- C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll
EXEC sp_add_trusted_assembly 0xC82748C97B09EAA107F2F24A271845869EC06E74054A9032FAA7B3CDBA8744064B6A5C9CDBC632455B85BEAE4D71A22EEC65B9DE81D429A23C1CFD116732E83D, N'System.Runtime.Serialization-NET4.8';

-- https://nuget.info/packages/Newtonsoft.Json/13.0.3
EXEC sp_add_trusted_assembly 0x56EB7F070929B239642DAB729537DDE2C2287BDB852AD9E80B5358C74B14BC2B2DDED910D0E3B6304EA27EB587E5F19DB0A92E1CBAE6A70FB20B4EF05057E4AC, N'Newtonsoft.Json-13.0.3';

--this is per database
CREATE ASSEMBLY [SMDiagnostics]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\SMDiagnostics.dll'
WITH PERMISSION_SET = UNSAFE;

CREATE ASSEMBLY [System.ServiceModel.Internals]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ServiceModel.Internals.dll'
WITH PERMISSION_SET = UNSAFE;

CREATE ASSEMBLY [System.Runtime.Serialization]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll'
WITH PERMISSION_SET = UNSAFE;

CREATE ASSEMBLY [Newtonsoft.Json]
FROM 'C:\temp\newtonsoft\Newtonsoft.Json.dll'
WITH PERMISSION_SET = UNSAFE;

For System.Text.Json:

For most recent version:

https://gist.github.com/ltctech/c7836826e3c173b759253edc8f64c925

/*

# Assumes SQL 2019 on Windows Server 2019
# Trustworthy ON not required

# This article explains why the old and new version assembly swap works:
# https://techcommunity.microsoft.com/t5/sql-server-blog/assembly-redirection-and-clr-integration/ba-p/383100

# Get SHA512 hashes using powershell
# (Get-FileHash 'c:\temp\some-assembly.dll' -Algorithm SHA512).Hash

$assemblies = @(
    'C:\temp\JSON\Microsoft.Bcl.AsyncInterfaces.dll',
    'C:\temp\JSON\System.Buffers.dll',
    'C:\temp\JSON\System.Memory.dll',
    'C:\temp\JSON\System.Numerics.Vectors.dll',
    'C:\temp\JSON\System.Text.Encodings.Web.dll',
    'C:\temp\JSON\System.Threading.Tasks.Extensions.dll',
    'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ValueTuple.dll',
    'C:\temp\JSON\System.Text.Json.dll',
    'C:\temp\JSON\4-5-3\System.Runtime.CompilerServices.Unsafe.dll',
    'C:\temp\JSON\6-0-0\System.Runtime.CompilerServices.Unsafe.dll'
)

foreach ($assemblyPath in $assemblies) {
    $hash = Get-FileHash $assemblyPath -Algorithm SHA512
    Write-Output "$($assemblyPath): $($hash.Hash)"
}

*/

-- check what assemblies are trusted already
select * from sys.trusted_assemblies

-- this is server wide, don't run for each DB
-- these will change depending on the version used, generate new ones as needed

-- https://nuget.info/packages/Microsoft.Bcl.AsyncInterfaces/8.0.0
EXEC sp_add_trusted_assembly 0x7F207F2E3F9F371B465BCA5402DB0E5CEC3CB842A1F943D3E3DCEDC8E5D134F58C7C4DF99303C24501C103494B4F16160F86DB80893779CE41B287A23574EE28, N'Microsoft.Bcl.AsyncInterfaces-8.0.0';

-- https://nuget.info/packages/System.Buffers/4.5.1
EXEC sp_add_trusted_assembly 0x5FC7FEE5C25CB2EEE19737068968E00A00961C257271B420F594E5A0DA0559502D04EE6BA2D8D2AAD77F3769622F6743A5EE8DAE23F8F993F33FB09ED8DB2741, N'System.Buffers-4.5.1';

-- https://nuget.info/packages/System.Memory/4.5.5
EXEC sp_add_trusted_assembly 0x0199AE0633BCCFEAEFBB5AED20832A4379C7AD73461D41A9DA3D6DC044093CC319670E67C4EFBF830308CBD9A48FB40D4A6C7E472DCC42EB745C6BA813E8E7C6, N'System.Memory-4.5.5';

-- https://nuget.info/packages/System.Numerics.Vectors/4.5.0
EXEC sp_add_trusted_assembly 0x0B14A039CA67982794A2BB69974EF04A7FBEE3686D7364F8F4DB70EA6259D29640CBB83D5B544D92FA1D3676C7619CD580FF45671A2BB4753ED8B383597C6DA8, N'System.Numerics.Vectors-4.5.0';

-- https://nuget.info/packages/System.Text.Encodings.Web/8.0.0
EXEC sp_add_trusted_assembly 0x58F468C982AB66930FF37EFB5A941DB116E8C1AED66EBC23720A7B18F71BEBE1E929BEA76680294EDB25F430C23D520B8A87E3A22064C5993D0396819A21CBE7, N'System.Text.Encodings.Web-8.0.0';

-- https://nuget.info/packages/System.Threading.Tasks.Extensions/4.5.4
EXEC sp_add_trusted_assembly 0xDA7AB8C0100E7D074F0E680B28D241940733860DFBDC5B8C78428B76E807F27E44D1C5EC95EE80C0B5098E8C5D5DA4D48BCE86800164F9734A05035220C3FF11, N'System.Threading.Tasks.Extensions-4.5.4';

-- C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ValueTuple.dll
EXEC sp_add_trusted_assembly 0x374EA90EAEB8976F8865DBCAE5DAC85C417A771608923442DE61D79A373BBDB9A51D101997EA51994BE0CA78A6050D9647FEB34BFF71701E3FCDFF8742AAF42D, N'System.ValueTuple-NET-4.8';

-- https://nuget.info/packages/packages/System.Text.Json/8.0.3
EXEC sp_add_trusted_assembly 0x167237648CDEA96798044A424C5B09D239BDA4DD3E021D3456067C25EF2F9852CBC638ACA6DD3395B542E37DC36ED3B0308044E62736A7F9AFC0BBEBBCB80F4A, N'System.Text.Json-8.0.3';

-- https://nuget.info/packages/System.Runtime.CompilerServices.Unsafe/4.5.3
EXEC sp_add_trusted_assembly 0x26AF01CA25E921465F477A0E1499EDC9E0AC26C23908E5E9B97D3AFD60F3308BFBF2C8CA89EA21878454CD88A1CDDD2F2F0172A6E1E87EF33C56CD7A8D16E9C8, N'System.Runtime.CompilerServices.Unsafe-4.5.3';

-- https://nuget.info/packages/System.Runtime.CompilerServices.Unsafe/6.0.0
EXEC sp_add_trusted_assembly 0x441610D2B9F841D25494D7C82222D07E1D443B0DA07F0CF735C25EC82F6CCE99A3F3236872AEC38CC4DF779E615D22469666066CCEFED7FE75982EEFADA46396, N'System.Runtime.CompilerServices.Unsafe-6.0.0';

-- execute one at a time in order
-- this is per database

-- no dependencies
CREATE ASSEMBLY [System.ValueTuple]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ValueTuple.dll'
WITH PERMISSION_SET = UNSAFE;

-- no dependencies
CREATE ASSEMBLY [System.Numerics.Vectors]
FROM 'C:\temp\JSON\System.Numerics.Vectors.dll'
WITH PERMISSION_SET = UNSAFE;

-- no dependencies
CREATE ASSEMBLY [System.Buffers]
FROM 'C:\temp\JSON\System.Buffers.dll'
WITH PERMISSION_SET = UNSAFE;

-- no dependencies
-- use old version 4.5.3 here
CREATE ASSEMBLY [System.Runtime.CompilerServices.Unsafe]
FROM 'C:\temp\JSON\4-5-3\System.Runtime.CompilerServices.Unsafe.dll'
WITH PERMISSION_SET = UNSAFE;

-- depends on System.Runtime.CompilerServices.Unsafe 4.5.3 & System.Buffers
CREATE ASSEMBLY [System.Memory]
FROM 'C:\temp\JSON\System.Memory.dll'
WITH PERMISSION_SET = UNSAFE;

-- depends on System.Runtime.CompilerServices.Unsafe 4.5.3
CREATE ASSEMBLY [System.Threading.Tasks.Extensions]
FROM 'C:\temp\JSON\System.Threading.Tasks.Extensions.dll'
WITH PERMISSION_SET = UNSAFE;

-- depends on System.Threading.Tasks.Extensions
CREATE ASSEMBLY [Microsoft.Bcl.AsyncInterfaces]
FROM 'C:\temp\JSON\Microsoft.Bcl.AsyncInterfaces.dll'
WITH PERMISSION_SET = UNSAFE;

-- no dependencies
-- alter with new version 6.0.0 here
Alter ASSEMBLY [System.Runtime.CompilerServices.Unsafe]
FROM 'C:\temp\JSON\6-0-0\System.Runtime.CompilerServices.Unsafe.dll'
WITH PERMISSION_SET = UNSAFE;

-- depends on System.Runtime.CompilerServices.Unsafe 6.0.0 & System.Buffers & System.Memory
CREATE ASSEMBLY [System.Text.Encodings.Web]
FROM 'C:\temp\JSON\System.Text.Encodings.Web.dll'
WITH PERMISSION_SET = UNSAFE;

-- depends on Microsoft.Bcl.AsyncInterfaces & System.Text.Encodings.Web & System.Buffers 
-- & System.Memory & System.Runtime.CompilerServices.Unsafe 6.0.0 & System.Threading.Tasks.Extensions
-- & System.ValueTuple
CREATE ASSEMBLY [System.Text.Json]
FROM 'C:\temp\JSON\System.Text.Json.dll'
WITH PERMISSION_SET = UNSAFE;

Answer 2

Yep. I tried loading the assembly but it pulled me into the rabbit hole of .net's version of DLL h*ll where this was dependent on that, which was dependent on that...

One would THINK System.Text.Json is the Microsoft replacement for Newtonsoft, apparently not quite yet.

Thanks for the answers.

Answer 3

The error is due to system.text.json not being in the list of Supported .NET Framework Libraries.

You will have to load that DLL manually via CREATE ASSEMBLY (it will load dependent assemblies automatically) as UNSAFE. However, if that assembly is not available in .NET Framework, then I'm not sure you will be able to load it (or, even if it is available in .NET Framework but is a mixed-mode assembly -- i.e. contains unmanaged code).