SQL Server

SSH Tunnel Per-Client Logging and Client Identifier

Demonstrates the ClientLogDir and ClientIdentifier properties. When ClientLogDir is set, a separate log file is created for each tunnel client; ClientIdentifier is the SSH client-identification string sent during the handshake.

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

Background: When many clients share one tunnel, a single combined log is hard to follow, so ClientLogDir gives each connection its own file — the fastest way to diagnose one misbehaving client. ClientIdentifier sets the version string the server sees; overriding the default is occasionally needed for compatibility with servers that key behavior off the client identification, or simply to label your application in server logs.

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 per-client logging properties ClientLogDir and ClientIdentifier.

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

    --  When ClientLogDir is set, a separate log file is created for each tunnel client in this
    --  directory, which is useful for troubleshooting individual connections.
    EXEC sp_OASetProperty @tunnel, 'ClientLogDir', 'qa_output/client_logs'

    --  The SSH client-identification string sent to the SSH server during the handshake.
    EXEC sp_OASetProperty @tunnel, 'ClientIdentifier', 'SSH-2.0-MyTunnelApp_1.0'

    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

    EXEC sp_OAGetProperty @tunnel, 'ClientLogDir', @sTmp0 OUT
    PRINT 'Tunnel running; each client is logged in ' + @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