Configuring SSL Encrypted Connections Between the SQL Gateway and the Microsoft SQL Server Database

Procedure

In order to encrypt the connection between the SQL Gateway and the Microsoft SQL Server database by SSL, perform the following steps:

Step	Action
1	Create a self-signed certificate for the server.
2	Configure the Microsoft SQL Server.
3	Configure the SQL Gateway.

The installation of the SQL Gateway contains a batch file that creates a self-signed certificate for the Microsoft SQL Server.

As a prerequisite for executing the batch file, the MakeCert tool from Microsoft must be available on the PC.

Proceed as follows

Step	Action	Details
1	Open the batch file.	In the Settings > Database Certificates tab, select Open folder > Microsoft SQL.
2	Adapt the batch file template.	Replace the placeholder `"SQLSERVER_TEST"` by the name that will be given to your root certificate. Replace the placeholder `"Full_Computer_Name"` by the name of the server PC. Copy the Full computer name from the Control Panel > System and Security > System of the server PC.
3	Execute the batch file on the server PC (the PC on which the Microsoft SQL Server is running).	Two certificates are created: Root certificate (sqlTestCA.cer) Server certificate (sqlTest.cer) that is added to the Personal folder of the certificate store. You are requested to enter two passwords: one for each certificate.
4	Import the root certificate (sqlTestCA.cer) to the Trusted Root Certification Authorities folder of the certificate store on the server PC.	–
5	Assign access rights to the server certificate in order to allow the SQL server to access the certificate.	You can copy the server name from the SQL Server Configuration Manager.
6	Select the certificate in the configuration of the SQL server as described in the section Configuring the Microsoft SQL Server and restart the SQL server.	–
7	Import the root certificate to the Trusted Root Certification Authorities folder of the certificate store on the client PC (the PC on which the SQL Gateway is running).	Result: The server certificate is accepted by the client PC.

Configure the Microsoft SQL Server for using SSL encryption:

Step	Action
1	Open the SQL Server Configuration Manager.
2	Right-click the Protocols node for the SQL server instance and execute the Properties command from the context menu.
3	Set the server certificate.
4	In order to use SSL encryption for the database connections, set the parameter Force Encryption to Yes.

In the Configuration tab of the SQL Gateway, configure the SSL parameters:

Step	Action
1	Select the entry from the list of Database Servers.
2	On the right-hand side, set the parameter SSL Encryption to ON.
3	Select the option for the Server Validation parameter.

NOTE: If the parameter SSL Encryption is set to ON but a server certificate is not available, connections are not SSL encrypted.

For Microsoft SQL Server connections, the Configuration tab of the SQL Gateway allows you to configure how to evaluate server certificates.

If the parameter SSL Encryption is set to ON, the parameter Server Validation provides the following options:

Server Validation option	Description
Validate Certificate + Verify Name	The SQL Gateway computer validates the server certificate and verifies the name. NOTE: If this option is used, the parameter Server Address must be set to the full name of the SQL server.
No Validation	The server certificate is not verified by the SQL Gateway computer.

WARNING
	UNAUTHENTICATED ACCESS Use the No Validation setting only for testing purposes. Do not use the No Validation setting during operation. Failure to follow these instructions can result in death, serious injury, or equipment damage.