Connecting to SQL Server Through SSH in Delphi

SSH is a protocol that allows users to securely log in 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.

You can use SSH to secure the network connection between a Delphi application and SQL Server. An SSH connection enables you to run shell commands as if you were physically operating the remote machine.

This page demonstrates how to use UniDAC to connect to SQL Server securely via an SSH tunnel.

To follow the steps on this page, you need an SSH client and an SSH server. You can create them using SecureBridge, a solution provided by Devart. It enables you to embed an SSH client into your Delphi application and, if needed, configure an SSH server. For more information, see the SecureBridge tutorial on configuring an SSH server. You can also build the SSHServer demo project, which is distributed with SecureBridge.

Your SQL Server instance must be configured to accept connections from the SSH tunnel.

Connecting to SQL Server via SSH is only supported in Direct mode. Direct mode—prDirect—is our proprietary implementation of the Tabular Data Stream (TDS) protocol. In this mode, UniDAC communicates directly with SQL Server without relying on any additional libraries.

UniConnection1.SpecificOptions.Values['Provider'] := 'prDirect';

• Connect Using SecureBridge
• Connect Using Any Third-Party SSH Tunnel

Connect Using SecureBridge

You can establish a secure SSH tunnel using SecureBridge without the need for third-party applications. SecureBridge provides all the necessary components to create an SSH client within your Delphi application.

Let's create a sample Delphi app that connects to SQL Server using SSH.

1. In RAD Studio, select File > New > Windows VCL Application - Delphi.



2. Place the following components from the Palette on the form:
   • TDBGrid
   • TButton
   • TUniDataSource
   • TUniQuery
   • TCRSSHIOHandler
   • TScFileStorage
   • TScSSHClient
   • TUniConnection
   • TSQLServerUniProvider
   With these components, the sample application will connect to SQL Server via SSH, execute a query, and display the retrieved data in a grid.

   

3. Select the TDBGrid component and, in the Object Inspector, set the DataSource property to the instance of TUniDataSource (UniDataSource1).



4. Select the TUniDataSource component and set the DataSet property to the instance of TUniQuery (UniQuery1).



5. Select the TCRSSHIOHandler component and set the Client property to the instance of TScSSHClient (ScSSHClient1).



6. Select the TScFileStorage component and, in the Path property, specify the directory to store information about keys.
7. Double-click the TScFileStorage component and generate a pair of keys for authenticating the server by the client.



8. Select the TScSSHClient component and assign values to the following properties:
   • Authentication – Select the value depending on the authentication method applicable for your SSH server: atPassword or atPublicKey.
   • HostKeyName – Specify the filename of the SSH server public key.
   • Hostname – Enter the host name or IP address of the SSH server.
   • KeyStorage – Set the property to the instance of TScFileStorage (ScFileStorage1).
   • Password – For password authentication, enter the password for the account on the SSH server.
   • Port – Specify the SSH port.
   • PrivateKeyName – For public key authentication, specify the filename of the client private key.
   • User – Enter the username for the account on the SSH server.



9. Select the TUniConnection component and set the IOHandler property to the instance of TCRSSHIOHandler (CRSSHIOHandler1).



10. Double-click the TUniConnection component.
11. On the Options tab, fill out the fields:
    • Provider – Select SQL Server.
    • Provider (key) – Select prDirect.

    

12. On the Connect tab, fill out the fields:
• Provider – Select SQL Server.
• Server – Enter the host name or IP address of the SQL Server instance.
• Port – Specify the port number used by the SQL Server instance.
• Username – Enter the username for the SQL Server account.
• Password – Enter the password for the SQL Server account.
• Database – Select the database.
13. Click Connect to test the connection to SQL Server, then click OK to close the dialog.



14. Select the TUniQuery component and set the Connection property to the instance of TUniConnection (UniConnection1).
15. Double-click the TUniQuery component, enter a SQL query to be run against the SQL Server database, and click OK.



16. Double-click the TButton component and add code to call the Open method on the TUniQuery component instance to activate the dataset when the button is clicked.



17. Press F9 to compile and run the application.
18. In the form that appears, click Button1 to run the query. Data appears in the grid.

Connect Using Any Third-Party SSH Tunnel

It is not mandatory to use SecureBridge and its components—you can use any other server that implements the SSH protocol.