SQL Server

Connect to IMAP Through an SSH Tunnel

Demonstrates the Chilkat Imap.SshOpenTunnel method, which connects to an SSH server and prepares a tunnel through which a later IMAP connection is routed. The first argument is the SSH server hostname and the second is the SSH port (usually 22). After it succeeds, authenticate the tunnel, then call Connect and Login for the IMAP server.

Background: SSH tunneling (port forwarding) routes the IMAP session inside an encrypted SSH connection. It is used to reach a mail server that is only accessible from behind a bastion/jump host, or to add a second layer of transport security. The order is fixed: open the tunnel, authenticate to SSH, then connect and log in to IMAP — and close the tunnel with SshCloseTunnel when finished.

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 Imap.SshOpenTunnel method, which connects to an SSH server and prepares a
    --  tunnel for a later IMAP connection.  The 1st argument is the SSH server hostname and the
    --  2nd is the SSH port (usually 22).

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

    --  Open the SSH tunnel.  Port 22 is the usual SSH port.
    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

    --  The SSH password is not hard-coded in source.  Insert code here to obtain it from an
    --  interactive prompt, environment variable, or a secrets vault.
    DECLARE @sshPassword nvarchar(4000)

    --  Authenticate the SSH tunnel with a username and password.
    EXEC sp_OAMethod @imap, 'SshAuthenticatePw', @success OUT, 'sshUser', @sshPassword
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        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
        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
        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
        RETURN
      END

    --  Close the tunnel when finished with the IMAP connection.
    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
        RETURN
      END

    EXEC @hr = sp_OADestroy @imap


END
GO