(SQL Server) SSH Commands to Sophos Router Device Console

Demonstrates how to establish an SSH session with a Sophos XG router and send commands in a device console session.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @ssh int
    -- Use "Chilkat_9_5_0.Ssh" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Ssh', @ssh OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @ssh, 'Connect', @success OUT, '172.16.16.16', 22
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

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

    -- Start a shell session.
    -- (The QuickShell method was added in Chilkat v9.5.0.65)
    DECLARE @channelNum int
    EXEC sp_OAMethod @ssh, 'QuickShell', @channelNum OUT
    IF @channelNum < 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- The Sophos router will send this initial prompt:

    -- 	Main Menu
    -- 
    -- 	    1.  Network  Configuration
    -- 	    2.  System   Configuration
    -- 	    3.  Route    Configuration
    -- 	    4.  Device Console
    -- 	    5.  Device Management
    -- 	    6.  VPN Management
    -- 	    7.  Shutdown/Reboot Device
    -- 	    0.  Exit
    -- 
    -- 	    Select Menu Number [0-7]: 

    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatch', @success OUT, @channelNum, 'Select Menu Number', 'utf-8', 1
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Show what we received so far:
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @sTmp0 OUT, @channelNum, 'utf-8'
    PRINT @sTmp0

    -- Send "4" <RETURN> to mimic typing in a shell session.
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, '4' + CHAR(10), 'utf-8'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- We should get a "console>" prompt.
    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatch', @success OUT, @channelNum, 'console>', 'utf-8', 1
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @sTmp0 OUT, @channelNum, 'utf-8'
    PRINT @sTmp0

    -- Send a command and get the output..
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, 'dnslookup host google.com' + CHAR(10), 'utf-8'
    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatch', @success OUT, @channelNum, 'console>', 'utf-8', 1
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @sTmp0 OUT, @channelNum, 'utf-8'
    PRINT @sTmp0

    -- Send another command and get the output, and so on...
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, 'dnslookup host microsoft.com' + CHAR(10), 'utf-8'
    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatch', @success OUT, @channelNum, 'console>', 'utf-8', 1
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @sTmp0 OUT, @channelNum, 'utf-8'
    PRINT @sTmp0

    EXEC sp_OAMethod @ssh, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @ssh


END
GO