Call Web API with Authentication in SQL server

manvendra45
 
on Feb 18, 2021 10:33 PM
2797 Views

Hello Team

I'm facing authentication issue while calling WebAPI with username and Password  in SQL server and getting error :" {"status-code":401,"message":"Basic Authorization is incomplete."}

But its running successfully on Postman.

Please give me suggestion how to fix this issue 

DECLARE @authHeader VARCHAR(8000);
DECLARE @contentType VARCHAR(8000);
DECLARE @postData VARCHAR(8000)='{​​​​​
    "fromDate": "2020-11-08",
    "toDate": "2020-11-08"
}​​​​​';
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);
DECLARE @UserName nvarchar(100) ='123456';--dummy UserName 
DECLARE @Password nvarchar(500)='234';--dummy password 
SET @authHeader = 'Authorization: Basic AUTH_STRING';

SET @contentType = 'application/json';
SET @url = 'https://mywebapi/'--- dummy URL 

-- 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',@UserName,@Password
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 

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Feb 18, 2021 11:36 PM

I will get back to you soon.

manvendra45
 
on Feb 19, 2021 02:15 AM

Okey sure