0

Currently I'm running SQL Server 2017 on a local workstation. I would like to make a call (GET) from a SQL stored procedure to a SharePoint 2016 REST API on our network.

When using the REST API url in a browser or when using PostMan (NTLM authentication) I'm successful in getting results.

When trying to call the same API from SQL I keep on running into a 401 Unauthorized error. The authentication header contains the same account details as the one used in PostMan.

The SQL query below returns:

Status: 401 (Unauthorized)
Response text: 401 UNAUTHORIZED

How can I fix the SQL query below to resolve this issue?

DECLARE @contentType NVARCHAR(64);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);
DECLARE @authHeader VARCHAR(8000);   

SET @authHeader = 'BASIC ' +(SELECT CAST('<account>:<password>' as varbinary(max)) FOR XML PATH(''), BINARY BASE64)

--SET @contentType = 'application/x-www-form-urlencoded';
SET @contentType = 'application/atom+xml';
SET @url = 'http://<FQDN>/sites/<site>/_api/web/lists/getbytitle(''Documents'')/items';

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

-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
IF @ret <> 0 EXEC sp_OAGetErrorInfo @token

EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
IF @ret <> 0 EXEC sp_OAGetErrorInfo @token

EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
IF @ret <> 0 EXEC sp_OAGetErrorInfo @token

EXEC @ret = sp_OAMethod @token, 'send'--, NULL, @postData;
IF @ret <> 0 EXEC sp_OAGetErrorInfo @token


-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;

-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);
M.Z.
  • 71
  • 4
  • 2
    Honestly, this isn't something SQL Server should be doing, have an application layer consume the API and the `INSERT`/`UPDATE` the data in SQL Server from there if you need to using parametrised queries. – Thom A Feb 02 '21 at 11:18
  • I don't need any data stored in a SQL db. The end-game is to have SQL fetch data from a REST API and pass it on to an SSRS report. (p.s. I know SSRS has a datasource connector for SP, but the REST API I want to use in the end is not based on SharePoint) – M.Z. Feb 02 '21 at 11:27
  • If you've done any research you will find many discussions about tying to access this sort of information via tsql. You will note the complete absence of support for this which is why use the ancient, error-prone, and difficult-to-debug OLE automation procedures. As already noted (and discussed in the research) this simply is not logic suited for tsql. – SMor Feb 02 '21 at 11:46
  • Why even involve SQL Server? Can't you wrap the RESTful API up an a [Data Processing Extension](https://learn.microsoft.com/en-us/sql/reporting-services/extensions/data-processing/implementing-a-data-processing-extension?view=sql-server-ver15)? – AlwaysLearning Feb 02 '21 at 11:47
  • Thank you for the pointer to the Data Processing Extension. I will look into this solution. – M.Z. Feb 02 '21 at 12:34

0 Answers0