C# SQL Server CLR Request error on functions GET and POST

Question

I followed the GitHub documentation to implement the http requests with the CURL extension, work in SQL Server 2008 R2, Visual Studio 2010 and .NET 3.5.

I managed to compile and sign correctly the .dll in visual studio, to then create the schemas and functions in SQL Server, since everything works correctly, I can perform GET and POST from SQL Server, however, when wanting to perform a GET or a POST at SABA API, it generates a series of errors.

A .NET Framework error occurred during execution of user-defined routine or aggregate "XGET": System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a send. ---> System.IO.IOException: Received an unexpected EOF or 0 bytes from the transport stream. System.IO.IOException: at System.Net.FixedSizeReader.ReadPacket(Byte[] buffer, Int32 offset, Int32 count) at System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest) at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest) at System.Net.Security.SslState.CheckCompletionBeforatextReceive(ProtocolTokat message, AsyncProtocolRequest asyncRequest) at System.Net.Security.SslState.StartSatdBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security.SslState.ForceAuthattication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest) at System.Net.Security.SslState.ProcessAuthattication(LazyAsyncResult lazyResult) at System.Net.TlsStream.CallProcessAuthattication(Object state) at System.Threading.ExecutionContext.runTryCode(Object userData) at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Net.TlsStream.ProcessAuthattication(LazyAsyncResult result)
at System.Net.TlsStream.Write(Byte[] buffer, Int32 offset, Int32 size) at System.Net.PooledStream.Write(Byte[] buffer, Int32 offset, Int32 size) at System.Net.ConnectStream.WriteHeaders(Boo ... System.Net.WebException: at System.Net.WebCliatt.DownloadDataInternal(Uri address, WebRequest& request) at System.Net.WebCliatt.DownloadString(Uri address) ...

This is the code of the Assembly

using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;
using System.Net;
using System.Threading;

public static class Curl
{
    [SqlFunction]
    [return: SqlFacet(MaxSize = -1)]
    public static SqlChars Get(SqlChars H, SqlChars url)
    {
        ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3 | SecurityProtocolType.Tls;
        var client = new WebClient();
        AddHeader(H, client);

        return new SqlChars(
                client.DownloadString(
                    Uri.EscapeUriString(url.ToSqlString().Value)
                    ).ToCharArray());
    }

    [SqlProcedure]
    public static void Post(SqlChars H, SqlChars d, SqlChars url)
    {
        var client = new WebClient();
        AddHeader(H, client);
        if (d.IsNull)
            throw new ArgumentException("You must specify data that will be sent to the endpoint", "@d");
        var response =
                client.UploadString(
                    Uri.EscapeUriString(url.ToSqlString().Value),
                    d.ToSqlString().Value
                    );
        SqlContext.Pipe.Send("Request is executed. " + response);
    }

    [SqlProcedure]
    public static void PostWithRetry(SqlChars H, SqlChars d, SqlChars url)
    {
        var client = new WebClient();
        AddHeader(H, client);
        if (d.IsNull)
            throw new ArgumentException("You must specify data that will be sent to the endpoint", "@d");
        int i = RETRY_COUNT;
        string response = "";
        do try
            {
                response =
                        client.UploadString(
                            Uri.EscapeUriString(url.ToSqlString().Value),
                            d.ToSqlString().Value
                            );
                i = -1;
                break;
            }
            catch (Exception ex)
            {
                SqlContext.Pipe.Send("Error:\t" + ex.Message + ". Waiting " + DELAY_ON_ERROR + "ms.");
                i--;
                Thread.Sleep(DELAY_ON_ERROR);
            }
        while (i > 0);
        if (i == -1)
            SqlContext.Pipe.Send("Request is executed." + response);
    }

    static readonly int RETRY_COUNT = 3;
    static readonly int DELAY_ON_ERROR = 50;

    public static bool IsNullOrWhiteSpace(this string theString)
    {
        if (theString == null)
        {
            return false;
        }

        if (theString.Trim() == string.Empty)
        {
            return false;
        }
        return true;
    }

    private static void AddHeader(SqlChars H, WebClient client)
    {
        if (!H.IsNull)
        {
            string header = H.ToString();
            if (!IsNullOrWhiteSpace(header))
                client.Headers.Add(HttpRequestHeader.UserAgent, header);
        }
    }
};

And this how to use in SQL Query

declare @hkey nvarchar(4000) = 'SabaCertificate: 31336132353061666330315E235E756F6E6555E6261536974655E235E656E5F55535E235E536162615E235E24414021463393C69358BE384802BA1BBEAD3B4661862F193021435F7E28A30F7540FE661B9C5F30FDB06C';
declare @endpoint nvarchar(1000) = 'https://libertad-api.sabacloud.com/v1/location?count=10&startPage=1';
select curl.xget(@hkey, @endpoint)

I already test it in PostMan, entering the Header of SabaCertificate, and if it throws a result at me, however, when the certificate is not correct it also throws a response and it is not shown.

Bad Request Example:

{"errorCode":123,"errorMessage":"Invalid or expired Certificate"}

But it also does not give me the answer of the certificate error, that I have to change in my WebClient for this to work.

Added to this I think the certificate is too big because sometimes I get this error:

The identifier that starts with 'SabaCertificate: 31336132353061666330315E235E756F6E6555E6261536974655E235E656E5F55535E235E536162615E235E24414021463393C69358BE384802BA1BBEAD3B4661862F193021435F7E28A30F7540FE661B9C5F30FDB06C' is too long. Maximum length is 128.

Answer 1

One definite problem in the code is a slight change you made to the original code. In your AddHeader method you have the following line:

client.Headers.Add(HttpRequestHeader.UserAgent, header);

You need to remove the HttpRequestHeader.UserAgent because the code is now creating a "UserAgent" header with a value of whatever you pass in, which is "SabaCertificate: 31336132....".

You will also need to change the security protocols that you are setting as they are not correct. You should try:

ServicePointManager.SecurityProtocol |= (SecurityProtocolType)3072; // TLS 1.2

Since you are using .NET 3.5 via SQL Server 2008 R2, you cannot specify SecurityProtocolType.Tls12 since that value had not yet been added to the enum in Framework Version 3.5, so you have to use the numeric value as shown above. Please keep in mind that the actual ability to do the security protocol is a function of the underlying OS, so it is possible that an older version of Windows / Windows Server does not support TLS 1.2, or might need a registry setting changed in order to do so. You will have to play around with that if you continue to get similar errors from System.Net.TlsStream.

Also, the following error:

The identifier that starts with 'SabaCertificate: 31336...30FDB06C' is too long. Maximum length is 128.

is from user-error. An "identifier" is an item name within SQL Server (objects, Logins, variables, etc). This means that you are doing something different (and wrong) when that error happens, but I can't see how it could be coming from your code, at least not the Get method, as that has no internal interaction with the database.