SQL Server

Open an SSH Tunnel for POP3

Demonstrates the Chilkat MailMan.SshOpenTunnel method, which connects to an SSH server and opens an SSH tunnel for MailMan's SMTP or POP3 connections. The first argument is the SSH server's hostname or IP address and the second is the SSH port. This example opens a tunnel, authenticates, performs a POP3 operation through it, and closes the tunnel.

Background: An SSH tunnel (port forwarding) wraps another protocol's traffic inside an encrypted SSH connection. Here, MailMan's POP3 or SMTP session is carried through the SSH server rather than connecting directly. This is useful when a mail server is only reachable from inside a network you can reach via SSH, or when policy requires all traffic to traverse a bastion host. Opening the tunnel is step one; you must then authenticate to the SSH server before the tunnel carries traffic.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the MailMan.SshOpenTunnel method, which connects to an SSH server and opens
    --  an SSH tunnel for MailMan's SMTP or POP3 connections.  The 1st argument is the SSH server
    --  hostname or IP address, and the 2nd is the SSH port.

    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 (port 22 is the standard SSH port).
    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

    --  Authenticate with the SSH server.
    EXEC sp_OAMethod @mailman, 'SshAuthenticatePw', @success OUT, 'sshUser', 'sshPassword'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        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

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

    EXEC @hr = sp_OADestroy @mailman


END
GO