SQL Server

SSH Tunnel Credentials from OS Secure Storage

Demonstrates the EnableSecrets property, which enables automatic resolution of credentials from the operating system's secure storage. When enabled, password properties and methods may receive a "secret specification string" beginning with !! instead of a literal password.

Background: This is a cleaner alternative to reading a secret yourself and passing it in: with EnableSecrets on, you hand Chilkat a reference like !!my_secret_name and it fetches the actual value from the platform's secure store — Windows Credential Manager, Apple Keychain, and so on. The literal password never appears in your source or configuration, which is exactly the practice recommended throughout these examples.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

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
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the SshTunnel.EnableSecrets property, which enables automatic resolution of
    --  credentials from the operating system's secure storage.

    DECLARE @tunnel int
    EXEC @hr = sp_OACreate 'Chilkat.SshTunnel', @tunnel OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  When EnableSecrets is 1, supported password properties and methods may receive a "secret
    --  specification string" beginning with "!!" instead of a literal password.  Chilkat resolves the
    --  secret from the OS secure store (Windows Credential Manager, Apple Keychain, etc.).
    EXEC sp_OASetProperty @tunnel, 'EnableSecrets', 1

    EXEC sp_OASetProperty @tunnel, 'DestHostname', 'db.internal.example.com'
    EXEC sp_OASetProperty @tunnel, 'DestPort', 5432

    DECLARE @sshPort int
    SELECT @sshPort = 22
    EXEC sp_OAMethod @tunnel, 'Connect', @success OUT, 'ssh.example.com', @sshPort
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        RETURN
      END

    --  Pass a secret specification instead of a literal password.  Chilkat looks up the named secret
    --  in the OS secure store rather than using this text directly.
    DECLARE @password nvarchar(4000)
    SELECT @password = '!!my_ssh_password_secret'
    EXEC sp_OAMethod @tunnel, 'AuthenticatePw', @success OUT, 'mySshLogin', @password
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        RETURN
      END

    DECLARE @listenPort int
    SELECT @listenPort = 1080
    EXEC sp_OAMethod @tunnel, 'BeginAccepting', @success OUT, @listenPort
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        RETURN
      END

    PRINT 'Authenticated using a secret from OS secure storage.'

    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 @tunnel
        RETURN
      END

    EXEC @hr = sp_OADestroy @tunnel


END
GO