I’m encountering an issue when making an API GET call on PC with SQL Server 2012 using sp_OACreate with MSXML2.ServerXMLHTTP (also tested with MSXML2.XMLHTTP). The same script works perfectly on my local PC with SQL Server 2019 and receives a response from the API.

This is the code I’m testing:

DECLARE @URL NVARCHAR(MAX),
    @basicAuth NVARCHAR(200),
    @Msg NVARCHAR(MAX),
    @Object as Int,
    @ResponseText as Varchar(8000),
        @HTTPStatus int, 
    @statusText nvarchar(100)

SET @URL = N'https://example.com/api/test'

-- 'MSXML2.XMLHTTP'
Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
        @URL, 'false'

SET @basicAuth = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'

SET @basicAuth = 'Basic '+ @basicAuth

EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Authorization', @basicAuth

Exec sp_OAMethod @Object, 'send'
EXEC sp_OAGetProperty @Object, 'status', @HTTPStatus OUT
EXEC sp_OAGetProperty @Object, 'statusText', @statusText OUT;
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Exec sp_OADestroy @Object

PRINT 'Response: '+ ISNULL(@ResponseText, 'NULL')
PRINT 'StatusCode: '+ ISNULL(CAST(@HTTPStatus AS NVARCHAR(100)), 'NULL')
PRINT 'Status: '+ ISNULL(@statusText, 'NULL')

SET @Msg = 'Call Web Api ended. Result: ' + ISNULL(@ResponseText, 'NULL')
PRINT @Msg

What could be causing this issue? Are there specific configurations or requirements for MSXML2 on SQL Server 2012 that might be necessary?

Thank you in advance for any suggestions or solutions!

What i’ve tested

• Working: On my local PC with SQL Server 2019, the script successfully receives a response from the API.

Response: {"result": 1}
StatusCode: 200
Status: OK
Call Web Api ended. Result: {"result": 1}

• Not Working on the final PC with SQL Server 2012, where the solution needs to work, the script does not receive any response from the API (Response, StatusCode, and Status are all NULL).

Response: NULL
StatusCode: NULL
Status: NULL
Call Web Api ended. Result: NULL

• The PC with the issue can reach the API and get a response using another program, so it is not a network connectivity issue.
• I also tested on the machine experiencing the issue by executing the SQL script with a different URL (a different server with different IP address) that does not require authentication. For this test, I temporarily commented out the authentication part. In this case, I received a response.
• I checked if the configuration of ‘Ole Automation Procedures’ are enabled:

sp_configure 'show advanced options', 1 
GO 
RECONFIGURE; 
GO 
sp_configure 'Ole Automation Procedures', 1 
GO 
RECONFIGURE; 
GO 
sp_configure 'show advanced options', 1 
GO 
RECONFIGURE;