Steve Steve - 6 months ago 135
SQL Question

Is there a way to use sp_OACreate with Authorization header, with Bearer and jwt token?

Is there a way to use

sp_OACreate 'WinHttp.WinHttpRequest.5.1'
to post data using SQL Server 2008r2 and Authorization header, with Bearer and jwt token? The code below does not work, but hopefully outlines what I'm trying to do. Gets 401, but token works in Fiddler:

set @body = '{
"id": "73135",
"Objectives": [{
"objt": "blah",
"comment": "blah",
"type": "blah",
"index": "blah",
"state": "blah"
}]
}'


exec @hr = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @obj OUT
if @hr <> 0
begin
set @msg = 'sp_OACreate failed'
end

exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', 'https://myurl/731', false
if @hr <> 0
begin
set @msg = 'sp_OAMethod Open failed'
end

EXEC sp_OAMethod @hr, 'setRequestHeader', NULL, 'Authorization', 'Bearer long.jwt.here'

exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type','application/json'
if @hr <> 0
begin
set @msg = 'sp_OAMethod setRequestHeader failed'
end

exec @hr = sp_OAMethod @obj, send, NULL, @body
if @hr <> 0
begin
set @msg = 'sp_OAMethod Send failed'
end

Answer

Change this:

EXEC sp_OAMethod @hr, 'setRequestHeader', NULL, 'Authorization', 'Bearer long.jwt.here' 

To this:

EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', 'Bearer long.jwt.here' 
if @hr <> 0 
      begin 
            set @msg = 'sp_OAMethod setRequestHeader failed' 
      end