SQL Server

SSH Authenticate with Secure Strings

Demonstrates SSH 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: This shows the shape of a genuinely careful credential flow: secrets live outside the source in encrypted form, and are decrypted straight into a SecureString so a plaintext copy never sits in an ordinary string. A SecureString keeps the value encrypted in memory under a randomly generated session key. In production the decryption key itself would come from a secure source — a key vault, an OS keystore, or the environment — rather than being a literal as shown here for illustration.

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 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 remain 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 @ssh int
    EXEC @hr = sp_OACreate 'Chilkat.Ssh', @ssh OUT

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

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


    PRINT 'SSH authentication successful.'

    EXEC sp_OAMethod @ssh, 'Disconnect', NULL

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


END
GO