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.

LucyChen-MSFT 965 Reputation points Microsoft Vendor

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!

2 answers

Erland Sommarskog 101K MVP

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 101K Reputation points MVP

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 101K Reputation points MVP

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 101K Reputation points MVP

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 101K Reputation points MVP

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.
LucyChen-MSFT 965 Reputation points Microsoft Vendor

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 101K Reputation points MVP

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.

LucyChen-MSFT 965 Reputation points Microsoft Vendor

2024-04-05T03:07:05.35+00:00

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.

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
Please sign in to rate this answer.

0 comments No comments
Sign in to comment