SQL Server

Authenticate an SSH Tunnel with a Private Key

Demonstrates the Chilkat MailMan.SshAuthenticatePk method, which authenticates with the SSH server using public-key authentication after an SSH tunnel has been opened. The first argument is the SSH account name and the second is the SSH private key (an SshKey object). This example opens a tunnel, loads an OpenSSH private key, authenticates with it, and runs a POP3 operation through the tunnel.

Background: SSH public-key authentication replaces a password with a key pair: the public key is installed on the SSH server, and the client proves it holds the matching private key without ever transmitting a secret. This is the preferred method for automated systems — there is no password to embed or rotate, and the key can be revoked server-side. Chilkat reads the key with an SshKey object, which understands common formats such as OpenSSH and PuTTY.

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

    --  Demonstrates the MailMan.SshAuthenticatePk method, which authenticates with the SSH server
    --  using public-key authentication after an SSH tunnel has been opened.  The 1st argument is
    --  the SSH account name and the 2nd is the SSH private key.

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

    --  Configure the POP3 server connection.  These connections will travel through the tunnel.
    EXEC sp_OASetProperty @mailman, 'MailHost', 'pop.example.com'
    EXEC sp_OASetProperty @mailman, 'MailPort', 995
    EXEC sp_OASetProperty @mailman, 'PopSsl', 1
    EXEC sp_OASetProperty @mailman, 'PopUsername', 'user@example.com'
    EXEC sp_OASetProperty @mailman, 'PopPassword', 'myPassword'

    --  Open the SSH tunnel first.
    EXEC sp_OAMethod @mailman, 'SshOpenTunnel', @success OUT, 'ssh.example.com', 22
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        RETURN
      END

    --  Load the SSH private key from an OpenSSH-format key file.
    DECLARE @sshKey int
    EXEC @hr = sp_OACreate 'Chilkat.SshKey', @sshKey OUT

    DECLARE @keyText nvarchar(4000)
    EXEC sp_OAMethod @sshKey, 'LoadText', @keyText OUT, 'qa_data/ssh/id_rsa'
    EXEC sp_OAGetProperty @sshKey, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @sshKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @sshKey
        RETURN
      END
    EXEC sp_OAMethod @sshKey, 'FromOpenSshPrivateKey', @success OUT, @keyText
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sshKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @sshKey
        RETURN
      END

    --  Authenticate with the SSH server using the private key.
    EXEC sp_OAMethod @mailman, 'SshAuthenticatePk', @success OUT, 'sshUser', @sshKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @sshKey
        RETURN
      END

    --  POP3 operations now travel through the SSH tunnel.
    DECLARE @count int
    EXEC sp_OAMethod @mailman, 'GetMailboxCount', @count OUT
    IF @count < 0
      BEGIN

        PRINT 'Failed to get the mailbox count.'
      END
    ELSE
      BEGIN

        PRINT 'Mailbox count: ' + @count
      END

    EXEC sp_OAMethod @mailman, 'SshCloseTunnel', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @sshKey
        RETURN
      END

    --  Note: The path "qa_data/ssh/id_rsa" is a relative local filesystem path,
    --  relative to the current working directory of the running application.

    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @sshKey


END
GO