JDBC: Connecting with SSL Encryption

In some cases, you may want to secure the JDBC connection between DualShield and SQL server. Here are the instructions for Microsoft SQL server. You can find the related information on Internet if you are using other SQL server.

Enable SSL under SQL Server Configuration manager

Select an certificate. You can buy the certificate, or generate it with MS CA server or openssl. Make sure it has private key, and Server Authentication (1.3.6.1.5.5.7.3.1) in Enhanced Key Usage.

Restart SQL Server service. If the service fails to start, check Event Viewer. For instance, during our test, we got

Unable to load user-specified certificate [Cert Hash(sha1) "DEFFF1C567283758B87B29882226DD3631A1ACAB"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

Checked online, there are two useful links

MSSQLServer Event ID 26014 - Unable to load user-specified certificate

SQL Server service does not start after enabling SSL encryption

 They both point to same direction, the SQL Service account does not have permissions to open the private key for the certificate.

OK, let us check it by "Manage Private Keys..." at the certificate

Unfortunately, the server account DS08\sqlSvcAcc

was not in the permission list.

Add it with full control

Now SQL server back to work. It is time to change the JDBC connection string in server.xml. Add

encrypt=true;trustServerCertificate=true

If you also want JDBC to validate the SQL server SSL certificate, then

encrypt=true;trustServerCertificate=false

For instance,

<Resource driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" maxActive="1000" maxIdle="2" maxWait="5000" name="jdbc/DasDS" type="javax.sql.DataSource" url="jdbc:sqlserver://ds0802.ds08.local:1433;DatabaseName=dualshield2;SelectMethod=cursor;

integratedSecurity=true;encrypt=true;trustServerCertificate=true" validationQuery="Select 1"/>

Note, if you have trustServerCertificate=false, and your SQL SSL certificate is NOT issued by a public CA, then you should import that CA into JAVA cacerts, please see the details in our KB2 or KB31. Please also make sure you are using FQDN instead of IP address of SQL server in connection string.

If the server name in the connection string does not match the server name in the SQL Server SSL certificate, the following error will be issued: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "java.security.cert.CertificateException: Failed to validate the server name in a certificate during Secure Sockets Layer (SSL) initialization."

Want to verify if the JDBC connection is SSL? Use WireShark.

Without SSL, you should be able to see some SQL batch

But with SSL, you shouldn't see any of them.