SQL Server

SSH Tunnel Listener Activity Log

Demonstrates the listener-log properties KeepAcceptLog, AcceptLog, and AcceptLogPath, which record the activity of the background listener thread started by BeginAccepting.

Note: The log paths are relative to the application's current working directory. Absolute paths may also be used.

Background: Because the listener runs on a background thread, an activity log is often the only practical way to see what it is doing — which connections it accepted and when. KeepAcceptLog must be enabled for the in-memory AcceptLog to be populated; AcceptLogPath is an independent file-based log that persists across the run. This log covers the listener specifically, as distinct from the TunnelLog that records the SSH tunnel threads.

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 SshTunnel listener-log properties KeepAcceptLog, AcceptLog, and AcceptLogPath.
    --  
    --  These log the activity of the background listener thread started by BeginAccepting.

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

    --  Retain the listener activity log in memory so it can be read from the AcceptLog property.
    EXEC sp_OASetProperty @tunnel, 'KeepAcceptLog', 1

    --  Optionally also write the listener activity to a file (separate from the in-memory log).
    EXEC sp_OASetProperty @tunnel, 'AcceptLogPath', 'qa_output/accept.log'

    EXEC sp_OASetProperty @tunnel, 'DestHostname', 'db.internal.example.com'
    EXEC sp_OASetProperty @tunnel, 'DestPort', 5432

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

    --  Normally you would not hard-code the password in source.  You should instead obtain it
    --  from an interactive prompt, environment variable, or a secrets vault.
    DECLARE @password nvarchar(4000)
    SELECT @password = 'mySshPassword'

    EXEC sp_OAMethod @tunnel, 'AuthenticatePw', @success OUT, 'mySshLogin', @password
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        RETURN
      END

    DECLARE @listenPort int
    SELECT @listenPort = 1080
    EXEC sp_OAMethod @tunnel, 'BeginAccepting', @success OUT, @listenPort
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        RETURN
      END

    --  ... the listener accepts connections for a while ...

    --  Read the accumulated in-memory listener log.
    EXEC sp_OAGetProperty @tunnel, 'AcceptLog', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @waitForThreadExit int
    SELECT @waitForThreadExit = 1
    EXEC sp_OAMethod @tunnel, 'CloseTunnel', @success OUT, @waitForThreadExit
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @tunnel
        RETURN
      END

    EXEC @hr = sp_OADestroy @tunnel


END
GO