Network Tunneling

Typically, it is presumed that a client can establish a direct connection to the server whenever there is a need for it. However, it is often necessary to use a proxy or bypass a firewall due to security reasons or network topology. This article describes different ways to establish a connection to an SQL Server using SDAC.

• Direct Connection
• Connection Through HTTP Tunnel
  • Connection Through Proxy and HTTP Tunnel
• Sample Delphi App That Connects to SQL Server Using HTTP/HTTPS Tunneling
• Additional Information

Direct Connection

Direct connection to the server means the server host is accessible from the client without extra routing and forwarding. This kind of connection is the simplest case. The only network settings you need are the hostname and port number. Such configuration is also the fastest and most reliable way of communicating with the server, which you can use whenever possible.

The following code illustrates the simplicity:

var
MSConnection: TMSConnection;
. . .
MSConnection.Authentication := auServer;
MSConnection.Username := 'user_name';
MSConnection.Password := 'pwd';
MSConnection.Server := 'Host\Instance';
MSConnection.Port := 1433;
MSConnection.Options.Provider := prDirect;
MSConnection.Connect;

Connection Through HTTP Tunnel

Sometimes, client computers are shielded by a firewall, not allowing you to connect to the server directly at the specified port. If the firewall allows HTTP connections, you can use SDAC and HTTP tunneling software to connect to the SQL Server. SDAC supports HTTP tunneling based on the PHP script.

An example of web script tunneling usage can be the following: you have a remote website, and access to its database through the port of the database server is forbidden. Access is limited solely to HTTP on port 80, and you must connect to the database from a remote computer, as you would with a usual direct connection.

You need to deploy the tunnel.php script, which is included in the provider package on the web server. It allows access to the database server to use HTTP tunneling. The script must be available through the HTTP protocol. You can verify if it is accessible with a web browser. The script can be found in the HTTP subfolder of the installed provider folder, e.g., %Program Files%\Devart\SDAC for Delphi XX\HTTP\tunnel.php. The only requirement for the server is PHP 5 support.

To connect to the database, you should set TMSConnection parameters for the usual direct connection, which will be established from the web server side, and set the Options.Provider property to prDirect. Also, you should set the following parameters specific to the HTTP tunneling:

Connecting Through Proxy and HTTP Tunnel

It is possible that direct access to the HTTP tunneling server from the client computer is not available. For example, the client address is 10.0.0.2, the server address is 192.168.0.10, and the SQL Server listens on port 1433. The client and server reside in different networks, so the client can reach it only through proxy at address 10.0.0.1, which listens on port 808. In this case, in addition to the TMSConnection.HttpOptions options: You have to set up the HttpOptions.ProxyOptions object as follows:

MSConnection := TMSConnection.Create(self);
MSConnection.Authentication := auServer;
MSConnection.Server := '192.168.0.10';
MSConnection.Port := 1433;
MSConnection.Username := 'sa'
MSConnection.Password := 'pwd';
MSConnection.Options.Provider := prDirect;
MSConnection.HttpOptions.Enabled := True;
MSConnection.HttpOptions.Url := 'http://server/tunnel.php';
MSConnection.HttpOptions.ProxyOptions.Hostname := '10.0.0.1';
MSConnection.HttpOptions.ProxyOptions.Port := '808;
MSConnection.HttpOptions.ProxyOptions.Hostname := 'ProxyUser';
MSConnection.HttpOptions.ProxyOptions.Password := 'ProxyPassword';
MSConnection.Connect;

Sample Delphi App That Connects to SQL Server Using HTTP/HTTPS Tunneling

• Open your browser and visit the URL of the tunnel.php script on your server to verify that the script has been properly installed.



• Run RAD Studio and select 'File -> New – > VCL Forms Application – Delphi'.
• Place the following components on the form: TMSConnection, TMSQuery, TDataSource, TDBGrid, TButton, and TCRSSLIOHandler. The last component is required when you connect via HTTPS. Note that the TCRSSLIOHandler is distributed with SecureBridge. The installation instructions for the component are provided in the Readme.html, located by default in "My Documents\Devart\SDAC for RAD Studio XX\Demos\TechnologySpecific\SecureBridge\DelphiXX".



• Select TDBGrid and set the DataSource property to DataSource1.



• In the TDataSource component, assign TMSQuery1 to the DataSet property.



• Select TMSQuery and set the Connection property to MSConnection1. Double-click the component and enter an SQL statement to be executed against the MS SQL database.



• Double-click TButton to switch to the unit view. Add the code invoking the Open method on the MSQuery1 object to activate the dataset when the button is clicked.



• Select the TMSConnection component. If you use the secure tunnel, set the IOHandler property to CRSSLIOHandler1. Expand the HttpOptions and enter the URL of the tunnel.php script on your server.



• In TMSConnection, expand Options and set the Provider property to prDirect.



• Double-click the TMSConnection component. Specify your server address, port, database name, username, and password of the MS SQL Server user. Click Connect to test the connection to the SQL Server.



• Press F9 to compile and run the project, and click the button to run the query against the database through HTTPS and display the data in the form.

Additional Information

Remember that traffic tunneling or encryption always increases CPU usage and network load. It is recommended that you use a direct connection whenever possible.