MyDAC Connecting via SSH

Connecting to MySQL Through SSH in Delphi

SSH is a protocol that allows users to securely log onto and interact with remote systems on the Internet by connecting a client program to an SSH server. SSH provides a mechanism for establishing a cryptographically secured connection between two endpoints, a client and a remote server, which authenticate each other and exchange messages. It employs different forms of symmetrical encryption, asymmetrical encryption, and hashing.

It is possible to use SSH to secure the network connection between a Delphi application and a PostgreSQL server. You execute shell commands in the same fashion as if you were physically operating the remote machine.

Devart offers a solution called SecureBridge that allows you to create a Delphi SSH client and a server. You can embed the SSH client into your application and install the SSH server on a remote machine where the MySQL server resides. The SSH client connects to the SSH server, which sends all commands to the remote MySQL server. This tutorial demonstrates how to create a sample Delphi application that connects to MySQL using SSH as the encryption method.

SSH key-based authentication is done by public and private keys that a client uses to authenticate itself when logging into an SSH server. The server key is used is used by the client to authenticate the SSH server and is specified in the TScSSHClient.HostKeyName property. The client key is used by the SSH server to authenticate the client and is specified in the TScSSHClient.PrivateKeyName property. Note that the private key contains the public key. See SecureBridge tutorial on configuring the SSH server.

An SSH server is required to replicate the steps in this tutorial and encrypt the network connection between the client application and the MySQL server. You can build the SSH server demo project that is distributed with SecureBridge ('Documents\Devart\MyDAC for RAD Studio\Demos\TechnologySpecific\SecureBridge\Demo') and run the executable file.

After installing MyDAC and SecureBridge software on your system, install the TMySSHIOHandler component in RAD Studio to bind MyDAC with SecureBridge. The installation instructions are provided in the Readme.html, which is located by default in "My Documents\Devart\MyDAC for RAD Studio XX\Demos\TechnologySpecific\SecureBridge\".

Sample Delphi App that Connects to MySQL Using SSH

1. Run RAD Studio and select 'File -> New – > VCL Forms Application – Delphi'.
2. Place the following components on the form: TMySSHIOHandler, TMyConnection, TMyQuery, TScFileStorage, TScSSHClient, TDataSource, TDBGrid, and TButton. The sample application will connect to the MySQL server via SSH, run a selection operation against the database, and display the obtained rows in the grid.



3. Select the TDBGrid and set the DataSource property to DataSource1.



4. In the TDataSource component, assign MyQuery1 to the DataSet property.



5. Select the TMyQuery and set the Connection property to MyConnection1. Double-click the component and enter a SQL statement to be executed against the MySQL database.



6. Double-click the TButton to switch to the unit view. Add the code to call the Open method on the MyQuery1 object to activate the dataset when the button is clicked.



7. In the TMySSHIOHandler component, assign ScSSHClient1 to the Client property.



8. Select the TScFileStorage component and specify in the Path property the directory where keys are stored on your system. Double-click the component and generate a pair of keys for authenticating the server by the client.

   

9. Set the Authentication property to atPublicKey in the TScSSHClient component. In HostKeyName, specify the server public key. In PrivateKeyName, specify the client private key. The Hostname property holds the address of your server. Assign ScFileStorage1 to the KeyStorage property. Enter your username on the server in the User property. Specify the SSH port in the Port property.



10. Double-click the TMyConnection component. Specify your server address, port, database name (optionally), and username and password for the MySQL user. Set the IOHandler property to CRSSHIOHandler1. Click Connect to check connection to the MySQL server.



11. Press F9 to compile and run the project. Click the button to run the query against the database and display the data in the form.

2. SSH Using OpenSSH or Any Other Third-Party SSH Tunnel

It is not obligatory to use SecureBridge TScSSHServer component as an SSH server - you can use any other server that implements the SSH protocol.

The following is the step-by-step sequence of actions for the easiest case of using OpenSSH for Windows. The detailed description of each command you can see in the documentation for OpenSSH.

1. Download OpenSSH for Windows from http://www.sourceforge.net/projects/sshwindows/
2. Install SSH server
   • Choose a machine that will be used as SSH server. It does not have to be the same machine that is a MySQL server, but communication channel between SSH server and MySQL server must be protected
   • Using Windows Control Panel create a user and set a password for him. For example, SSHUser with password SSHPass
   • Install Open SSH. It is enough to install only Server components
   • Open OpenSSH/bin folder
   • Add SSHUser to the list of allowed users:
     mkpasswd -l -u SSHUser >> ..\etc\passwd
   • Use mkgroup to create a group permissions file
     mkgroup -l >> ..\etc\group
   • Run OpenSSH service
     net start opensshd
3. Install SSH client
   • Choose a machine that will be used as SSH client. It does not have to be the same machine where client application (MySQL client) is running, but communication channel between SSH client and MySQL client must be protected
   • Install Open SSH to SSH client. You may not install server components
   • Run SSH client
     

     ssh.exe -L <SSH port>:<MySQL server>:<MySQL server port> <SSHUser>@<SSH server>
     <SSH port> - port number of SSH client that will be redirected to the corresponding port of MySQL server
     <MySQL server> - name or IP address of the machine where MySQL server is installed
     <MySQL server port> - number of MySQL server port. As usual, 3306.
     <SSHUser> - user name created in p. 2
     <SSH server> - name or IP address of the machine where SSH server is installed in p. 2

     For example,

     ssh.exe -L 3307:server:3306 [email protected]

     At the first start you will be suggested to confirm a connection with the specified SSH server. Enter "yes" for confirmation.
     On each start of SSH you must enter a password set in p. 2

4. Configure TMyConnection
   

   MyConnection1.Server := <SSH client>;
   MyConnection1.Port := <SSH port>;

   If SSH client was installed at the same machine as MySQL client, you can assign 'localhost' to MyConnection1.Server.

Pay attention that in the specified sequence above check of SSHUser authentication is performed by Windows. About the methods of higher protection (key authentication etc) see documentation for OpenSSH.

To get more detailed information on using encrypted connections refer to MySQL Reference Manual.