如何在MS SQL Server中通过Basic Authentication调用REST API获取数据

Hey there, let's break down how to call a REST API with Basic Authentication from MS SQL Server to pull in data. I've tackled this scenario a few times, so here's a practical, step-by-step guide with working code examples.

First, a quick note: we'll use OLE Automation Procedures here since it’s the most widely compatible method across SQL Server 2016+. If you’re on SQL Server 2022, there are newer options, but this approach works for most environments.

1. Enable OLE Automation Procedures #

By default, OLE Automation is disabled for security reasons. You’ll need admin privileges to turn it on:

-- Enable advanced configuration options
sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Activate Ole Automation Procedures
sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;
GO

2. Create a Stored Procedure for API Calls #

This stored procedure handles encoding credentials for Basic Auth, sending the request, and returning the API response. It includes cleanup logic to avoid COM object leaks:

CREATE PROCEDURE dbo.FetchDataFromAPI
    @ApiUrl NVARCHAR(MAX),
    @Username NVARCHAR(100),
    @Password NVARCHAR(100),
    @Response NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Obj INT;
    DECLARE @HttpStatus INT;
    DECLARE @BasicAuth NVARCHAR(MAX);

    -- Encode username:password into Base64 for Basic Authentication
    SET @BasicAuth = 'Basic ' + CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(MAX)')
    FROM (SELECT CAST(@Username + ':' + @Password AS VARBINARY(MAX)) AS bin) AS bin;

    -- Create XMLHTTP object
    EXEC sp_OACreate 'MSXML2.XMLHTTP', @Obj OUT;
    IF @@ERROR <> 0 GOTO Cleanup;

    -- Open a GET request (swap 'GET' with 'POST' if your API requires it)
    EXEC sp_OAMethod @Obj, 'open', NULL, 'GET', @ApiUrl, 'false';
    IF @@ERROR <> 0 GOTO Cleanup;

    -- Add the Authorization header
    EXEC sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Authorization', @BasicAuth;
    IF @@ERROR <> 0 GOTO Cleanup;

    -- Optional: Set timeout values (30 seconds for each phase)
    EXEC sp_OAMethod @Obj, 'setTimeouts', NULL, 30000, 30000, 30000, 30000;

    -- Send the request to the API
    EXEC sp_OAMethod @Obj, 'send';
    IF @@ERROR <> 0 GOTO Cleanup;

    -- Check HTTP status code to confirm success
    EXEC sp_OAGetProperty @Obj, 'status', @HttpStatus OUT;
    IF @@ERROR <> 0 GOTO Cleanup;

    -- Retrieve response if request succeeded (200 OK)
    IF @HttpStatus = 200
    BEGIN
        EXEC sp_OAGetProperty @Obj, 'responseText', @Response OUT;
    END
    ELSE
    BEGIN
        SET @Response = 'Request failed. HTTP Status Code: ' + CAST(@HttpStatus AS NVARCHAR(10));
    END

Cleanup:
    -- Always destroy the COM object to prevent memory leaks
    IF @Obj IS NOT NULL
        EXEC sp_OADestroy @Obj;
END
GO

3. Call the Procedure and Parse the Response #

Once the stored procedure is ready, call it and parse the JSON response (assuming your API returns JSON) using OPENJSON:

DECLARE @ApiResponse NVARCHAR(MAX);

-- Replace with your API details
EXEC dbo.FetchDataFromAPI
    @ApiUrl = 'https://api.yourdomain.com/data-endpoint',
    @Username = 'your-basic-auth-username',
    @Password = 'your-basic-auth-password',
    @Response = @ApiResponse OUT;

-- Validate and parse the JSON response
IF ISJSON(@ApiResponse) = 1
BEGIN
    -- Adjust the WITH clause to match your API's JSON structure
    SELECT *
    FROM OPENJSON(@ApiResponse)
    WITH (
        ItemId INT '$.id',
        ItemName NVARCHAR(150) '$.name',
        CreatedDate DATETIME '$.created_at',
        Description NVARCHAR(500) '$.description'
    );
END
ELSE
BEGIN
    PRINT 'Error or invalid JSON: ' + @ApiResponse;
END

Key Considerations #

• Security: Never hardcode credentials. Store them in encrypted columns (using ENCRYPTBYCERT or Azure Key Vault for Azure SQL) and retrieve them securely. Limit permissions for users executing the procedure.
• Error Handling: Expand the procedure with sp_OAGetErrorInfo to capture more detailed error messages if needed.
• HTTPS: Always use HTTPS for your API to avoid sending credentials in plaintext.
• SQL Server 2022 Alternative: For newer versions, you can use OPENROWSET(BULK...) with FOR JSON for a more modern approach, but OLE Automation remains compatible with older SQL Server versions.

内容的提问来源于stack exchange，提问作者Bhavesh Harsora