SQL Server

SSH Commands that Prompt for Input, such as su

Demonstrates running a shell command that prompts for additional input, using su as the example. The technique is: send the command, read until the expected prompt appears, then send the response exactly as though it were typed.

Note: This example deliberately requests a PTY. su reads its password from a controlling terminal and refuses to run without one, so a pseudo-terminal is genuinely required here.

Background: Programs that ask for a password almost always insist on reading it from a real terminal rather than a pipe — a deliberate safeguard against passwords being fed in by scripts. That is why this is one of the cases where a PTY is necessary despite the general advice to avoid one. The interaction is a strict send-then-read-to-prompt cycle, and retrieving the received text between steps is essential: a prompt left in the buffer would make the next receive return immediately with the wrong output. Where possible, a passwordless sudo rule avoids this dance altogether.

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

    --  This example requires the Chilkat API to have been previously unlocked.
    --  See Global Unlock Sample for sample code.

    --  Demonstrates running a shell command that prompts for additional input, using "su" as the
    --  example.  The technique is: send the command, read until the expected prompt, then send the
    --  response as though it were typed.
    --  
    --  Note: This example deliberately DOES request a PTY.  "su" reads its password from a
    --  controlling terminal and refuses to run without one, so a pseudo-terminal is required here.

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

    DECLARE @hostname nvarchar(4000)
    SELECT @hostname = 'ssh.example.com'
    DECLARE @port int
    SELECT @port = 22
    EXEC sp_OAMethod @ssh, 'Connect', @success OUT, @hostname, @port
    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

    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

    --  Request a pseudo-terminal.  A "dumb" terminal avoids ANSI escape sequences in the output.
    DECLARE @termType nvarchar(4000)
    SELECT @termType = 'dumb'
    DECLARE @widthInChars int
    SELECT @widthInChars = 120
    DECLARE @heightInChars int
    SELECT @heightInChars = 40
    DECLARE @pixWidth int
    SELECT @pixWidth = 0
    DECLARE @pixHeight int
    SELECT @pixHeight = 0
    EXEC sp_OAMethod @ssh, 'SendReqPty', @success OUT, @channelNum, @termType, @widthInChars, @heightInChars, @pixWidth, @pixHeight
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    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

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

    --  Send the su command.  Linux/UNIX servers generally expect a bare LF, not a CRLF.
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, 'su' + CHAR(10), 'utf-8'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    --  Read until su prompts for the password.
    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatch', @success OUT, @channelNum, 'Password:', 'utf-8', @caseSensitive
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    DECLARE @cmdOutput nvarchar(4000)
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @cmdOutput 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 @cmdOutput

    --  Send the root password, exactly as if it were typed at the prompt.  Like the login password,
    --  this should come from a secure source rather than being hard-coded.
    DECLARE @suPassword nvarchar(4000)
    SELECT @suPassword = 'myRootPassword'
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, @suPassword, 'utf-8'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, CHAR(10), 'utf-8'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    --  Read the response up to the root shell prompt.  This will differ on your system.
    DECLARE @myShellPrompt nvarchar(4000)
    SELECT @myShellPrompt = 'root@myserver:~#'
    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatch', @success OUT, @channelNum, @myShellPrompt, 'utf-8', @caseSensitive
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    --  Retrieving the text also clears the receive buffer, which matters: if the prompt were left
    --  buffered, the next receive would match it immediately.
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @cmdOutput 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 @cmdOutput

    --  Now run a command as root.
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, 'ls' + CHAR(10), 'utf-8'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

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

    EXEC sp_OAMethod @ssh, 'GetReceivedText', @cmdOutput 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 @cmdOutput

    --  Additional commands follow the same pattern: send the command, read to the next prompt, then
    --  fetch and clear the receive buffer.

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

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

    EXEC sp_OAMethod @ssh, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @ssh


END
GO