JDBC SSL to SQL Database

Hello folks,

I am trying to utilize a “Hub and Spoke” style architecture where the SQL Bridge Module is at a remote site and logging data to a centralized database across the web. I have enabled SSL on the gateway and it works as desired for gateway and client access. However, when I set the “Force Encryption” setting on my SQL server (within SQL Server Configuration Manager) the connection fails.

I am not sure if the break is on the SQL server side or the JDBC driver side…
• I’ve been using a self-signed certificate created on the SQL server
• The Connect URL looks as follows:

jdbc:sqlserver://FQDN\MSSQLSERVER:1433

• The Extra Connection Properties looks as follows:

databaseName=DBName;trustServerCertificate=true

If I leave the database connection exactly the way it is but change the “Force Encryption” setting on the SQL server it connects without issue. I apologize for the poor formatting of the attached stack but I wasn’t able to get it out of the browser any cleaner.

Version Info:
IA 7.5.0
SQL Server 2008
Windows Server 2008
Default JDBC driver “sqljdbc4.jar”

I appreciate how active these forums are as they’ve certainly helped me already. If anyone has any experience with SSL+SQL the input is appreciated!

Jordan
JDBCStackTrace.pdf (37.8 KB)

I think you need some more parameters. Check out this article from Microsoft:

msdn.microsoft.com/en-us/library … 9(v=sql.90.aspx

Thanks for the reply. Reading through the MSDN article the only paramaters I think I need are:

"encrypt=true;trustServerCertificate=true"

I believe the encrypt parameter is an option in the event the server does not demand encryption; with that switch the client will request it. Either way I end up with the same errors as before.

From what I understand the trustserverCertificate is kind of a catch all where the client automatically accepts any certificate on the server (useful for testing / self signed probably not ideal for production).

Shot in the dark here, but try going into SQL Server Config Manager (on the main server) and go to SQL Server Network Configuration -> Protocols for SQLEXPRESS (or whatever the instance name is). Right click on TCP/IP and go to Properties, and under the IP Addresses tab change the IP address under IP2 to the external IP of the “spoke” and the port for IPALL to 1433.

  • NOTE: take note of the original settings, as this is not guaranteed to work.

No such luck. Thanks anyhow.

The SQL traces on the server show the client logging on successfully however the next entry in the trace is the client logging off. I’ve opened a ticket with Microsoft and will followup here if a resolution is found.

Resolved via Microsoft.

There are apparently issues with SSL connectivity and basic installs of SQL Server 2008. After installing “SQL Server 2008 Service Pack 3” and following it up with “Cumulative Update 6” (and rebooting) the SSL connection succeeded.

General notes for anyone wanting to do this in the future:

  • For testing purposes a self signed certificate works if you add “trustServerCertificate=true” to the client connection string
  • UPDATE your SQL server with MS patches
  • Aside from selecting the Certificate and setting the “Force Encryption” setting to yes, no further configuration is needed within SQL Server Configuration Manager