Assembly 'system.net.http, version=4.0.0.0' was not found in the SQL catalog error

Nguyen, David V 1

I created a SQL C# CLR Stored Procedure project and when trying to publish it the SQL Server (15.0.4335.1) I got this error:

(47,1): SQL72014: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'system.net.http, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.

(47,0): SQL72045: Script execution error. The executed script:

CREATE ASSEMBLY [FUSEAutomation]

AUTHORIZATION [dbo]
An error occurred while the batch was being executed.

I write c# code with these references:


powershell
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net.Http;
using System.Net.Http.Headers;
using System.ComponentModel.DataAnnotations;
using System.Collections.Generic;
using System.Text;
using Newtonsoft.Json;

I am using HttpClient class to call web api.

Am I not using the right version of System.Net.Http? If so what is the verion should I be

using? I am using Visual Studio 2017.

LucyChenMSFT-4874 1,580 Reputation points

2024-04-22T06:55:09.1266667+00:00

Hi @Nguyen, David V,

We haven't heard any response from you yet.

Is this issue solved now? If you have any questions, please feel free to share with us.

And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thanks for your understanding!

3 answers

Erland Sommarskog 102.4K

Not all .NET libraries are directly supported in SQL Server. See this Learn topic for a list of supported libraries/namespaces. System.Net is not among those.

As discussed in the same topic, you can still use these unsupported libraries, but you need to load them with CREATE ASSEMBLY.

Nguyen, David V 1 Reputation point

2024-03-29T15:47:15.7333333+00:00

This is my first time doing this. May I ask how to go about doing that? Right now when I publish the project, I was presented with a dialog , I would select the database and then click the Publish button.
Erland Sommarskog 102.4K Reputation points

2024-03-29T16:37:51.98+00:00

For the Visual Studio part, maybe someone else can fill in. Visual Studio goes way over my head. The few times I write CLR code for SQL Server, these are short things, less than 200 lines of code, and I compile from the command line.

Nguyen, David V 1

I tried to run this script from Microsoft SQL Management Studio:

create assembly [system.net.http]
from 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll'
with permission_set = unsafe;
go

but I got an error:

Warning: The Microsoft .NET Framework assembly 'system.net.http, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
Msg 10327, Level 14, State 1, Line 6
CREATE ASSEMBLY for assembly 'System.Net.Http' failed because assembly 'System.Net.Http' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly.

Nguyen, David V 1

I tried to run this script from Management Studio

create assembly [system.net.http]
from 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll'
with permission_set = unsafe;
go

And I got this error:

Warning: The Microsoft .NET Framework assembly 'system.net.http, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
Msg 10327, Level 14, State 1, Line 6
CREATE ASSEMBLY for assembly 'System.Net.Http' failed because assembly 'System.Net.Http' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly.

Erland Sommarskog 102.4K Reputation points

2024-03-29T17:14:10.36+00:00

What it says. Loading an unsafe assembly requires server-level permission. And since a couple of versions back, all assemblies are considered unsafe when the setting "clr strict security" is in force, and it is set by default for new instances of SQL Server. This is required, since an assembly potentially could have detrimental effects on the entire server.

Or in short, using the CLR inside SQL Server comes with quite a bit of red tape.

The easiest way is probably sp_add_trusted_assembly. You need to compute a SHA_512 for the file. There's probably more than one way to do it. What I have done is to load the DLL as a blob with OPENROWSET(BULK) and use the HASH_BYTES function. But that is because I only know my SQL Server hammer. There must be ways to compute hashes outside SQL Server as well.

Nguyen, David V 1

I copied the value from the publish fail error and run this script.

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;

declare @assembly varbinary(max) =0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001DED06660000000000000000E00022200B013000001A000000060000000000003A380000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000E83700004F00000000400000C002000000000000000000000000000000000000006000000C000000B03600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000004018000000200000001A000000020000000000000000000000000000200000602E72737263000000C00200000040000000040000001C0000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000020000000000000000000000000000040000042000000000000000000000000000000001C380000000000004800000002000500682400004812000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300300F0020000010000110072010000700A140B7290000070730800000A0C00086F0900000A000608730A00000A0D00730B00000A13041104096F0C00000A00730D00000A0B1104076F0E00000A2600DE0B092C07096F0F00000A00DC00DE0B082C07086F0F00000A00DC072C10076F1000000A6F1100000A16FE022B01161305110539720200000072C00000701306731200000A13070011071106731300000A6F1400000A0011076F1500000A6F1600000A0011076F1500000A6F1700000A6F1800000A0011076F1500000A6F1700000A720C010070731900000A6F1A00000A0000076F1000000A6F1B00000A130838D001000011086F1C00000A740D0000011309001109722E0100706F1D00000A6F1E00000A130A110972400100706F1D00000A6F1E00000A130B110972660100706F1D00000A6F1E00000A130C110B280100002B130D110D282000000A282100000A720C010070732200000A130E11077284010070110E6F2300000A6F2400000A130F7E2500000A1310110F6F2600000A131111112C13110F6F2700000A6F2800000A6F2900000A13101110282A00000A16FE01131211123917010000007290000070730800000A13130011136F0900000A0072A20100700A061113730A00000A131400001114176F2B00000A0011146F2C00000A72E10200701F0C6F2D00000A2611146F2C00000A72F50200701F0C6F2D00000A2611146F2C00000A721D0300701F0C6F2D00000A2611146F2C00000A723D0300701F0C6F2D00000A2611146F2C00000A72E10200706F2E00000A110A6F2F00000A0011146F2C00000A72F50200706F2E00000A110B6F2F00000A0011146F2C00000A721D0300706F2E00000A110C6F2F00000A0011146F2C00000A723D0300706F2E00000A11106F2F00000A0011146F3000000A2600DE0613150000DE0000DE0D11142C0811146F0F00000A00DC00DE0D11132C0811136F0F00000A00DC000011086F3100000A3A24FEFFFFDE1611087511000001131611162C0811166F0F00000A00DC00DE0D11072C0811076F0F00000A00DC002A41AC0000020000002400000023000000470000000B00000000000000020000001400000041000000550000000B0000000000000000000000C9010000C900000092020000060000001000000102000000C8010000D30000009B0200000D0000000000000002000000AF010000FC000000AB0200000D0000000000000002000000E5000000E3010000C80200001600000000000000020000008C00000055020000E10200000D000000000000002202283200000A002A1E027B010000042A2202037D010000042A1E027B020000042A2202037D020000042A1E027B030000042A2202037D030000042A1E027B040000042A2202037D040000042A1E027B050000042A2202037D050000042A1E027B060000042A2202037D060000042A0042534A4201000100000000000C00000076342E302E33303331390000000005006C000000B4050000237E0000200600001807000023537472696E677300000000380D00005C0300002355530094100000100000002347554944000000A4100000A401000023426C6F6200000000000000020000015715A2090908000000FA013300160000010000002A00000004000000060000001000000006000000320000001F0000000100000002000000060000000C00000003000000010000000600000001000000000069030100000000000600CD0203050600ED02030506008802F0040F0023050000060057068A030A00B702B5040A00BB013D000A000A0469060A00720169060A00A60469060E007F0645040600D004B0050A00F2063D000E00A20645040E00A7014504060018048A030600C5018A0306006D02030506004402F00406009C02F00406002400800012005B028A050A00FD0322040A00980422040A00EB033D000A0021023D00160065038A030E00E705C3050E00DB05C3050E000800C3050E001503C3051A00D106350406005303E6060600010043050E00B006450406005E038A030A00680122040A00F7013D000A00D40369060A008B0469060A00E9013D000A007F04220400000000340000000000010001000100100032050000150001000100010010004900000015000100030001001000B2030000150004000A0001000901CD000100430136010100D700CD0001002401CD000100BB00CD000100F000CD00502000000000960003023E010100F823000000008618EA0406000100012400000000860896038E0001000924000000008608A4033E00010012240000000086085A05420102001A2400000000860872054B0102002324000000008608D1018E0003002B24000000008608DD013E000300F823000000008618EA040600040034240000000086085B048E0004003C240000000086086D043E000400452400000000860862008E0005004D2400000000860871003E00050056240000000086083B038E0006005E2400000000860847033E000600F823000000008618EA04060007000000010035030000010035030000010035030000010035030000010035030000010035030900EA0401001100EA0406001900EA040A003100EA0406009100EA040600A100EA041000B100EA0406004100EA043E00B900910306004900EA0443005100EA04060051007D014A003900EA040600C1007C03500089003C020600390033065600D100BC065B005900EA040600D900EA043E00590023065F005900FA056500E90055040600E100C6066A000C0055040600F900EA043E000C00B7007A00D100DC04800061008A06850069008103890029005C038E0001014C06920001013C069E0009012B00A3007100EA04A9005900AD00B20014005E06C80021011207CD0079008F01D00079009606D40019019B00DA001C005E06C80021010A07EA002901F301EF0049001406F6003901B700FC0039018103060151010B030D012901FA065B006100DD06D0002900EA04060020002300930121002B00930121003300980129003B0093012E000B0062012E0013006B012E001B008A0141002B00930141003300980149003B00930160002B00930161002B00930161003300980169003B00930180002B00930181002B009301810033009801A0002B009301A1002B009301A10033009801C0002B009301C1002B009301C10033009801E0002B00930100012B00930140012B00930160012B00930180012B009301A0012B009301C0012B009301E0012B009301160003000100040004000000A80355010000760559010000E101550100007104550100007500550100004B03550102000300030001000400030002000500050001000600050002000700070001000800070002000A00090001000B00090002000C000B0001000D000B0002000E000D0001000F000D007300C000E300048000000000000000000000000000000000C5030000040000000000000000000000120159000000000004000000000000000000000012013D00000000000400000000000000000000001B0145040000000004000000000000000000000024018A050000000004000000000000000000000012018A03000000000D00000000000000000000002D013504000000003F0099000000005461736B6031004874747048656164657256616C7565436F6C6C656374696F6E6031004C6973746031006765745F55544638003C4D6F64756C653E0053797374656D2E4461746100467573655265717565737444617461006D73636F726C6962006765745F47656E6572616C526563007365745F47656E6572616C5265630053797374656D2E436F6C6C656374696F6E732E47656E6572696300526561644173537472696E674173796E6300506F73744173796E6300416464003C47656E6572616C5265633E6B5F5F4261636B696E674669656C64003C4170704E616D653E6B5F5F4261636B696E674669656C64003C46696E64696E673E6B5F5F4261636B696E674669656C64003C416363657373696F6E3E6B5F5F4261636B696E674669656C64003C4F62784964656E7469666965723E6B5F5F4261636B696E674669656C64003C467573655265636F6D6D656E646174696F6E733E6B5F5F4261636B696E674669656C64004462436F6D6D616E640053716C436F6D6D616E64007365745F53656C656374436F6D6D616E64006765745F497353756363657373537461747573436F64650048747470526573706F6E73654D65737361676500446174615461626C650049446973706F7361626C65006765745F4170704E616D65007365745F4170704E616D650053716C446254797065007365745F436F6D6D616E645479706500465553454175746F6D6174696F6E53746F72656450726F63656475726500496E7465726E616C44617461436F6C6C656374696F6E4261736500446973706F736500446562756767657242726F777361626C65537461746500526571756972656441747472696275746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C6541747472696275746500446562756767657242726F777361626C654174747269627574650053716C50726F63656475726541747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465007365745F56616C7565004D6564696154797065576974685175616C69747948656164657256616C75650076616C7565006765745F46696E64696E67007365745F46696E64696E6700456E636F64696E6700546F537472696E670055726900465553454175746F6D6174696F6E2E646C6C0046696C6C006765745F4974656D0053797374656D004F70656E006765745F416363657373696F6E007365745F416363657373696F6E00467573655265636F6D6D656E646174696F6E00465553454175746F6D6174696F6E0053716C506172616D65746572436F6C6C656374696F6E0044617461526F77436F6C6C656374696F6E004462436F6E6E656374696F6E0053716C436F6E6E656374696F6E00457863657074696F6E0053797374656D2E446174612E436F6D6D6F6E004E6577746F6E736F66742E4A736F6E0053797374656D2E4E65742E4874747000436C656172006765745F4F62784964656E746966696572007365745F4F62784964656E746966696572004462506172616D657465720053716C506172616D6574657200446244617461416461707465720053716C4461746141646170746572004D6963726F736F66742E53716C5365727665722E5365727665720049456E756D657261746F7200476574456E756D657261746F72002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053746F72656450726F636564757265730053797374656D2E546872656164696E672E5461736B73006765745F467573655265636F6D6D656E646174696F6E73007365745F467573655265636F6D6D656E646174696F6E730053797374656D2E436F6D706F6E656E744D6F64656C2E44617461416E6E6F746174696F6E730053797374656D2E436F6C6C656374696F6E730053797374656D2E4E65742E487474702E4865616465727300487474704865616465727300487474705265717565737448656164657273006765745F44656661756C745265717565737448656164657273006765745F506172616D6574657273007365745F4261736541646472657373006765745F526F77730053657269616C697A654F626A65637400446573657269616C697A654F626A656374006765745F526573756C740053797374656D2E446174612E53716C436C69656E740048747470436C69656E74006765745F43757272656E74006765745F436F6E74656E7400537472696E67436F6E74656E740048747470436F6E74656E74006765745F436F756E74006765745F416363657074004A736F6E436F6E76657274004D6F76654E6578740053797374656D2E546578740044617461526F7700457865637574654E6F6E51756572790049734E756C6C4F72456D7074790000808D530065006C00650063007400200054006500730074004E0061006D0065002C0020005200650063006F006D006D0065006E0064006100740069006F006E004F007200690067002C0020004500780070006500630074006500640052006500730075006C0074002000660072006F006D0020004600550053004500540065007300740043006100730065007300002F43006F006E007400650078007400200043006F006E006E0065006300740069006F006E003D007400720075006500004B680074007400700073003A002F002F00730074006100670065002D0065006D0069007700650062006100700069002E0070006100720074006E006500720073002E006F00720067002F0001216100700070006C00690063006100740069006F006E002F006A0073006F006E00001154006500730074004E0061006D00650000255200650063006F006D006D0065006E0064006100740069006F006E004F00720069006700001D4500780070006500630074006500640052006500730075006C007400001D6100700069002F0066007500730065002F0069006E0070007500740000813D49006E007300650072007400200069006E0074006F0020004600550053004500540065007300740052006500730075006C0074007300280054006500730074004E0061006D0065002C0020005200650063006F006D006D0065006E0064006100740069006F006E004F007200690067002C0020004500780070006500630074006500640052006500730075006C0074002C002000410063007400750061006C0052006500730075006C00740029002000760061006C007500650073002800400054006500730074004E0061006D0065002C00200040005200650063006F006D006D0065006E0064006100740069006F006E004F007200690067002C00200040004500780070006500630074006500640052006500730075006C0074002C0020004000410063007400750061006C0052006500730075006C00740029000013400054006500730074004E0061006D006500002740005200650063006F006D006D0065006E0064006100740069006F006E004F00720069006700001F40004500780070006500630074006500640052006500730075006C007400001B4000410063007400750061006C0052006500730075006C00740000000000C7916D8133CF9D4599F1EC6A7CE5FEE40004200101080320000105200101111105200101114D2707170E121D122112251229020E122D123112350E0E0E120C1239123D0E02021221122512411245042001010E062002010E122105200101122505200108121D04200012650320000805200101126D042000127108200015127901127D0615127901127D05200101130004200012310320001C0420011C0E0320000E061001011E000E040A01120C0400010E1C050000128085082003010E1280850E0D20021512808901123D0E12808D071512808901123D042000130002060E0320000205200012808D08200015128089010E0615128089010E040001020E0620010111809905200012809D0920021280A10E1180A50620011280A10E042001011C08B77A5C561934E08908B03F5F7F11D50A3A0831BF3856AD364E350830AD4FE6B2A6AEED070615125501121003000001082000151255011210092001011512550112100328000E0828001512550112100801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000080100000000000000000000000000001DED066600000000020000001C010000CC360000CC18000052534453287EBF92654AEF4A95DACA477DCC9F2B01000000433A5C5661756C745C465553454175746F6D6174696F6E5C465553454175746F6D6174696F6E5C6F626A5C44656275675C465553454175746F6D6174696F6E2E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001038000000000000000000002A3800000020000000000000000000000000000000000000000000001C380000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000640200000000000000000000640234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004C4010000010053007400720069006E006700460069006C00650049006E0066006F000000A001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000046001300010049006E007400650072006E0061006C004E0061006D006500000046005500530045004100750074006F006D006100740069006F006E002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004E00130001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000046005500530045004100750074006F006D006100740069006F006E002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C0000003C3800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

declare @hash varbinary(64) = HASHBYTES('SHA2_512', @assembly);

EXEC sys.sp_add_trusted_assembly @hash, N'System.Net.Http Assembly';

It seems to work but It still failed the same error when I try to re-publlsh the project

Erland Sommarskog 102.4K Reputation points

2024-03-29T17:37:40.59+00:00

Did you use the @assembly variable to create the assembly for System.Net.Http as well?
Nguyen, David V 1 Reputation point

2024-03-29T17:42:39.2133333+00:00

No.

What I have done so far are:

step in comment 3. It did not work.

then I did the step in comment 6.
Erland Sommarskog 102.4K Reputation points

2024-03-29T17:52:24.7133333+00:00

Not sure what you mean with comment 3 and comment 6 here.

But I like to point out that just because you can do something, does not mean that it is a good idea to do it. I don't know exactly what System.Net.Http does, but there certainly are assemblies that do not play well with SQL Server. Specifically, anything that can put up any sort of desktop interaction is highly unsuitable.

So I am not inclined to spend a whole of time to try to figure out how you can load System.Net.Http and your own assembly, because in the end it may prove to not work well at all. Generally, if you aim to load a .NET assembly which is not on the list of supported assemblies, you better know quite well what you are doing.

I have some text here: https://www.sommarskog.se/grantperm-appendix.html#CLR but it is not really the best beginner material, and it also aims at a different situation. It does however give you some ideas with the challenges. For more details, you could turn to Google. But beware that you have entered the land of "Advanced".
Nguyen, David V 1 Reputation point

2024-03-29T17:55:47.2633333+00:00

System.Net.Http allow me to call web api.

Thank you for your help so far.
LucyChenMSFT-4874 1,580 Reputation points

2024-04-01T07:28:49.8266667+00:00

Hi @Nguyen, David V,

This article is about how to create assembly, hope this can help you understand.

In addition, do you have further questions that we can offer more support? If Erland Sommarskog's answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Best regards,

Lucy Chen

Nguyen, David V 1

No my issue has not been resolved. Here what I have done so far:

If I used this script to create an Assembly

create assembly [system.net.http]
from 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll'
with permission_set = unsafe;
go

Then I got this error:

Warning: The Microsoft .NET Framework assembly 'system.net.http, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
Msg 10327, Level 14, State 1, Line 3
CREATE ASSEMBLY for assembly 'System.Net.Http' failed because assembly 'System.Net.Http' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly.

Then I used the script below to create the assembly using the sp_add_trusted_assembly as suggested by the error above.

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;
declare @assembly varbinary(max) =0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300DCF40A660000000000000000E00022200B013000001A000000060000000000003A380000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000E83700004F00000000400000C002000000000000000000000000000000000000006000000C000000B03600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000004018000000200000001A000000020000000000000000000000000000200000602E72737263000000C00200000040000000040000001C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000
declare @hash varbinary(64) = HASHBYTES('SHA2_512', @assembly);
EXEC sys.sp_add_trusted_assembly @hash, N'system.net.http, version=4.2.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.';

The trusted System.Net.Http assembly created. How do i know? Because if the used the script above again. SSMS told me it is already trusted (see below)

Configuration option 'clr enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 10345, Level 16, State 1, Procedure sys.sp_add_trusted_assembly, Line 1 [Batch Start Line 8]
The assembly hash '0xE1D04DC51869A558CCAECBE7A07CD3C1E7D8F1FA096A1E1D83EA2FAE91A27DA940659353E6E3BC66238FCC3E77854B1C289EB3EA3E56F9F97BC84B62122A0C69' is already trusted.

The problem is when I try to publish my CLR C# Stored Procedure again I got the same error

Creating [FUSEAutomation]...
(47,1): SQL72014: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'system.net.http, version=4.2.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.
(47,0): SQL72045: Script execution error.  The executed script:
CREATE ASSEMBLY [FUSEAutomation]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300DCF40A660000000000000000E00022200B013000001A000000060000000000003A380000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000E83700004F00000000400000C002000000000000000000000000000000000000006000000C000000B03600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000004018000000200000001A000000020000000000000000000000000000200000602E72737263000000C00200000040000000040000001C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000
An error occurred while the batch was being executed.

I am not sure where to go from here.

Erland Sommarskog 102.4K Reputation points

2024-04-01T20:00:28.34+00:00

I note that when you try to create the system.net.http assembly you get:

Warning: The Microsoft .NET Framework assembly 'system.net.http, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.'

The error message you get when attempting to create your own assembly:

(47,1): SQL72014: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'system.net.http, version=4.2.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.

Again, I like to remind you that you are entered a very advanced path, and it is not really apt for beginners.

LucyChenMSFT-4874 1,580

Hi @Nguyen, David V,

I checked the error messages you provided in comments. Just like Erland said, 'it is not really apt for beginners.'. However, I’ve found an issue similar to yours, hope this can help you well. User's image

Anyway, it’s always good to try and explore hard. Hope your issue will be solved well.

Best regards,

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Samyn Axel 0

Your answer relates to a web project while the OP (and myself) is dealing with a SQL Server Database Project and SQL CLR C# User Defined Functions. I don't think the solution applies in this case since there is no web.config file in the default solution.

I was actually able to reproduce the issue. I looked up a bit and tried different approaches to try to fix this while working with SQL Server 2014, Visual Studio 2022 and the .NET Framework 4.7.2.

I did try to add the assembly directly via the Sql Server prompt "New Assembly" and providing the default location of the assembly "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETFramework\v4.7.2\System.Net.Http.dll". I was asked to specify either "External Access" or "Unrestricted" as permission set. and encounter the following error with both options :

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for SqlAssembly 'System.Net.Http'.  (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.200.48044.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+SqlAssembly&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

CREATE ASSEMBLY for assembly 'System.Net.Http' failed because assembly 'System.Net.Http' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.Net.Http.SR::.ctor][mdToken=0x6000001][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_ResourceManager][mdToken=0x6000002][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_Culture][mdToken=0x6000003][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::set_Culture][mdToken=0x6000004][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_argument_empty_string][mdToken=0x6000005][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_absolute_baseaddress_required][mdToken=0x6000006][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_content_headers][mdToken=0x6000007][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_execution_error][mdToken=0x6000008][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_http_baseaddress_required][mdToken=0x6000009][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_invalid_requesturi][mdToken=0x600000a][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_request_already_sent][mdToken=0x600000b][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_request_headers][mdToken=0x600000c][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_response_headers][mdToken=0x600000d][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_send_canceled][mdToken=0x600000e][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_send_completed][mdToken=0x600000f][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net...
Warning: The Microsoft .NET Framework assembly 'system.net.http, version=4.2.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details. (Microsoft SQL Server, Error: 6218)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-6218-database-engine-error

------------------------------
BUTTONS:

OK
------------------------------

Apparently, there is an issue with the assembly verification process.

I also tried to "Publish" from within Visual Studio 2022.

To do so, I did provide the server information and successfully tested the connection. Unfortunatly, the process also fails with the same error message :

Creating Assembly [SqlServer.CustomExtensions]...
(47,1): SQL72014: Framework Microsoft SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'system.net.http, version=4.2.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.

I also found that, when selecting the System.Net.Http assembly under the References section in the Solution Explorer, there is a property "Generate SQL Script" defaulting to false in the property pane. I changed that to true but with no better outcomes. I also set the Permission Set for the assembly in the same Pane to External, but again, no success.

Any ideas ?

Erland Sommarskog 102.4K Reputation points

2024-05-15T21:30:40.12+00:00

House rules: don't piggyback on old threads, but please ask a new Question.

Samyn Axel 0 Reputation points

2024-05-16T08:53:59.3233333+00:00

I encountered the same issue.

After reading some more posts, I realized that the .NET version used by SQL Server is fixed for a certain SQL Server Version. That means that for my SQL Server 2014, I had to use .NET Framework 4.0 and the libraries that are available in that framework to make things work.

I asked chatgpt about your particular version (SQL Server 2019) and found that it had to use .NET Framework 4.6.

If I remember correctly, System.Net.Http was introduced starting .NET Framework 4.5 so you should be able to reference that namespace, but I guess you'll still have to add the assembly to your SQL Server which is a step i did not have to, since I used older classes built in more classic assemblies (System.Net) to achieve my network queries.

I hope this will help you find a solution !
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Assembly 'system.net.http, version=4.0.0.0' was not found in the SQL catalog error

3 answers