SQL Server

SSH Execute Remote Commands

See more SSH Examples

Shows how to execute a command on an SSH server and retrieve the command output. 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 @iTmp0 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 <> 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, 'theSshLogin', 'theSshPassword'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Send some commands and get the output.
    DECLARE @strOutput nvarchar(4000)
    EXEC sp_OAMethod @ssh, 'QuickCommand', @strOutput OUT, 'df', 'ansi'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    PRINT '---- df ----'

    PRINT @strOutput

    EXEC sp_OAMethod @ssh, 'QuickCommand', @strOutput OUT, 'echo hello world', 'ansi'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    PRINT '---- echo hello world ----'

    PRINT @strOutput

    EXEC sp_OAMethod @ssh, 'QuickCommand', @strOutput OUT, 'date', 'ansi'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    PRINT '---- date ----'

    PRINT @strOutput

    -- --------------
    -- Sample output:

    -- 	---- df ----
    -- 	Filesystem    512-blocks      Used  Available Capacity  iused     ifree %iused  Mounted on
    -- 	/dev/disk2    2176716032 265736304 1910467728    13% 33281036 238808466   12%   /
    -- 	devfs                382       382          0   100%      662         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
    -- 
    -- 	---- echo hello world ----
    -- 	hello world
    -- 
    -- 	---- date ----
    -- 	Thu Dec 22 17:19:32 CST 2016

    EXEC @hr = sp_OADestroy @ssh


END
GO