How to configure SQL Server With Encryption
Applies to Write-Back Server
Summary
It is possible to enable encryption with the SQL Server connection allowing to have a secure connection to the database.
Setting Up
Option 1: Add encryption settings to JDBC Advanced Options
By default, Write-Back may not trust your server certificate right away, so to enable encryption and trust the certificate, add the following options:
JDBC Advanced Options
encrypt=true;trustServerCertificate=true
Option 2: Add your SQL Server certificate to the Java truststore [connection string]
If you prefer to set “trustServerCertificate=false” then you must also specify the truststore where the certificate is imported:
JDBC Advanced Options
encrypt=true;trustServerCertificate=false;trustStore=C:\path\to\keysotre.jks;trustStorePassword=storePasswordWe recommend that you import the certificate to the truststore of the Java installed in the same folder as Write-Back.
Note: The default truststore password is “changeit” (no quotes) and it is located in <installation_folder>\Write-Back\openJDK-jre\lib\security\cacerts.jks, so that would be the path that you would place in the JDBC options.
To do so:
Step 1: Add the certificate to the truststore
The following command imports the certificate to the truststore:
keytool -import -v -trustcacerts -alias sqlserver -file yourSQLServerCertificate.cer -keystore <installation_folder>\Write-Back\openJDK-jre\lib\security\cacerts.jksNotes:
cacerts is the Java truststore that is bundled with Write-Back, located in <installation_folder>\Write-Back\openJDK-jre\lib\security
The keytool utility can be found in <installation_folder>\Write-Back\openJDK-jre\bin\
Make sure to replace the bold and underline sections of the command with your appropriate settings.
When keytool prompts for password, it is “changeit” (no quotes), as that is the default password of cacerts.
Step 2: Restart the Write-Back Manager service
Manually restart the Write-Back Manager server, on Windows by going to the services or the task manager and restarting the WriteBackManagerServer service and on Linux by using systemctl to restart the writeback-manager-server service.
Step 3: Test the connection on the Write-Back Manager
Verify if you can now successfully connect to your SQL Server.
Option 3: Add your SQL Server certificate to the Java truststore [Write-Back server configuration]
This option is similar to option 2, but instead of using the truststore in the connection string, it is imported with the Write-Back server configuration. The JDBC options are also simplified:
JDBC Advanced Options
Step 1: Add the certificate to the truststore
This step is exactly the same as step 1 of option 2, so please see the instructions there.
Step 2: Configure the Write-Back server to read the truststore
Navigate to: <installation_folder>\apache-tomcat\bin
Double click on WriteBackServerw.exe
Go to the Java tab
Under Java options, scroll to the bottom and add these two lines (changing the installation path to your own):
(again, by default, the cacerts password is changeit)
- Djavax.net.ssl.trustStore=<installation_folder>\openJDK-jre\lib\security\cacerts
- Djavax.net.ssl.trustStorePassword=changeit
It should look similar to this:
Click Apply and OK.
Repeat the same process for the Write-Back Manager server, located at <installation_folder>\manager\apache-tomcat\bin\WriteBackManagerServerw.exe
Step 3: Restart the Write-Back Manager service and test the connection
Repeat steps 2 and 3 of option 2.
These steps were adapted from Microsoft's documentation on securing SQL Server connections: