How to make a remote web call from inside a custom assembly in a SQL Server project

Question

I developed a trigger in a SQL Server project in Visual Studio 2022. The assembly is called `SQL_Server.dll.

When a row is inserted in certain SQL Server table called USERINFO, I need that data to be sent to a remote server by making a REST API call.

The code of the trigger is this, in C#:

public static void UserManage()
{
    SqlTriggerContext ctx = SqlContext.TriggerContext;
    switch (ctx.TriggerAction)
    {
        case TriggerAction.Insert:
        case TriggerAction.Update:
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                DataSet users = new DataSet();
                using (SqlConnection sqlCnn = new SqlConnection("context connection=true"))
                {
                    sqlCnn.Open();
                    SqlCommand cmdRead = new SqlCommand("SELECT USERID, BADGENUMBER, NAME, SSN, CardNo, STREET, PAGER FROM INSERTED", sqlCnn);
                    adapter.SelectCommand = cmdRead;
                    adapter.Fill(users);
                    int u = SendUsers(users);
                    if (u > 0)
                        SqlContext.Pipe.Send($"Envío correcto de {u} usuario(s).");
                    else
                        SqlContext.Pipe.Send("No se envió ningún usuario.");
                }
            }
            break;
    }
}

private static int SendUsers(DataSet users)
{
    database.AddUsersAsync(toSend).Result
}

database is a class instance defined in a referenced assembly, called DbPlugin.dll`

AddUsersAsync is defined this way:

    public async Task<bool> AddUsersAsync(List<User> users)
    {
        if (users == null || users.Count == 0)
            return false;

        bool result = true;
        foreach (var user in users)
        {
            result &= await AddUserAsync(user);
        }
        return result;
    }

Finally, AddUserAsync is a method that does the actual REST API call. For this question, the code is irrelevant so please assume it works.

I have signed the main SQL_Server.dll assembly and the DbPlugin.dll assembly.

SQL_Server.dll assembly SQLCLR options were defined this way:

I have signed all assemblies. I used .SNK signatures and then .PFX signatures.

Well... the steps to install the assembly were these:

EXEC sp_configure 'clr enabled'
 
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
 
EXEC sp_configure 'clr enabled'

USE master
GO

-- SQL_Server.dll

CREATE ASYMMETRIC KEY SQL_CLRKey FROM EXECUTABLE FILE = 'path-to\SQL_Server.dll';
GO

CREATE LOGIN SQL_CLRKey_Login FROM ASYMMETRIC KEY SQL_CLRKey
GO

GRANT EXTERNAL ACCESS ASSEMBLY TO SQL_CLRKey_Login
GO

-- DbPlugin.dll

CREATE ASYMMETRIC KEY DbPlugin_CLRKey FROM EXECUTABLE FILE = 'path-to\DbPlugin.dll';
GO

CREATE LOGIN DbPlugin_CLRKey_Login FROM ASYMMETRIC KEY DbPlugin_CLRKey
GO

GRANT UNSAFE ASSEMBLY TO DbPlugin_CLRKey_Login
GO

USE MY_DB
GO

CREATE USER SQL_CLRKey_Login FOR LOGIN SQL_CLRKey_Login
GO

CREATE USER DbPlugin_CLRKey_Login FOR LOGIN DbPlugin_CLRKey_Login
GO

CREATE ASSEMBLY [SQL_Server] FROM
     'path-to\SQL_Server.dll' WITH PERMISSION_SET=EXTERNAL_ACCESS
GO

CREATE TRIGGER TR_UserManage    
     ON dbo.USERINFO    
     FOR INSERT, UPDATE, DELETE    
     AS EXTERNAL NAME [SQL_Server].[Triggers].UserManage
GO

Then, in SQL Server Manager, I issued this command:

insert into USERINFO (BADGENUMBER, NAME, SSN, PAGER)
values (88888888, 'Juan Pérez', '88.888.888-8', 1)

And this error was shown:

Msg 6522, Level 16, State 1, Procedure TR_UserManage, Line 1 [Batch Start Line 0] A .NET Framework error occurred during execution of user-defined routine or aggregate "TR_UserManage": System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All The demanded resources were: Synchronization, ExternalThreading

System.Security.HostProtectionException: en Triggers.SendUsers(DataSet users) en Triggers.UserManage() . The statement has been terminated.

Completion time: 2022-10-23T18:54:46.7043805-03:00

How can I do it?