(SQL Server) REST Basic Auth with Secure Strings

Demonstrates how to do REST Basic authentication using secure strings.

This example requires Chilkat v9.5.0.71 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

    -- Imagine we've previously saved our encrypted login and password within a JSON config file
    -- that contains this:

    -- {
    --   "http_login": "mCrOmA7mBA7Au9RuJGb9hw==",
    --   "http_password": "jJtiI9TgErTTpqBz9JtHBw=="
    -- }

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/passwords/http.json'

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    -- These are the encryption settings we previously used to encrypt the credentials within the JSON config file.
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 128
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'

    DECLARE @ssLogin int
    -- Use "Chilkat_9_5_0.SecureString" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.SecureString', @ssLogin OUT

    DECLARE @ssPassword int
    -- Use "Chilkat_9_5_0.SecureString" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.SecureString', @ssPassword OUT

    -- Decrypt to the secure string.  (the strings will still held in memory encrypted, but are now encrypted using
    -- a randomly generated session key.)
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'http_login'
    EXEC sp_OAMethod @crypt, 'DecryptSecureENC', @success OUT, @sTmp0, @ssLogin
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'http_password'
    EXEC sp_OAMethod @crypt, 'DecryptSecureENC', @success OUT, @sTmp0, @ssPassword

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT

    -- Connect to a REST server.
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'chilkatsoft.com', @port, @bTls, @bAutoReconnect

    -- Cause the "Authorization: Basic ..." header to be added to HTTP requests
    EXEC sp_OAMethod @rest, 'SetAuthBasicSecure', @success OUT, @ssLogin, @ssPassword

    DECLARE @responseJson nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseJson OUT, 'GET', '/helloWorld.html'
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @ssLogin
        EXEC @hr = sp_OADestroy @ssPassword
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- Show the LastRequestHeader that was sent.
    EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
    PRINT @sTmp0

    -- The LastRequestHeader looks like this:

    -- Host: chilkatsoft.com
    -- Authorization: Basic bXlIdHRwTG9naW46bXlIdHRwUGFzc3dvcmQ=

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @ssLogin
    EXEC @hr = sp_OADestroy @ssPassword
    EXEC @hr = sp_OADestroy @rest


END
GO