2

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.

5
  • To begin with, I would change the @hkey and @endpoint variables to nvarchar(max), and see what happens. Commented Nov 1, 2018 at 7:03
  • Is the same result with nvarchar(max) Commented Nov 1, 2018 at 14:38
  • You are trying to connect using week and abandoned encryption - all sites have abandoned SSL v3 and most require TLS1.1 if not 1.2 outright. .Tls is TLS1.0 whis isn't used either Commented Nov 1, 2018 at 14:57
  • 1
    When I use Fiddler I see that libertad-api.sabacloud.com/v1/location uses TLS1.2. That's not surprising since banks, cloud providers, airlines etc moved to mandatory TLS1.2 two years ago Commented Nov 1, 2018 at 15:05
  • @PanagiotisKanavos Thanks for bringing the security protocol issue up. I was meaning to mention it after getting past the initial, obvious issue. But since you brought it up, I updated my answer to address this issue as well. I will say that I have tried this using my own code and the URL and header info provided here, definitely supporting TLS 1.2, and still cannot get this to work. Commented Nov 1, 2018 at 16:09

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

8 Comments

I followed all your recommendations and now i get this error in SQL Server: Msg 6522, Level 16, State 1, Line 2 A .NET Framework error occurred during execution of user-defined routine or aggregate "XGET": System.Net.WebException: Error on the remote server: (500) Internal server error. System.Net.WebException: at System.Net.WebClient.DownloadDataInternal(Uri address, WebRequest& request) at System.Net.WebClient.DownloadString(Uri address) at System.Net.WebClient.DownloadString(String address) at Curl.Get(SqlChars H, SqlChars url)
@AdrianSandoval Yes, I get that same error. I even tried with the actual curl.exe program and got the exact same response. Are you 100% sure that the URI and the header are correct? Is there documentation for this SABA API?
this is the Rest Api Uses In Postman I got a satisfactory answer, and this is what I get in the headers section, Headers SABA. And thank you very much for all the help.
If I specify the URL in that headers file you linked to, (i.e. https://na3p1.sabacloud.com/Saba/api/component/location?count=10&startPage=1) then I get a full page response, which is an improvement (maybe). But that response is still saying: "(123) Invalid or expired Certificate". I looked at the docs as well. Do you need to specify an API Key or X-Locale or anything like that?
@AdrianSandoval In fact, I just noticed that if I supply your value for the SabaCertificate into the "API Key" field at the top of the documentation page, if I click the "Try it!" button for "Get All Locations", it submits a URL with &SabaCertificate=313361... appended to the end. That also comes back with: { "errorCode": 123, "errorMessage": "Invalid or expired Certificate" }
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.