0

I have a webserver running a REST API, and I need to query from a SQL Server.

I can easily make the API work with Postman and the NTLM option as seen below:

enter image description here

but I have no idea how to do that with SQL code. I have been using HTTP:

Declare @Object as Int;
DECLARE @hr int
Declare @json as table(RESPONSE nvarchar(max))

Exec @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;
EXEC @hr= sp_OASetProperty @Object, 'Option', '13056', 4
--EXEC @hr = sp_OASetProperty @object, 'WinHttpRequestOption_SslErrorFlag_Ignore_All', 1;  

IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',
      'https://blahblahblah/GetInventoryDetails?barcode=00387127399232474375', 
      'false'
--IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'send'
--IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
--IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

INSERT into @json (RESPONSE) exec sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
select * from @json

EXEC sp_OADestroy @Object

But this obviously returns a 401 unauthorized.

Any way to achieve that?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Waza_Be
  • 39,407
  • 49
  • 186
  • 260
  • 4
    Just don't; not in SQL Server. Those old OA objects haven't seen updates since 2005, maybe even as long ago as 2000. If you *had* do this is SQL Server you would use CLR, but again, don't, use middleware to communicate with both the website and SQL Server appropriately. – Thom A Jul 13 '23 at 07:48
  • Alternatively use Powershell from a SQL Server Agent job and insert the data using `Invoke-SqlCmd`, or use Python from `sp_execute_external_script` and pass an output parameter or dataset back. In Powershell this is a single line `Invoke-RestMethod -UseDefaultCredentials -Method Get -Uri 'https://blahblahblah/GetInventoryDetails?barcode=00387127399232474375'`. – Charlieface Jul 13 '23 at 08:07
  • You could see what headers get created from postman and try to replicate them with sql server. Or just don't use the sql server for tasks that aren't you know, database management – siggemannen Jul 13 '23 at 08:19
  • @siggemannen I can understand that, but when there is a potential solution to have everything inside SQL and avoid an extra app to maintain/monitor , that is worth trying :-D – Waza_Be Jul 13 '23 at 09:04
  • Even if you could get a challenge-response type authentication mechanism like NTLM working by way of sp_OA* objects, which account do you think that would be using for authentication? You really do need to use an external process to do this interaction and it needs to be running in the context of the required user account (or at least able to impersonate it). – AlwaysLearning Jul 13 '23 at 09:55
  • btw, ntlm is kind of a weird auth protocol for web api, at least i have never seen it, what's wrong with standard basic auth – siggemannen Jul 13 '23 at 10:07

0 Answers0