How to pass Authorization Header from SQL Server to API to get JSON?

Question

I'm trying to get JSON data from an API by using SQL Server 2016. I am able to get the data where API doesn't need user credentials however if I need to provide user name and password I cannot get the data.

DECLARE @authHeader VARCHAR(8000);
DECLARE @contentType VARCHAR(8000);
DECLARE @postData VARCHAR(8000);
DECLARE @responseText VARCHAR(8000);
DECLARE @responseXML VARCHAR(8000);
DECLARE @ret INT;
DECLARE @status VARCHAR(8000);
DECLARE @statusText VARCHAR(8000);
DECLARE @token INT;
DECLARE @url VARCHAR(8000);
DECLARE @JSON VARCHAR(8000);

SET @authHeader = 'BASIC QWERTYASDFGH1234567890123456789==';
SET @contentType = 'application/json';
SET @url = 'https://example.example.com/api/product-list?area=12345&fromMonth=201811&toMonth=201812'

-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);


-- Send the request.

EXEC @ret = sp_OAMethod @token, 'Open', null, 'GET', @Url, 'false'
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;

EXEC sp_OAMethod @token, 'send', null
EXEC sp_OAMethod @token, 'responseText', @ResponseText OUTPUT

SET @JSON = @ResponseText
SELECT @JSON

Does anyone know how to pass Authorization header properly from SQL Server?

Did you ever get this to work?

pee2pee
– pee2pee

2021-07-23 13:17:59 +00:00
Commented Jul 23, 2021 at 13:17 — pee2pee
– pee2pee, Commented Jul 23, 2021 at 13:17

Haseeb · Accepted Answer · 2021-08-11 17:50:25Z

2

Below script for Bearer authentication.

SET @authHeader = 'Bearer keHDkAlaWwlczbqmGuGnqqYm-d3GfAvu_IuaX2l93';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;

in the case, if you want to pass the request body to the API add the below lines

DECLARE @RequestBody VARCHAR(8000)='[{"requestId": 1,"sender":"Myself"}]'
EXEC @ret = sp_OAMethod @token, 'send', NULL, @RequestBody;

answered Aug 11, 2021 at 17:50

Haseeb

1,04113 silver badges27 bronze badges

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

Comments

Sebbbbbb · Accepted Answer · 2018-12-13 16:06:41Z

1

Did you check the same request with Postman? With the exact same header?

Usually, the Authorization header requires to be prefixed by the keyword "Bearer".

"Authorization": "Bearer yourTokenHere"

Could you try with this prefix?

answered Dec 13, 2018 at 16:06

Sebbbbbb

1311 silver badge7 bronze badges

2 Comments

Ibrahim D Over a year ago

The same request with exact same header works fine in Postman. I am trying to make it work in SQL Server.

Haseeb Over a year ago

how can i pass a json body using sp_OAMethod?

Collectives™ on Stack Overflow

How to pass Authorization Header from SQL Server to API to get JSON?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related