It is currently Wed Jul 30, 2014 5:09 am




Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
 JDBC SSL to SQL Database 
Author Message
Trooper
Trooper

Joined: Wed Jun 20, 2012 5:56 am
Posts: 12
Post 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:
Code:
jdbc:sqlserver://FQDN\MSSQLSERVER:1433

• The Extra Connection Properties looks as follows:
Code:
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


Attachments:
File comment: JDBC Error
JDBCStackTrace.pdf [37.78 KiB]
Downloaded 42 times
Mon Jul 30, 2012 10:34 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 2:46 pm
Posts: 1982
Location: Sacramento, CA
Post Re: JDBC SSL to SQL Database
I think you need some more parameters. Check out this article from Microsoft:

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

_________________
Travis Cox
Inductive Automation
Technical Support Rep.


Mon Jul 30, 2012 12:34 pm
Profile WWW
Trooper
Trooper

Joined: Wed Jun 20, 2012 5:56 am
Posts: 12
Post Re: JDBC SSL to SQL Database
Thanks for the reply. Reading through the MSDN article the only paramaters I think I need are:
Code:
"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).


Tue Jul 31, 2012 7:56 am
Profile
Moderator
Moderator

Joined: Mon Jun 04, 2012 8:53 am
Posts: 440
Post Re: JDBC SSL to SQL Database
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.

_________________
James Lorenz
QA/Build Systems Engineer
Inductive Automation


Tue Jul 31, 2012 4:01 pm
Profile
Trooper
Trooper

Joined: Wed Jun 20, 2012 5:56 am
Posts: 12
Post Re: JDBC SSL to SQL Database
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.


Thu Aug 02, 2012 10:16 am
Profile
Trooper
Trooper

Joined: Wed Jun 20, 2012 5:56 am
Posts: 12
Post Re: JDBC SSL to SQL Database  Topic is solved
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


Thu Aug 02, 2012 3:55 pm
Profile
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 


Who is online

Users browsing this forum: Google [Bot], IronYeti and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: