SQL Server

HTTP Basic Auth with Secure Strings

Demonstrates how to do HTTP basic authentication using secure strings. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- 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
    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
    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
    EXEC @hr = sp_OACreate 'Chilkat.SecureString', @ssLogin OUT

    DECLARE @ssPassword int
    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 @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    -- Cause the "Authorization: Basic ..." header to be added to HTTP requests
    -- by setting the Login and Password properties.  However, instead of setting the password property
    -- directly, set it via the SetPassword method using the secure string.
    EXEC sp_OAMethod @ssLogin, 'Access', @sTmp0 OUT
    EXEC sp_OASetProperty @http, 'Login', @sTmp0
    EXEC sp_OAMethod @http, 'SetSecurePassword', @success OUT, @ssPassword

    -- Also indicate that Basic authentication is to be used..
    EXEC sp_OASetProperty @http, 'BasicAuth', 1

    -- Do an HTTP GET w/ Basic authentication.
    -- REMEMBER: Always use TLS with Basic authentication.  Otherwise your credentials are exposed for the world to see..

    DECLARE @responseStr nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @responseStr OUT, 'https://www.chilkatsoft.com/helloWorld.html'

    -- Show the request header we sent in the QuickGetStr:
    EXEC sp_OAGetProperty @http, 'LastHeader', @sTmp0 OUT
    PRINT @sTmp0

    -- The LastHeader looks something like this:

    -- GET /helloWorld.html HTTP/1.1
    -- Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
    -- Connection: keep-alive
    -- User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0
    -- Accept-Language: en-us,en;q=0.5
    -- Authorization: Basic bXlIdHRwTG9naW46bXlIdHRwUGFzc3dvcmQ=
    -- Accept-Encoding: gzip
    -- Host: www.chilkatsoft.com

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


END
GO