HTTP Requests Using SQLCLR

Question

Post reply

HTTP Requests Using SQLCLR

July 8, 2022 at 12:36 pm

This was removed by the editor as SPAM
July 14, 2022 at 4:47 am

This was removed by the editor as SPAM

Viewing 8 posts - 46 through 52 (of 52 total)

You must be logged in to reply to this topic. Login to reply

Eitan Blumin SSC Enthusiast Points: 172 More actions · Answer 1

@fedan , I've added support for a new "Proxy" header in my own version.

Please see if it helps:

https://github.com/MadeiraData/ClrHttpRequest

fedan Grasshopper Points: 15 More actions · Answer 2

@eitan-blumin

Thank you very much for your indifference to my request. But I ran into a problem that due to the use of the ":" sign as a URI:PORT will not allow «http://» to be used before the IP address, as shown in the example https://acmeproxy:4321 . I remove https:// - acmeproxy:4321 and it works! There is one more wish, some paid proxies provide access after entering Login:Password. Could you add this feature to the CLR? I tried to build such a structure, but I couldn't log in. Unfortunately, I'm a beginner in C#

case "PROXY":
                        var proxyValues = headerValue.Split(':');
                        if (proxyValues.Length < 2)
                        {
                            throw new FormatException("When specifying the PROXY header, please set the value in a format of URI:PORT:username:password");
                        }
                        int proxyPort;
                        if (!int.TryParse(proxyValues[1], out proxyPort))
                        {
                            throw new FormatException("When specifying the PROXY header in the format of URI:PORT, the PORT must be numeric");
                        }
                        //Validate proxy address
                        var proxyURI = new Uri(string.Format("http://"+"{0}:{1}", proxyValues[0], proxyPort));
 
                        //Set credentials
                        ICredentials credentials = new NetworkCredential(proxyValues[2], proxyValues[3]);
                        WebProxy myproxy = new WebProxy(proxyURI, false, null, credentials);

                        //WebProxy myproxy = new WebProxy(proxyValues[0], proxyPort);
                        myproxy.BypassProxyOnLocal = false;
                        request.Proxy = myproxy;
                        break;

Eitan Blumin SSC Enthusiast Points: 172 More actions · Answer 3

That's a good point, @fedan

Thank you for your comment.

I should refactor the code a bit so that the separator character would be a comma "," instead of a colon ":".

As for the username and password... I guess I would've done something like this format:

http://proxyURI,port,username:password

I would split this value by commas, and then if there's a 3rd item then I would assume it's a username/password combination, which I'd be able to split again by colon. Something like this:

var proxyValues = headerValue.Split(',');

WebProxy myproxy = new WebProxy(proxyValues[0], int.Parse(proxyValues[1]));
myproxy.BypassProxyOnLocal = false;

var proxyCred = proxyValues[2].Split(':');
myproxy.Credentials = new NetworkCredential(proxyCred[0], proxyCred[1]);

I'll update my code soon to integrate this.

@fedan , are you getting any specific errors when it's not working for you?

morribd91 Newbie Points: 1 More actions · Answer 4

This is a very slick bit of code. However I am running into a few issues when making an attempt to execute a get call. My API has a header called 'accept'

My SQL looks like

DECLARE @ResponseA AS XML = dbo.clr_http_request

(

'GET',

'https://smariqs02.smr.motherson.com/restapi/tokens',

NULL,

'<Headers>

<Header Name="accept">application/json </Header>

<Header Name="Authorization">Basic SVFTQURNSU46UGFzc3dvcmRAMQ==</Header>

</Headers>' ,

300000,

1,

0

);

DECLARE @ResponseA_JSON NVARCHAR(MAX) = @ResponseA.value('Response[1]/Body[1]','NVARCHAR(MAX)');

SELECT A.access,

A.refresh

FROM OPENJSON(@ResponseA_JSON) WITH([items] NVARCHAR(MAX) AS JSON) B

CROSS APPLY OPENJSON(B.[items]) WITH

(

VARCHAR(500),

[refresh] VARCHAR(500)

) A;

However when it runs I get

Msg 6522, Level 16, State 1, Line 1

A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_http_request":

System.ArgumentException: The 'accept' header must be modified using the appropriate property or method.

Parameter name: name

System.ArgumentException:

at System.Net.WebHeaderCollection.ThrowOnRestrictedHeader(String headerName)

at System.Net.WebHeaderCollection.Add(String name, String value)

at UserDefinedFunctions.clr_http_request(String requestMethod, String url, String parameters, String headers, Int32 timeout, Boolean autoDecompress, Boolean convertResponseToBas64)

It seems concered witrh the accept Header. This is my first time doing anything like this so please forgive my ignorace

Eitan Blumin SSC Enthusiast Points: 172 More actions · Answer 5

Hi,

I'd suggest a couple of things:

1. It's not entirely clear from your code and it just might be a formatting issue on my phone... But it looks like you have a new line inside your accept header. It needs to be without new lines.

2. The accept header might be case sensitive. Try using a capital first letter: Accept

Regards,

Eitan

dizzyjay Newbie Points: 1 More actions · Answer 6

hello. I am trying to get this solution to work in an Azure SQL Managed Instance. One difference I've found in the process is when deploying to SQLMI, you're not able to reference the .dll file when creating the assembly and the asymetrical key. I've figured out how to get a hex version of the assembly but am stuck on how to make a valid asymetrical key without linking to the .dll - any help on how to implement this solution on Azure SQLMI is greatly appreciated.