SQL Server

SSH Tunnel TCP Socket Options

Demonstrates tuning the underlying TCP socket with the SoRcvBuf, SoSndBuf, TcpNoDelay, OutboundBindIpAddress, and OutboundBindPort properties. These normally should be left at their defaults.

Background: These are low-level knobs for specific situations. Larger send/receive buffers can improve throughput on high-bandwidth, high-latency ("long fat") links; TcpNoDelay disables Nagle's algorithm to cut latency for small interactive packets at the cost of efficiency; and binding the outbound socket to a specific local address or port is occasionally required by firewall rules or multi-homed hosts. Change them only with a concrete reason, since the defaults are tuned for the common case.

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 tuning the underlying TCP socket with the SshTunnel properties SoRcvBuf, SoSndBuf,
    --  TcpNoDelay, OutboundBindIpAddress, and OutboundBindPort.
    --  
    --  These normally should be left at their defaults; adjust them only for specific performance or
    --  networking requirements.

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

    --  Socket send/receive buffer sizes, in bytes.  Larger buffers can help throughput on
    --  high-latency links.
    EXEC sp_OASetProperty @tunnel, 'SoRcvBuf', 4194304
    EXEC sp_OASetProperty @tunnel, 'SoSndBuf', 262144

    --  Disable Nagle's algorithm to reduce latency for small, interactive packets.
    EXEC sp_OASetProperty @tunnel, 'TcpNoDelay', 1

    --  Bind the outbound socket to a specific local address/port before connecting.  0 (the default)
    --  lets the OS choose the local port; an empty address lets the OS choose the interface.
    EXEC sp_OASetProperty @tunnel, 'OutboundBindIpAddress', '192.168.1.50'
    EXEC sp_OASetProperty @tunnel, 'OutboundBindPort', 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

    PRINT 'Tunnel established with custom socket options.'

    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