How to Setup TLS connection in SQL Anywhere 11

In this article, we will learn how to setup a TLS connection in SQL Anywhere 11.
The connection will be like this:

A client device, e.g. laptop, connects to a server. Communications between the two will be encrypted and sent over a TLS protocol.

Requirements

• Both client and server use Windows Operating System
• The SQL Anywhere 11 is installed on both
• A telnet utility is installed on the client device

Boundary

• The client device should be able to connect to the server. We will test it using the telnet utility
• I use SQL Anywhere 11 here, but the steps described here should be applicable to version 9+
• The server should allow inbound connections to port 2638
• The scenario has been tested on Windows 10

Server Setup

We will now setup the TLS connection on the server. We will do these:

1. Create a TLS certificate
2. Run DB server

1. Create a TLS certificate

Before going further, make sure the SQL Anywhere 11 has been installed on the server already. We will use the createcert utility here.

Also, we first need to create a certs folder. The certificate files created will be stored in this folder. To create the folder, follow these steps:

1. Open up Command Prompt
2. Type: mkdir c:\Users\Public\Documents\certs, and hit enter
3. Type: dir c:\Users\Public\Documents\certs, and hit enter. There should be no error message, and the folder contents will be displayed.

To create the TLS certificate, simply login to your server, and follow these steps:

1. Open up Powershell
2. Type cd "C:\Program Files (x86)\SQL Anywhere 11\Bin32" and then press enter
3. Type createcert.exe and then press enter. Note that, you may adjust the values accordingly.
   1. Enter RSA key length (512-16384): 4096
   2. Country Code: ID
   3. State/Province: DKI Jakarta
   4. Locality: Indonesian
   5. Organization: My Organization
   6. Organizational Unit: Engineering
   7. Common name: my-organization.com
   8. Enter file path of signer's certificate: (leave this empty, and hit enter)
   9. Serial number [generate GUID]: (leave this empty, and hit enter)
   10. Certificate valid for how many years (1-100): 1
   11. Certificate Authority (Y/N) [N]: N
       1. Digital Signature
       2. Nonrepudiation
       3. Key Encipherment
       4. Data Encipherment
       5. Key Agreement
       6. Certificate Signing
       7. CRL Signing
       8. Encipher Only
       9. Decipher Only
   12. Key Usage [3,4,5]: (leave this empty, and hit enter)
   13. Enter file path to save certificate: C:\Users\Public\Documents\certs\my-organization.com.pem
   14. Enter file path to save private key: C:\Users\Public\Documents\certs\my-organization.com.key
   15. Enter password to protect private key: my-password
   16. Enter file path to save identity: C:\Users\Public\Documents\certs\my-organization.com.id.pem

After this, 3 certificate files will be created in C:\Users\Public\Documents\certs, and they are:

• my-organization.com.pem (we will use this on the client device later)
• my-organization.com.id.pem (we will use this on the server later)
• my-organization.com.key

2. Run DB server

Before we run the DB server, copy the SQL Anywhere database, e.g. DEMO2022.db to C:\Users\Public\Documents.
To run the DB server, follow these steps:

1. Open up Windows Explorer, then go to C:\Program Files (x86)\SQL Anywhere 11\Bin32
2. Find dbsrv11.exe, and double click it.
3. Once an application loaded, fill in the form as follow:
   1. Database: C:\Users\Public\Documents\Demo2022.db
   2. Server name: DEMO2022
   3. Options: -ec TLS(TLS_TYPE=RSA;IDENTITY=c:\Users\Public\Documents\certs\my-organization.com.id.pem;IDENTITY_PASSWORD=my-password) -x tcpip
   4. Hit the OK button The database server will now run at port 2638. If no error message is shown, the database server runs correctly.

Setup Client

Before going further, make sure the SQL Anywhere 11 has been installed on the server already.

Also, we will use telnet to test the client connection to the server. If the telnet has not been installed yet, open up Powershell (run it as Administrator), and then run this command:
Enable-WindowsOptionalFeature -Online -FeatureName TelnetClient

If you receive an error message when running the command, make sure you are connected to the internet, and then re-run the command.

Now let's assume each of the device's ip addresses are:

1. Client: 192.168.0.9
2. Server: 192.168.0.10

To test the client-to-server connection, Open up Command Prompt, and then run this command:
telnet 192.168.0.10 2638
Hit enter, and then you will see a blank output, it means the connection goes through and the client can connect to the server successfully. Otherwise, if you see an error message, fix it first until you can connect to the server successfully.

Now, we need to copy the certificate files we created on the server to the client device, specifically at C:\Users\Public\Documents\certs (create the certs folder first).

Now, we will setup the ODBC connection. Open up Start Menu, and then type "ODBC", and then click "ODBC Data Sources (64-bit)". Make sure you choose the 64-bit option. Once it is opened, follow these steps:

1. Hit the "System DSN" tab. We will create a system-wide ODBC configuration
2. Hit the "Add" button
3. Choose "SQL Anywhere 11". If the option does not appear, you need to install the SQL Anywhere 11 first
   1. At the ODBC tab, fill in the following:
      1. Data source name: Demo
   2. At the Login tab, if your database requires a login:
      1. Choose "Supply user ID and password"
      2. Fill in User ID and Password
      3. Tick the "Encrypt password" option
   3. At the Database tab, fill in the following:
      1. Server name: Demo2022 (we specify this name when running the DB server before)
      2. Untick "Stop database after last disconnect". This will make sure the DB server will always run on the server
   4. At the Network tab, fill in the following:
      1. Untick the "Shared memory" option. This option is enabled when we run the DB server locally
      2. Tick the "TCP/IP" option, and set the value to this: "HOST=192.168.0.10;PORT=2638"
      3. Click the "TLS" option, and then click the Edit button
         1. For the "Trusted certificates", click the Browse button and then point it to the my-organization.com.pem. If you cannot see the certificate files, choose the "All Files" option, and then you should be able to see them. Or, directly set the value to this: C:\Users\Public\Documents\certs\my-organization.com.pem
         2. TLS Type: RSA
   5. Lastly, go back to the ODBC tab, and hit the "Test Connection" button. And you should see a "Connection successful" message.

Now, you can use the created ODBC Data Source in your application. Happy coding!

References

• Database Server Options
• Starting the database server with transport-layer security
• Transport Layer Security
• Certificate Creation utility