Using SSH Protocol ? (CONNECTION STRING)

[avista]

Hi,
Is it possible to connect to sql server using SSH Protocol ?
(I mean to connnect from FWH Application)
Best Regards,

[ADutheil]

With a linux server running MySQL I´d try something like this:

Code: Select all | Expand

ssh -L 3456:mysqlserver:3306 user@sshserver
 

After authentication I´d set the MySQL-Client to query on port 3456. Not tested!

[avista]

Thanks André for reply.
I mean to connnect from FWH application ,
Is it possible to use TODBC and TDBODBCD classes via SSH Protocol ?
Is it possible to create ODBC to informix server via SSH protocol ?
In other cases i use informix CLI-32 to create ODBC and connecting with no problems.
But to some servers i must to connect SSH Protocol with defined port ...
Or i need something else ?
Best regards,

[avista]

Noone use SSH protocol to connect to SQL server ?
Regards ?

[avista]

Hi,
Every sugestion will be helpfyl.
What driver to use to connect from fivewin application to INFORMIX server via SSH Protocol and how to create connection string or is it chance to create ODBC using SSH Protocol or i need to do something else ?

Best rehards,

[Enrico Maria Giordano]

Sorry, I don't know.

EMG

[metro]

Pozdrav Avista

U koliko je problem još aktualan, javi te pa ću vam poslati moje riješenje za spajanje na SSH Data server.
Baza je MySQL što nije bitno. može bilo koja.

Koristio sam ChilkatSSH.dll i radi odlicno.



Hello Avista

The problem is much more topical, and told so I'll send you my solution to connect to the SSH Data server .
MySQL database is not essential. Can anyone.

I used ChilkatSSH.dll and works great. (FWH,xHB)

mail: info@metrokom.hr
Best regards

[avista]

Zdravo,
Hvala na poruci,
Svakako da je problem jos aktuelan
(Malo sam bio otsutan i nisam ranije procitao reply ranije)
Molimte za sugestije
Hvala jos jednom

Best regards,

sunrised@t-home.mk

[metro]

hi

This is the code with which it connects to the SSH server.

Code: Select all | Expand

  
****************************************   
   // variable from ini file

   //--- parametri SSH pristupa
   Public cSSHServer      := ""
   Public cSSHPort        := ""
   Public cSSHUserName    := "root"            
   Public cSSHPassword    := "abc!!!"    

   Public cSSHDestHostname:= "localhost"   
   Public cSSHDestPort    := "3306"        // MySQL
   
   Public cListenPort     := "3404"         



**********************************************
FUNCTION TestLogin()
   local cDSN,nDetected,cConnString:=""
   local local1:= .f.
   Public sshTunnel         
   //------------------------------------------- testiraj ID i Pass
   if lSSH  
   
      cListenPort := cPort
      
      local1 := CreateSSH()
    
      if (local1)
         lMozeStart := .t.
      else      
         msginfo( " Greska pri SSH pristupu serveru. Vidi ssh.log za detalje !!! "  )
         lMozeStart := .F.
         Return .t.
      endif
   endif
   //-------------------------------------------  formiraj conection string ovisno o bazi
   cConnString := SetString(cRDD)
   //-------------------------------------------  testiraj konekciju  
   nDetected   := DetectDBFromDSN( cConnString )
   
   If nDetected > SYSTEMID_UNKNOW             
   
      nCnn := SR_AddConnection( nDetected, cConnString )

      If nCnn < 0
         msginfo( "Greska pri konektiranju . Vidi sqlerror.log za detalje !!! " + str(nCnn) )
         lMozeStart := .F.
      else
         lMozeStart := .t.
         SetCP(nCnn) //ovo treba za linux - isprobano na mysql -u
      EndIf   
   
   EndIf

   Return .t.

****************************************   
Function CreateSSH()   
   local success
   local local1 := .t.
  
      sshTunnel = CreateObject("Chilkat.SshTunnel")                  // Objekt      

      success = sshTunnel:UnlockComponent("SSHxxxxxxxx yyyyyyyyyyyy")          
      
      If (success <> 1)                                                             
         local1 := .f.
         sshTunnel:SaveLastError(_LogDir + "\ssh.log")                                    
      endif
      
           
      sshTunnel:DestPort = val(cSSHDestPort)               //3306   za MySQL  
      sshTunnel:DestHostname = cSSHDestHostname   //"localhost"             
                                                                         
      sshTunnel:SshHostname = cSSHServer            //"85.94.73.203"                  
      sshTunnel:SshPort = val(cSSHPort)                  //9202                          
      sshTunnel:SshLogin = cSSHUserName             //"root"                              
      sshTunnel:SshPassword = cSSHPassword         //"abcd!!!"                  
                                                                         
  
     
      success = sshTunnel:BeginAccepting( val(cListenPort) )                           
                                                                         
      If (success <> 1)                                                    
         sshTunnel:SaveLastError(_LogDir + "\ssh.log")                                    
         local1 := .f.

      EndIf                                                                   
                                             
             

   Return(local1)   
   
****************************************   
Function StopSSH()        

   if lSSH  // 

      // sad treba isključiti SSH
      success = sshTunnel:StopAccepting()
      If (success <> 1) 
         sshTunnel:SaveLastError(_LogDir + "\ssh.log")                                    
      EndIf
   
      //  If any background tunnels are still in existence (and managed
      //  by a single SSH tunnel pool background thread), stop them...
      maxWaitMs = 1000
      success = sshTunnel:StopAllTunnels(maxWaitMs)
      If (success <> 1) 
         sshTunnel:SaveLastError(_LogDir + "\ssh.log")                                    
      EndIf
   
   EndIf
   
   Return 
   

Best regards,

[devtuxtla]

Hello meters

Excellent solution, I'll try, but can not locate ChilkatSSH.dll, has a version of this dll that we can share?

regards

[metro]

Hello meters

Excellent solution, I'll try, but can not locate ChilkatSSH.dll, has a version of this dll that we can share?

regards

Contact me on private mail .

[Baxajaun]

Devtuxtla, Metro,

i need to connect to Linux box to execute commands. Can you help me ?

Thanks in advance.

Best regards

[metro]

Hi Baxajaun


According to the definition of Chilkat SSH.dll is:
The SSH / SFTP ActiveX component provides two objects: A client-side SSH2 implementation for executing commands and shell sessions on Unix / Windows SSH servers, and an SFTP implementation for file transfer and remote file management over SSH.

In the description of options are:
Remote shell over SSH.
Execute commands over SSH channel.

By this, we would say that it can execute commands on a Linux machine.
I use chilkatSSH.dll for remote access to MySQL on a Linux server.
I have no experience with the execution of the command, but I am convinced that it can.
Of course, I'll help as much as I can

I found an example written in VB Script, which would be easy to write in FWH / XHB:

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

Code: Select all | Expand

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    --  Important: It is helpful to send the contents of the
    --  ssh.LastErrorText property when requesting support.

    -- This sp_OACreate statement uses the new single-DLL ActiveX for v9.5.0
    DECLARE @ssh int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Ssh', @ssh OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  Any string automatically begins a fully-functional 30-day trial.
    DECLARE @success int

    EXEC sp_OAMethod @ssh, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        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

    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/libr ... 90880.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'
    IF @cmdOutput Is NULL 
      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
 

end Demonstrates how to start a shell on a remote SSH server, run a command, and retrieve the output.

Code: Select all | Expand

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    --  Important: It is helpful to send the contents of the
    --  ssh.LastErrorText property when requesting support.

    -- This sp_OACreate statement uses the new single-DLL ActiveX for v9.5.0
    DECLARE @ssh int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Ssh', @ssh OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  Any string automatically begins a fully-functional 30-day trial.
    DECLARE @success int

    EXEC sp_OAMethod @ssh, 'UnlockComponent', @success OUT, '30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

    --  Connect to an SSH server:
    DECLARE @hostname nvarchar(4000)

    DECLARE @port int

    --  Hostname may be an IP address or hostname:
    SELECT @hostname = 'www.some-ssh-server.com'
    SELECT @port = 22

    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'
    IF @cmdOutput Is NULL 
      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

Dubravko

[Baxajaun]

Metro,

thanks a lot for your time and help.

Best regards