Click here to Skip to main content
14,209,923 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hello All,


I am fairly knew to Java and Tomcat etc as I came from a non Java\Tomcat previous role but have inherited a project which is a Java servlet (Java 1.6.0.29) running on Windows with Tomcat (Tomcat 7) as the container. The servlet communicates with both an Oracle database on a Unix server and a SQL server database on a Windows server. I now require to secure the communication with the SQL Server database using SSL (Two way communication) and would really like some straight forward guidance on how to do this, i.e. what exactly do I do?


I ask this because there is a lot of information on the Tomcat website and other web sites but I find it becomes very ambiguous and confusing. They mostly talk about setting up a Keystore for the root certificate on the server and then say nothing about the "client". In my servlets situation the server hosting the SQL server is the "server" and the server hosting the servlet is the "client". The server hosting the servlet ("the client") already has a keystore set up on it to handle the encryption to the Oracle database and a entry to suit in the Tomcat server.xml file.


Any assistance would be greatly appreciated. I am really stuck with this


Thank you in advance


AJFarroll
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 1

Please clarify one point:

From the text of your question, you are actually asking about communicating with a MS SQL Server, and the SQL Server requires communication over SSL. Is this correct?

If it is, then the fact that the communication is coming from a servlet is mostly immaterial. Once you throw "Tomcat","SSL", "Servlet" into a Google search, the results are going to be centered on securing the Tomcat server (thus the references to root certificates and the keystore).

It sounds like you are having a jdbc driver configuration issue. If you know the JDBC driver (name, source, and version), then you can either Google that plus "SSL" or post it here and I'll try to help.

David

Summarized after resolution:

For the TL;DR crowd: There were a couple of issues with the Microsoft sqljdbc.jar driver configuration. It seems that the original servlet was getting its configuration settings from a different context.xml than was obvious at first glance. The questioner was able to find the correct context.xml and make the required change to the pooled connection, and it worked. There seemed to be another issue with having multiple instances of the servlet running at the same time, but the OP was able to resolve that issue.
   
v2
Comments
jar8695 14-Feb-14 5:50am
   
Hi David Days,

Yes you are correct when you say "communicating with a MS SQL Server, and the SQL Server requires communication over SSL". Basically I need to encrypt with SSL the web service requests and responses between the SQL Server and the Java Servlet. The connection is set up using JNDI in the Tomcat context.xml file with the xml resource tag having the following attributes; driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" factory="org.moss.jdj.dbcp.EncryptedDataSourceFactory"

The connection is tested by setting up a database connection in the Eclipse IDE using "sqljdbc-1.2.0.jar" and this pings successfully.

When I inherited the project the Oracle connection was already set up with a keystore to encrypt its communication and an entry in the Tomcat server.xml file, so unsure if I can amend the server.xml file. Also, the SQL Server server is not at my location in the company and someone else manages that server and they have provided me with what I think is a public key SSL certificate but any attempts by myself to apply this certificate has not succeeded.

Thanks for any assistance you provide.

AJFarroll
jar8695 14-Feb-14 10:57am
   
I may have a little progress on this. In the Tomcat context.xml file I amended the relevant JNDI resource tag to have the "URL" parameter to include this integratedSecurity=true;encrypt=true;trustServerCertificate=true.

Now when I attempt a web request using SoapUI I get an empty tag returned in the SoapUI response, but the Tomcat outputs states; com.microsoft.sqlserver.jdbc.AuthenticationJNI <clinit>
WARNING: Failed to load the sqljdbc_auth.dll

and

org.hibernate.exception.GenericJDBCException: Cannot open connection

It is as if it now only needs to find and use the correct SSL certificate. Is this correct and if so what do I do now.

Regards

AJFarroll
David Days 14-Feb-14 11:32am
   
Actually, the "failed to load sqljdbc_auth.dll" sounds like more of an installation issue. The hibernate error is (99% sure) likely because of the previously mentioned error.

"failed to load" may not mean that the dll isn't on the machine, but that it's not where the Tomcat system can find it. You may have to go through the installation steps again, or check the permissions and account under which the Tomcat server is running.

From my reading, the "trustServerCertificate=true" should cover it--you don't necessarily need a keystore at that point ('course, you just trusting anyone who says they are the server, but one issue at a time).
jar8695 14-Feb-14 11:39am
   
Thanks David for your assistance so far. I did a search for the dll "sqljdbc_auth.dll" on my machine but it does not appear to be there. Do you think the first thing I should do now after adding in the "integratedSecurity=true;encrypt=true;trustServerCertificate=true" is to provide the sqljdbc_auth.dll and see what happens? Is sqljdbc_auth.dll part of Tomcat or Java?

It is Friday 4.45 PM here in Scotland so I might not be able to get this done until Monday morning now. We are told to be out of building by 5.45 PM!!!

Regards

AJFarroll
David Days 14-Feb-14 12:22pm
   
sqljdbc_auth.dll is (and I'm guessing, but very confident) part of the windows installation of the jdbc driver. Getting to the edge of my guesswork here, but I would recommend re-installing the driver, then confirming that the DLL is present.
jar8695 17-Feb-14 7:43am
   
Hi David. Monday morning again in Scotland. I downloaded the latest Microsoft JDBC driver, which was an exe file but when I attempted to install it, the file simply unpackaged. Although I found the sqljdbc_auth.dll within. I placed this in C:\Windows\system32 and there error is now different - "java.lang.UnsatisfiedLinkError: com.microsoft.sqlserver.jdbc.AuthenticationJNI.SNISecGenClientContext([B[I[BI[B[I[ZLjava/lang/String;Ljava/lang/String;Ljava/lang/String;Ljava/util/logging/Logger;)I at com.microsoft.sqlserver.jdbc.AuthenticationJNI.SNISecGenClientContext
(Native Method)"

It appears that I may have to Set java.library.path to a directory containing this DLL which Java uses to find native libraries!!

How do I do that? Does this sound correct?

Thanks again

AJFarroll
David Days 18-Feb-14 10:20am
   
Ok--I downloaded the driver and went through the installation process.

What the installation does is basically deliver you a structured package. The entire structure should be used at once in your application--meaning, you don't just pull out the DLL. If the Tomcat server is going to be the only user of this type of connection (JDBC to Sql Server), I would recommend placing the entire extracted package (subfolders and all) in the Tomcat's JAR library (whatever the currently configured CLASSPATH is.)

The help files included in the download show code, configuration, and examples of each, so you should be able to get closer to your goal.
jar8695 18-Feb-14 10:38am
   
Thanks again. When you say "in the Tomcat's JAR library (whatever the currently configured CLASSPATH is.", do you mean the CLASSPATH Windows environment variable or the CLASSPATH in the Tomcat catalina.bat file?

I appreciate your patience and time with me on this. Before this role I was in Unix and Oracle. I am enjoying the Java and Tomcat etc but can become quite complicated.
David Days 18-Feb-14 10:47am
   
Easiest way to go would be in the catalina.bat file. Tomcat is one of those systems that is configured almost identically in both the Windows and *nix versions, so it's often easier to stick with a Tomcat-oriented configuration (Tomcat Classpaths, configs, etc), than to try and do machine-level configurations, like installing DLLs in the Mystery Folders.
jar8695 18-Feb-14 10:50am
   
I created a setenv.bat file in the tomcat >> bin directory and in there I have created the following entries;

set JAVA_OPTS="-DJavax.net.ssl.trustStore=C:\path\to\keystore"
set JAVA_OPTS="-DJavax.net.ssl.trustStorePassword=*********"


Apparently this is how to set these parameters. The path and password are the same as the keystore set up and configured in the server.xml file. I set up the TrustStore parameters because the help files with the downloaded Microsoft JDBC tells us to in the section "SSL Certificate" in the Index.html. Does this sound correct?
David Days 18-Feb-14 10:53am
   
It sounds correct with regards to configuring the driver resources. I generally stuck with the context.xml parameters that I spoke about earlier. This way, though, your entire Tomcat server should have this configured connection available; it's up to you to decide if that's what you want or not.
jar8695 18-Feb-14 11:23am
   
Okay. I took a backup of ctatlina.bat file then amended it where it said set CLASSPATH= to be set "CLASSPATH=C:\tomcat\lib". I also copied entire folder named sqljdbs_4.0 to C:\tomcat\lib.

The out come was the same. I made a web request using SoapUI and in Tomcat console the errors at top were

WARNING: Failed to load the sqljdbc_auth.dll
and
org.hibernate.exception.GenericJDBCException: Cannot open connection

A small point of note is that, I also left the setenv.bat file with the two entries I mentioned previously in place and when I start Tomcat, at the start of the console output it now states

"INFO: Loaded APR based Apache Tomcat Native library 1.1.29 using APR version 1.4.8.
18-Feb-2014 16:13:47 org.apache.catalina.core.AprLifecycleListener init
INFO: APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true].".

I look forward to your feedback.
David Days 18-Feb-14 11:28am
   
Admittedly, I don't know what else to do. You can turn up the logging level to see where in the JDBC process it is dying--I would suggest that as a next step.

After that, it all depends on what you find: Are the various PATH variables correct? Is it a permission issue because Hibernate can't reach into the correct DDL? etc..

jar8695 18-Feb-14 11:37am
   
Hi David.

I also am out of ideas here. I will try and fine grain the logging and see if that provides any more information on the JDBC process.

When I downloaded the latest Microsoft JDBC and unpackaged it it created a folder named "Microsoft JDBC Driver 4.0 for SQL Server" and in this folder was a subfolder named "sqljdbc_4.0". It was this sqljdbc_4.0 folder that I placed in C:\tomcat\lib and made the CLASSPATH set to C:\tomcat\lib. Was this the correct thing to try?

Thanks again so much for all the assistance.

Regards

AJFarroll
David Days 18-Feb-14 11:47am
   
I would probably either pull out the subfolder "enu" (on the version I have), or add "C:\tomcat\lib\sqljdbc_4.0\enu" to the classpath, since the actual JAR files are lower down in the structure.
jar8695 18-Feb-14 12:03pm
   
I tried amending the classpath to be C:\tomcat\lib\sqljdbc_4.0\enu" but still the same outcome.

Thanks for your assistance. I appreciate your patience, expertise and time. As we say in scotland - "Lang may your lumb reek" ------ Live long and happily.
jar8695 18-Feb-14 11:46am
   
Hi David,

I Have just found this URL. It is Eclipse I am working with and did try and add the sqljdbc4.jar into the build path as an external jar but when I tried it it stopped connections to the Oracle database as well, so I reverted back. But URL has something extra. Do you think it would work?

http://thusithamabotuwana.wordpress.com/2012/07/19/connecting-to-sql-server-from-java/
David Days 18-Feb-14 11:52am
   
Personally, I always used the JTDS driver--much easier to use, and never had any issues with it. I have never used any of the SSL options, though, so I can't speak to that. Having said that, though, it sounds like it's more a matter of having your app connect to an https connection rather than providing authentication via one (client certificate stuff).

I would try some test projects with both in Eclipse, and concentrate on the driver setup rather than the results, since you may not want to rework your code on the main servlet.

jar8695 20-Feb-14 7:21am
   
Hi David,

I have actually got this working, although only for one instance on one machine which I think is strange. I had one last attempt at something and what I changed was - in my JNDI connection for the SQL server database in the context.xml I adjusted the "URL" element from "integratedSecurity=true;encrypt=true;trustServerCertificate=true" to "encrypt=true;trustServerCertificate=true". This got the encryption working completely on my TEST instance of the servlet. However, my Live version of the servlet the encryption is NOT working even though they are connecting to the same SQL server database and both are running on the same Tomcat etc.

Any ideas why this might be?

Thanks

AJFarroll
David Days 20-Feb-14 10:37am
   
First thing that comes to mind is that the servlet you are ultimately trying to change might be getting configuration from another source.

Try a file search on the Tomcat directories for the server url--you might find the servlet is getting its config from a different context.xml; it's a shot in the dark, but it sounds like you've solved the parameters issue--just making sure the servlet uses the correct ones.
jar8695 21-Feb-14 11:14am
   
Hi David. EUREKA!!! I think it is all sorted and have discovered the problem(s).

I have all encryption working now on both my desktop PC and the intended server it will run on. The problem seems to have been the following;

1. As you suggested, look for conflicting configuration files. This seemed very relevant and I found that in my Eclipse environment there were two context.xml files and these affected the outcomes as I found when I changed one back again.

2. The other is that, and please correct me if I am wrong here, but we found that two instances of the servlet, i.e. Test and Live running in the same Tomcat (and on the same machine then obviously) can run together fine - but NOT when SSL is implemented. When SSL is implemented only one can be running at once. I have created a work around for this though by creating a batch file on the desktop that swaps around the WAR and configuration files and restarts Tomcat

Perhaps you know of a way that both instances can run with SSL implemented.

Otherwise I am very relieved.

Thanks again for all your help

Regards

Alan
David Days 21-Feb-14 12:42pm
   
I'm glad to have helped you get in the right direction.

Running the same servlet multiple times on Tomcat is a normal capability--different contexts (URL paths) means they get treated as independent apps for the most part. I'm not sure why SSL becomes an issue, though; it's either a driver-dependent limitation (possible, but would seem to be a strange way to set it up) or it has to do with how SSL kind of strips away all pretension and forces both sides to talk with distinct machine names (as in, you can't do an alias for "appsrv.secure.domain" to "www.secure.domain" and have the SSL cert continue to be valid)...

In my limited experience with other drivers over SSL, it's not usually a problem--but it's always been one app trying to get to one database, not two instances of the same app.

I'll do some checking around to see if anything pops up--I've had other SSL issues that trip me up when everything else is working correctly, and this one sounds familiar.

Glad it got resolved; hopefully this whole conversation will help someone else that runs into the same thing.

Good luck, and don't hesitate if you have any other issues.

Sincerely,
David
Rate this:
Please Sign up or sign in to vote.

Solution 2

Got it.

There's a lot of information to work with, and not all of it will fit into a single post; however, I will try to get you started in the right direction. I may be a few versions of Tomcat behind, but it sounds like this project may be, also, so this information shouldn't be too out of date.

(I also apologize for not giving you any direct code or configuration--I don't have a setup to play with at the moment, so this will be mostly "hand waving" advice.)

You have three configuration requirements here, and it sounds like two of them are (fairly) complete. Server credentials and access, driver "correctness", and Tomcat config.

You said you successfully pinged the SQL Server using Eclipse, so driver, credentials, and network access are confirmed. That just leaves the Tomcat config.

Tomcat (and the servlets) are set up (mostly) by xml config files, which you already seem to be familiar with. There is the server.xml, which controls the high-level stuff, and then there are what are named "Context" configurations--basically, subdivisions of the server for different sites, applications, subdirectories, etc. These context files provide things like access controls, renaming, and, in your case, common resources like database connections. The connectivity info doesn't have to be in the context files, but it's best practice to do so because you can swap around database connections in one go instead of hitting umpteen different web apps or hundreds of servlets.

If you have access to the server, you can change these files, since they are just text files. If you take the time to learn the Tomcat admin interface (and it's installed and accessible) you can also make the changes there.

I keep saying "context.xml files" (plural) because there are context files for individual applications, and there is one big mama for the entire server. If you want this connection available to all applications, you put the configuration entry in the big Context.xml file for the server. Here's a good writeup on how, and where, context.xml files are located, and what scope they have on the system.

From the sound of it, you are either trying to add a new SQL server connection, or replace the Oracle connection. Either way, you will be changing the entry (or adding an entry) in one of the context.xml files.

Since you have Eclipse, I would recommend the following to get everything set up.


  • Read the configuration links I've provided. Those will take you to a few other concepts that you may need to understand, but keep everything focused on "configure sqljdbc in Tomcat"
  • Create a simple servlet app (say, a page that does a very specific record count from your database server.)
  • Keep testing the servlet until you can get it running in the IDE
  • Look at the context.xml and resource config that works. Then, when you've read through the Tomcat JNDI config instructions enough, either add or replace the resource config in the appropriate context.xml
  • Install your test app to see if it works.


I apologize for this sounding like an "RTFM" response. There really is a lot to consider, but you should be able to do it by following the appropriate cook book--it's just digging out the correct recipe.

Let me know how it goes, and I'll try to help with any specific problems you run into. Meanwhile, I'll probably start my own setup to see how it runs (and knock the rust off the skillset).

Stack Overflow with a specific configuration for the driver:

Configure SQL Server connection pool on Tomcat

MS pages on their JDBC configuration:

All JDBC parameters
SSL Parameters
Client trust store setup
One example of troubleshooting
   
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190617.3 | Last Updated 21 Feb 2014
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100