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 (22.214.171.124.126.96.36.199.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
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
If you also want JDBC to validate the SQL server SSL certificate, then
<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.