Set custom request header in SQL querying a REST API which expects session to be passed in header

Question

Having followed a fantastic answer found here: Parse JSON from web in SQL Server 2017

I was able to get the response body from the HTTP request to an REST API server which responds in JSON format. This works beautifully when the API endpoint doesn't require a session ID.

However the endpoint I would like to query requires a session ID (which I have tested a permanent ID without any problems with authentication).

When analyzing the HTTP request in Wireshark I can see that there is no mention of my header within the data packet. But looking at when I interface with the API via a web browser I can see my Session-Id being passed.

Here is my SQL:

Declare @Object as Int;
Declare @Response table (txt nvarchar(max));

Exec sp_OACreate 'MSXML2.serverXMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'GET',
             'http://someip/Rest/EndPoint'
Exec sp_OAMethod @Object, 'setRequestHeader',null,'Session-Id','123456789'
Exec sp_OAMethod @Object, 'send'

Insert Into @Response (txt)
Exec sp_OAMethod @Object, 'responseText'

Exec sp_OADestroy @Object   

Select txt From @Response

I can achieve this simply in Javascipt (see below) without trouble; but need it in SQL to parse the response into an SQL table without using a middle man like NodeJS and sending the response to SQL server.

var xhr = new XMLHttpRequest();
xhr.open("GET", url, true);
xhr.setRequestHeader("Session-Id", "123456789");

Any/all help would be much appreciated!

Thanks

Vintron · Accepted Answer · 2020-02-25 15:01:34Z

0

This is now resolved; the reason why it wasn't working was two fold:

The session ID needed to be generated from the same source as the server the query was running from (e.g. web front end generated a session ID which was linked to the machine ID it was generated from, the server the SQL was being ran from had to be the one to generate the session ID).
The server I was running the query from had an out of date SSL certificate and needed to update that

Both are a little niche but maybe this answer will help someone out, or at least rule some reasons out, who knows?!

answered Feb 25, 2020 at 15:01

Vintron

91 silver badge2 bronze badges

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

Collectives™ on Stack Overflow

Set custom request header in SQL querying a REST API which expects session to be passed in header

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related