SQL Server

Start a Remote Shell over SSH

Demonstrates the Chilkat Ssh.SendReqShell method, which starts a remote shell on an open session channel. The only argument is the channel number. This example starts a shell without a PTY, which is the recommended approach for scripting, and streams a command whose output ends with a marker.

Background: A shell request starts the account's login shell and leaves the channel open for ongoing input and output — the difference from exec, which runs a single command. When no PTY is requested, the shell detects it is attached to a raw pipe rather than a terminal and runs non-interactively: it prints no command prompt, does not echo the commands you send, and skips interactive login scripts. That yields clean, easily parsed output, but it also means there is no prompt to synchronize on — have each command echo a predictable marker, or rely on EOF and the channel exit status, to know when its output is complete.

Chilkat SQL Server Downloads

Download Chilkat for SQL 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
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the Ssh.SendReqShell method, which starts a remote shell on an open session
    --  channel.  The only argument is the channel number.  A shell can be started without a PTY,
    --  which is the recommended approach for scripting and automation.

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

    DECLARE @sshPort int
    SELECT @sshPort = 22
    EXEC sp_OAMethod @ssh, 'Connect', @success OUT, 'ssh.example.com', @sshPort
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    --  Normally you would not hard-code the password in source.  You should instead obtain it
    --  from an interactive prompt, environment variable, or a secrets vault.
    DECLARE @password nvarchar(4000)
    SELECT @password = 'mySshPassword'

    EXEC sp_OAMethod @ssh, 'AuthenticatePw', @success OUT, 'mySshLogin', @password
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    --  Open a session channel.  A negative return value indicates failure.
    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

    --  Start a shell WITHOUT requesting a PTY.  Without a PTY the shell sees it is connected to a
    --  raw data pipe and enters non-interactive mode: it suppresses the command prompt, does not
    --  echo typed commands back, and skips interactive login scripts.  The output is therefore
    --  clean and easily parseable -- but because no prompt is ever sent, code must not wait for
    --  one.  Instead, have each command print a predictable marker, or wait for EOF / channel close.
    EXEC sp_OAMethod @ssh, 'SendReqShell', @success OUT, @channelNum
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    PRINT 'Remote shell started.'

    --  Multiple commands can now be streamed into the shell, one after another.  Each command
    --  echoes a marker so the code can tell when its output is complete.
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, 'uname -a; echo DONE_MARKER' + CHAR(10), 'utf-8'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    --  IMPORTANT: Set a read timeout before receiving until a match.  ReadTimeoutMs defaults to 0,
    --  which means no limit -- without it, this call waits forever if the received data never
    --  contains a match.
    EXEC sp_OASetProperty @ssh, 'ReadTimeoutMs', 15000

    DECLARE @caseSensitive int
    SELECT @caseSensitive = 0
    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatch', @success OUT, @channelNum, 'DONE_MARKER', 'utf-8', @caseSensitive
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    DECLARE @output nvarchar(4000)
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @output OUT, @channelNum, 'utf-8'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    PRINT @output

    EXEC sp_OAMethod @ssh, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @ssh


END
GO