(SQL Server) Using sudo in an SSH Shell Session

See more SSH Examples

Demonstrates how to run a command as sudo in a shell 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 requires 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 @port int
    SELECT @port = 22
    DECLARE @success int
    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

    -- 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

    -- Construct a StringBuilder with our command ("ls") run with "sudo"
    -- 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.)

    -- Using "sudo -S" causes the sudo prompt to be written to stderr,
    -- and the password is read from stdin (i.e. from the echo command ouput).
    -- The sudo commands -p option allows us to set the prompt, and we can set it to
    -- the empty string, thus eliminating the prompt from the stderr completely.
    -- This allows us to run the command as root (super-user) with no prompt.
    DECLARE @sbCommands int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbCommands OUT

    EXEC sp_OAMethod @sbCommands, 'Append', @success OUT, 'echo "theSshPassword" | sudo -S -p "" ls' + CHAR(10)

    -- Add another command 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 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @sbCommands
        RETURN
      END

    -- 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

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

    -- 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

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


END
GO