SQL Server

SSH Tunnel (Port Forwarding via direct-tcpip channel)

See more SSH Examples

Demonstrates how to create an SSH tunnel to a remote hostname:port via a direct-tcpip channel. 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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    -- Connect to an SSH server:
    DECLARE @hostname nvarchar(4000)

    DECLARE @port int

    -- Hostname may be an IP address or hostname:
    SELECT @hostname = '192.168.1.117'
    SELECT @port = 22

    EXEC sp_OAMethod @ssh, 'Connect', @success OUT, @hostname, @port
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Wait a max of 5 seconds when reading responses..
    EXEC sp_OASetProperty @ssh, 'IdleTimeoutMs', 5000

    -- Authenticate using login/password:
    EXEC sp_OAMethod @ssh, 'AuthenticatePw', @success OUT, 'chilkat', 'myPassword'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Open a direct-tcpip channel.  We want the SSH server to connect
    -- to www.chilkatsoft.com, port 80 (i.e. the web server).
    -- Data sent through the SSH tunnel is forwarded to the remote
    -- host:port.  (Note: The remote host:port does not need to be 
    -- a web server.  It can be anything.  It can be your own
    -- customer application server that listens on a port, or any
    -- other type of server.)
    -- When we read from the SSH channel, we'll be reading data
    -- sent from the remote host:port (i.e. the web server in this
    -- example).
    DECLARE @channelNum int

    EXEC sp_OAMethod @ssh, 'OpenDirectTcpIpChannel', @channelNum OUT, 'www.chilkatsoft.com', 80
    IF @channelNum < 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Build a simple HTTP GET request for http://www.chilkatsoft.com/xyz.html
    DECLARE @httpReq nvarchar(4000)
    SELECT @httpReq = 'GET /xyz123.html HTTP/1.1' + CHAR(13) + CHAR(10) + 'Host: www.chilkatsoft.com' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

    -- Send the HTTP request:
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, @httpReq, 'ansi'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Get the HTTP response.
    -- First read the HTTP response header which ends with a double CRLF.
    -- Calling ChannelReceiveUntilMatch will receive until match string is seen,
    -- or until a timeout occurs (IdleTimeoutMs property).  ChannelReceiveUntilMatch
    -- may read beyond the match string, but it will stop reading as soon as the match
    -- string is seen.
    DECLARE @caseSensitive int
    SELECT @caseSensitive = 0
    DECLARE @matchStr nvarchar(4000)
    SELECT @matchStr = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatch', @success OUT, @channelNum, @matchStr, 'ansi', @caseSensitive
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Extract the HTTP header from the receive buffer.
    -- (GetReceiveTextS extracts up to and including the match string from the receive buffer)
    DECLARE @responseHeader nvarchar(4000)

    EXEC sp_OAMethod @ssh, 'GetReceivedTextS', @responseHeader OUT, @channelNum, @matchStr, 'ansi'

    PRINT '---- HTTP Response Header ----'

    PRINT @responseHeader

    -- Now get the body of the HTTP response (this is the HTML content
    -- of http://www.chilkatsoft.com/xyz.html
    -- It's possible we've already received the entire HTTP response in the
    -- call to ChannelReceiveUntilMatch.  Therefore, we'll poll for any remaining data
    -- and wait a max of .2 seconds.
    DECLARE @numBytesRead int

    DECLARE @pollTimeoutMs int
    SELECT @pollTimeoutMs = 200
    EXEC sp_OAMethod @ssh, 'ChannelPoll', @numBytesRead OUT, @channelNum, @pollTimeoutMs
    -- We're not checking for an error here.
    -- A return value of -2 means that no data was available and the poll simply timed out (not an error)
    -- A return value of -1 indicates an error.
    -- A return value greater than 0 indicates that additional data was received.


    PRINT '---- HTML BODY ----'

    -- Extract the remainder of the accumulated data in the internal receive buffer.
    -- This should be our HTML body:
    DECLARE @htmlBody nvarchar(4000)

    EXEC sp_OAMethod @ssh, 'GetReceivedText', @htmlBody OUT, @channelNum, 'ansi'

    PRINT @htmlBody

    -- Close the channel:
    EXEC sp_OAMethod @ssh, 'ChannelSendClose', @success OUT, @channelNum
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Disconnect
    EXEC sp_OAMethod @ssh, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @ssh


END
GO