In some cases, you may want to secure the connection between a DualShield server and the SQL server it uses. This article describes provides the instructions for Microsoft SQL server. You can find related information on Internet for other types of SQL servers.
Enable SSL in the MS-SQL Server Configuration Manager
Select a certificate. You can buy a commerical certificate, or generate one with MS CA server or openssl. Make sure it has private key, and Server Authentication (220.127.116.11.18.104.22.168.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 articles:
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 full control to the account
Now SQL server is back to work. It is time to change the JDBC connection string in in the DualShield's 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 trusted CA, then you should import that CA cert into the JAVA keystore in DualShield. Please refer to the article "How to Import a CA Certificate into JAVA Keystore". Please also make sure that 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."
Finally, if you want to verify if the JDBC connection is secured with SSL, use WireShark.
Without SSL, you should be able to see some SQL batch
But with SSL, you shouldn't see any of them.