Unable to load assembly on Azure Sql that uses HttpClient object

Question

Hi Team,

We have developed a UDF in C# that sends a request to Http rest endpoint. For that we have used HttpClient object in our code. Getting below error while creating assembly using our dll.

 

Msg 10301, Level 16, State 1, Line 1

 

Assembly 'Mydll' references assembly 'system.net.http, version=4.2.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', 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. 

 

We know WebClient can be used instead of HttpClient but we wanted to leverage asynchronous functionality.

 

Below is the reference code:

 
using System;
 
using System.Data.SqlTypes;
 
using Microsoft.SqlServer.Server;
 
using System.Net.Http;
 
using System.Threading.Tasks;
 
 
public class MyClass
 
{
 
   private static readonly HttpClient httpClient = new HttpClient();
 
   private const string MyEndPointUrl = "https://10.10.10.10:1234/api/yourfunction";
 
   private const string apiKey = "your-api-key";
 
 
   static MyEndPointInvoker()
 
   {
 
       // Set up HttpClient defaults here if needed
 
       httpClient.DefaultRequestHeaders.Add("x-functions-key", apiKey);
 
   }
 
 
   [SqlFunction]
 
   public static SqlString InvokeMyEndPoint(SqlString input)
 
   {
 
       try
 
       {
 
           // Asynchronous call in a synchronous context
 
           var response = Task.Run(() => PostDataAsync(input.Value)).Result;
 
           if (response.IsSuccessStatusCode)
 
           {
 
               return new SqlString(response.Content.ReadAsStringAsync().Result);
 
           }
 
           else
 
           {
 
               return new SqlString($"Error: {response.StatusCode} - {response.ReasonPhrase}");
 
           }
 
       }
 
       catch (Exception ex)
 
       {
 
           return new SqlString($"Error: {ex.Message}");
 
       }
 
   }
 
   private static async Task

Answer 1

Far from all .NET assemblies are available in SQL Server. Those that are missing you will need to load yourself - on your own risk. And some assemblies may be blacklisted and be accepted all. I don't know off the top of my head what is the case here. Particularly since this Azure Managed Instance, there may be a stricter régime than in on-prem SQL Server.