(SQL Server) SSH Exec (Execute Command Line)

Shows how to execute a command on an SSH server and retrieve the command output.

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
    DECLARE @iTmp0 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

    -- 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.108'
    SELECT @port = 22

    DECLARE @success int
    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, 'myLogin', 'myPassword'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Open a session channel.  (It is possible to have multiple
    -- session channels open simultaneously.)
    DECLARE @channelNum int

    EXEC sp_OAMethod @ssh, 'OpenSessionChannel', @channelNum OUT
    IF @channelNum < 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- The SendReqExec method starts a command on the remote
    -- server.   The syntax of the command string depends on the
    -- default shell used on the remote server to run the command.
    -- On Windows systems it is CMD.EXE.  On UNIX/Linux
    -- systems the user's default shell is typically defined in /etc/password.

    -- Here are some examples of command lines for <b>Windows SSH servers</b>:

    -- Get a directory listing:
    DECLARE @cmd1 nvarchar(4000)
    SELECT @cmd1 = 'dir'

    -- Do a nameserver lookup:
    DECLARE @cmd2 nvarchar(4000)
    SELECT @cmd2 = 'nslookup chilkatsoft.com'

    -- List a specific directory.  Given that the shell is CMD.EXE, backslashes must
    -- be used:
    DECLARE @cmd3 nvarchar(4000)
    SELECT @cmd3 = 'dir \temp'

    -- Execute a sequence of commands.  The syntax for CMD.EXE may be found
    -- here: http://technet.microsoft.com/en-us/library/bb490880.aspx.  Notice how the commands
    -- are separated by "&&" and the entire command must be enclosed in quotes:
    DECLARE @cmd4 nvarchar(4000)
    SELECT @cmd4 = '"cd \temp&&dir"'

    -- Here are two examples of command lines for <b>Linux/UNIX SSH servers</b>:

    -- Get a directory listing:
    DECLARE @cmd5 nvarchar(4000)
    SELECT @cmd5 = 'ls -l /tmp'

    -- Run a series of commands (syntax may depend on your default shell):
    DECLARE @cmd6 nvarchar(4000)
    SELECT @cmd6 = 'cd /etc; ls -la'

    -- Request a directory listing on the remote server:
    -- If your server is Windows, change the string from "ls" to "dir"
    EXEC sp_OAMethod @ssh, 'SendReqExec', @success OUT, @channelNum, 'ls'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Call ChannelReceiveToClose to read 
    -- output until the server's corresponding "channel close" is received.
    EXEC sp_OAMethod @ssh, 'ChannelReceiveToClose', @success OUT, @channelNum
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    -- Let's pickup the accumulated output of the command:
    DECLARE @cmdOutput nvarchar(4000)
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @cmdOutput OUT, @channelNum, '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

    -- Display the remote shell's command output:

    PRINT @cmdOutput

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

    EXEC @hr = sp_OADestroy @ssh


END
GO