SQL Server

SSH Tunnel Authenticate with Secure Strings

Demonstrates SSH tunnel password authentication using SecureString objects, with the credentials read from an encrypted JSON config file and decrypted directly into secure strings rather than being hard-coded in source.

Background: The SshTunnel class runs the tunnel on a background thread, so it authenticates once and then serves connections until closed — which makes protecting the stored credential especially worthwhile. Decrypting straight into a SecureString avoids ever holding the password in an ordinary string, and the value stays encrypted in memory under a randomly generated session key. In production the decryption key itself would come from a key vault or OS keystore rather than a literal.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    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.

    --  Demonstrates SSH tunnel password authentication using SecureString objects, with the
    --  credentials read from an encrypted config file rather than hard-coded in source.

    --  Imagine the login and password were previously encrypted and saved in a JSON config file:
    --  {
    --    "ssh_login": "2+qylFfC56Ck7OQQt/U2/w==",
    --    "ssh_password": "5neIq9Jmn0E3p71N6Yc8TA=="
    --  }
    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/ssh.json'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @json, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    --  These are the encryption settings used when the credentials were encrypted.  In a real
    --  application the key would come from a secure source, not a literal.
    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    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'

    --  Decrypt directly into SecureString objects.  The values stay encrypted in memory, now under
    --  a randomly generated session key.
    DECLARE @ssLogin int
    EXEC @hr = sp_OACreate 'Chilkat.SecureString', @ssLogin OUT

    DECLARE @ssPassword int
    EXEC @hr = sp_OACreate 'Chilkat.SecureString', @ssPassword OUT

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'ssh_login'
    EXEC sp_OAMethod @crypt, 'DecryptSecureENC', @success OUT, @sTmp0, @ssLogin
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'ssh_password'
    EXEC sp_OAMethod @crypt, 'DecryptSecureENC', @success OUT, @sTmp0, @ssPassword

    DECLARE @tunnel int
    EXEC @hr = sp_OACreate 'Chilkat.SshTunnel', @tunnel OUT

    DECLARE @port int
    SELECT @port = 22
    EXEC sp_OAMethod @tunnel, 'Connect', @success OUT, 'ssh.example.com', @port
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tunnel, '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 @tunnel
        RETURN
      END

    --  Authenticate using the secure strings.
    EXEC sp_OAMethod @tunnel, 'AuthenticateSecPw', @success OUT, @ssLogin, @ssPassword
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tunnel, '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 @tunnel
        RETURN
      END


    PRINT 'SSH tunnel authentication successful.'

    --  ... use the tunnel ...

    DECLARE @waitForThreadExit int
    SELECT @waitForThreadExit = 1
    EXEC sp_OAMethod @tunnel, 'CloseTunnel', @success OUT, @waitForThreadExit
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tunnel, '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 @tunnel
        RETURN
      END

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


END
GO