Click here to Skip to main content
15,903,362 members
Please Sign up or sign in to vote.
3.67/5 (2 votes)
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

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
 
Share this answer
 
v3
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.
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900