SQL Server

Authenticate an SSH Tunnel with a Password

Demonstrates the Chilkat MailMan.SshAuthenticatePw method, which authenticates with the SSH server using an SSH username and password after an SSH tunnel has been opened. The first argument is the SSH account name and the second is the corresponding password. This example opens a tunnel, authenticates, and sends SMTP traffic through it.

Background: Opening an SSH tunnel and authenticating to the SSH server are two separate steps, and note the SSH credentials are distinct from the mail account credentials — the SSH login gets you through the tunnel, while SmtpUsername/SmtpPassword authenticate to the mail server at the far end. Password authentication is the simplest option; public-key authentication (SshAuthenticatePk) is generally preferred for automated systems.

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.SshAuthenticatePw method, which authenticates with the SSH server
    --  using an SSH username and password after an SSH tunnel has been opened.  The 1st argument
    --  is the SSH account name and the 2nd is the corresponding password.

    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 SMTP server connection.  These connections will travel through the tunnel.
    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.example.com'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 465
    EXEC sp_OASetProperty @mailman, 'SmtpSsl', 1
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'user@example.com'
    EXEC sp_OASetProperty @mailman, 'SmtpPassword', '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

    --  Authenticate with the SSH server using a username and password.
    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


    PRINT 'SSH tunnel authenticated.'

    --  SMTP operations now travel through the SSH tunnel.
    EXEC sp_OAMethod @mailman, 'VerifySmtpLogin', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        RETURN
      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
        RETURN
      END

    EXEC @hr = sp_OADestroy @mailman


END
GO