SQL Server

SSH Quick/Simple Shell Session

See more SSH Examples

Demonstrates the simplified way to run multiple commands in a shell session on an SSH 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
    -- 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 requires 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

    DECLARE @port int
    SELECT @port = 22
    EXEC sp_OAMethod @ssh, 'Connect', @success OUT, 'the-ssh-server.com', @port
    IF @success = 0
      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, 'theSshLogin', 'theSshPassword'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Start a shell session.
    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

    -- Construct a StringBuilder with multiple commands, one per line.
    -- Note: The line-endings are potentially important.  Some SSH servers may
    -- require either LF or CRLF line endings.  (Unix/Linux/OSX servers typically
    -- use bare-LF line endings.  Windows servers likely use CRLF line endings.)
    DECLARE @sbCommands int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbCommands OUT

    EXEC sp_OAMethod @sbCommands, 'Append', @success OUT, 'echo hello world' + CHAR(10)
    EXEC sp_OAMethod @sbCommands, 'Append', @success OUT, 'date' + CHAR(10)
    EXEC sp_OAMethod @sbCommands, 'Append', @success OUT, 'df' + CHAR(10)

    -- For our last command, we're going to echo a marker string that
    -- we'll use in ChannelReceiveUntilMatch below.
    -- The use of single quotes around 'IS' is a trick so that the output
    -- of the command is "THIS IS THE END OF THE SCRIPT", but the terminal echo
    -- includes the single quotes.  This allows us to read until we see the actual
    -- output of the last command.
    EXEC sp_OAMethod @sbCommands, 'Append', @success OUT, 'echo THIS ''IS'' THE END OF THE SCRIPT' + CHAR(10)

    -- Send the commands..
    EXEC sp_OAMethod @sbCommands, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, @sTmp0, 'ansi'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @sbCommands
        RETURN
      END

    -- Receive output up to our marker.
    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatch', @success OUT, @channelNum, 'THIS IS THE END OF THE SCRIPT', 'ansi', 1

    -- Send an EOF to indicate no more commands will be sent.
    -- For brevity, we're not checking the return values of each method call.
    -- Your code should check the success/failure of each call.
    EXEC sp_OAMethod @ssh, 'ChannelSendEof', @success OUT, @channelNum

    -- Close the channel.
    -- It is important to close the channel only after receiving the desired output.
    EXEC sp_OAMethod @ssh, 'ChannelSendClose', @success OUT, @channelNum

    -- Get any remaining output..
    EXEC sp_OAMethod @ssh, 'ChannelReceiveToClose', @success OUT, @channelNum

    -- Get the complete output for all the commands in the session.

    PRINT '--- output ----'
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @sTmp0 OUT, @channelNum, 'ansi'
    PRINT @sTmp0

    -- Here's our actual sample output:

    -- 	Last login: Thu Dec 22 20:19:09 2016 from chilkat13
    -- 
    -- 	echo hello world
    -- 	date
    -- 	df
    -- 	echo THIS 'IS' THE END OF THE SCRIPT
    -- 	chilkatosx:~ chilkat$ echo hello world
    -- 	hello world
    -- 	chilkatosx:~ chilkat$ date
    -- 	Thu Dec 22 20:30:48 CST 2016
    -- 	chilkatosx:~ chilkat$ df
    -- 	Filesystem    512-blocks      Used  Available Capacity  iused     ifree %iused  Mounted on
    -- 	/dev/disk2    2176716032 265768928 1910435104    13% 33285114 238804388   12%   /
    -- 	devfs                383       383          0   100%      664         0  100%   /dev
    -- 	map -hosts             0         0          0   100%        0         0  100%   /net
    -- 	map auto_home          0         0          0   100%        0         0  100%   /home
    -- 	/dev/disk3s2      374668    374668          0   100%    93665         0  100%   /Volumes/Google Chrome
    -- 	chilkatosx:~ chilkat$ echo THIS 'IS' THE END OF THE SCRIPT
    -- 	THIS IS THE END OF THE SCRIPT
    -- 	chilkatosx:~ chilkat$ 

    EXEC @hr = sp_OADestroy @ssh
    EXEC @hr = sp_OADestroy @sbCommands


END
GO