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
