SQL Server

SSH Authenticate with a SecureString Password

Demonstrates the Chilkat Ssh.AuthenticateSecPw method, which performs the same authentication as AuthenticatePw but receives the login and password as SecureString objects. The first argument is the login and the second is the password.

Background: A SecureString keeps the credential encrypted in memory, reducing the chance of a plaintext password lingering in the process. The connection-state, retry, and AuthFailReason behavior matches AuthenticatePw; only the credential handling is more protective. Populate the SecureString from a value obtained at runtime, not a hard-coded literal.

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 Ssh.AuthenticateSecPw method, which authenticates using SecureString login
    --  and password objects.  The 1st argument is the login and the 2nd is the password.

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

    --  Connect to the SSH server.  Port 22 is the usual SSH port.
    DECLARE @sshPort int
    SELECT @sshPort = 22
    EXEC sp_OAMethod @ssh, 'Connect', @success OUT, 'ssh.example.com', @sshPort
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    --  Normally you would not hard-code the password in source.  You should instead obtain it
    --  from an interactive prompt, environment variable, or a secrets vault.
    DECLARE @password nvarchar(4000)
    SELECT @password = 'mySshPassword'

    --  Place the login and password into SecureString objects.
    DECLARE @secureLogin int
    EXEC @hr = sp_OACreate 'Chilkat.SecureString', @secureLogin OUT

    EXEC sp_OAMethod @secureLogin, 'Append', @success OUT, 'mySshLogin'
    DECLARE @securePassword int
    EXEC @hr = sp_OACreate 'Chilkat.SecureString', @securePassword OUT

    EXEC sp_OAMethod @securePassword, 'Append', @success OUT, @password

    EXEC sp_OAMethod @ssh, 'AuthenticateSecPw', @success OUT, @secureLogin, @securePassword
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @secureLogin
        EXEC @hr = sp_OADestroy @securePassword
        RETURN
      END

    PRINT 'Authenticated.'

    EXEC sp_OAMethod @ssh, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @ssh
    EXEC @hr = sp_OADestroy @secureLogin
    EXEC @hr = sp_OADestroy @securePassword


END
GO