SQL Server

Authenticate an IMAP SSH Tunnel with a Private Key

Demonstrates the Chilkat Imap.SshAuthenticatePk method, which authenticates the SSH tunnel using a username and an SshKey private key. The first argument is the SSH login name and the second is the private key. Call SshOpenTunnel first; the matching public key must already be authorized for the login on the SSH server.

Note: The private-key path is relative to the application's current working directory. Supply the path to your own key file.

Background: Public-key authentication is the usual choice for unattended/automated SSH access: the private key stays on the client and the server is configured to trust the corresponding public key, so no reusable password travels over the wire. The SshKey object can import keys in several formats — this example uses an OpenSSH private key.

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 Imap.SshAuthenticatePk method, which authenticates the SSH tunnel using a
    --  username and a private key.  The 1st argument is the SSH login name and the 2nd is an SshKey
    --  private key.  Call SshOpenTunnel first.

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

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

    --  Load the SSH private key.  LoadText returns the file's text, which is then imported.
    DECLARE @privKey int
    EXEC @hr = sp_OACreate 'Chilkat.SshKey', @privKey OUT

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

    --  Authenticate the SSH tunnel with the private key.  The matching public key must already be
    --  authorized for the login on the SSH server.
    EXEC sp_OAMethod @imap, 'SshAuthenticatePk', @success OUT, 'sshUser', @privKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    --  With the SSH tunnel in place, connect and log in to the IMAP server as usual.  The IMAP
    --  traffic flows through the tunnel automatically.
    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.example.com'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'user@example.com', 'myPassword'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

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

    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @privKey


END
GO