SQL Server
SQL Server
SSH Remote Shell
See more SSH Examples
Demonstrates how to start a shell on a remote SSH server, run a command, and retrieve the output.Chilkat SQL Server Downloads
-- 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 assumes 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
-- Hostname may be an IP address or hostname:
DECLARE @hostname nvarchar(4000)
SELECT @hostname = 'www.some-ssh-server.com'
DECLARE @port int
SELECT @port = 22
-- Connect to an SSH server:
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
-- Some SSH servers require a pseudo-terminal
-- If so, include the call to SendReqPty. If not, then
-- comment out the call to SendReqPty.
-- Note: The 2nd argument of SendReqPty is the terminal type,
-- which should be something like "xterm", "vt100", "dumb", etc.
-- A "dumb" terminal is one that cannot process escape sequences.
-- Smart terminals, such as "xterm", "vt100", etc. process
-- escape sequences. If you select a type of smart terminal,
-- your application will receive these escape sequences
-- included in the command's output. Use "dumb" if you do not
-- want to receive escape sequences. (Assuming your SSH
-- server recognizes "dumb" as a standard dumb terminal.)
DECLARE @termType nvarchar(4000)
SELECT @termType = 'dumb'
DECLARE @widthInChars int
SELECT @widthInChars = 120
DECLARE @heightInChars int
SELECT @heightInChars = 40
-- Use 0 for pixWidth and pixHeight when the dimensions
-- are set in number-of-chars.
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 <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Start a shell on the channel:
EXEC sp_OAMethod @ssh, 'SendReqShell', @success OUT, @channelNum
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Start a command in the remote shell. This example
-- will send a "ls" command to retrieve the directory listing.
EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, 'ls' + CHAR(13) + CHAR(10), 'ansi'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Send an EOF. This tells the server that no more data will
-- be sent on this channel. The channel remains open, and
-- the SSH client may still receive output on this channel.
EXEC sp_OAMethod @ssh, 'ChannelSendEof', @success OUT, @channelNum
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Read whatever output may already be available on the
-- SSH connection. ChannelReadAndPoll returns the number of bytes
-- that are available in the channel's internal buffer that
-- are ready to be "picked up" by calling GetReceivedText
-- or GetReceivedData.
-- A return value of -1 indicates failure.
-- A return value of -2 indicates a failure via timeout.
-- The ChannelReadAndPoll method waits
-- for data to arrive on the connection usingi the IdleTimeoutMs
-- property setting. Once the first data arrives, it continues
-- reading but instead uses the pollTimeoutMs passed in the 2nd argument:
-- A return value of -2 indicates a timeout where no data is received.
DECLARE @n int
DECLARE @pollTimeoutMs int
SELECT @pollTimeoutMs = 2000
EXEC sp_OAMethod @ssh, 'ChannelReadAndPoll', @n OUT, @channelNum, @pollTimeoutMs
IF @n < 0
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Close the channel:
EXEC sp_OAMethod @ssh, 'ChannelSendClose', @success OUT, @channelNum
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ssh
RETURN
END
-- Perhaps we did not receive all of the commands output.
-- To make sure, call ChannelReceiveToClose to accumulate any remaining
-- 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