First, to use integrated security, the connection string needs to be modified. Here is a sample connection string from my laptop setup to use integrated security:
jdbc:sqlserver://localhost;DatabaseName=dodeca;SelectMethod=cursor;integratedSecurity=true
This connection string worked perfectly when Tomcat was running in a command window but stopped working when we tried running Tomcat as a service. That seemed very confusing at first but, after some investigation, it makes sense. Integrated security uses the security context of the user running the process. In the case of Tomcat running as a service, Tomcat is running under the SYSTEM user and SYSTEM is not a SQL Server user. Here is the solution I came up with:
- Create a user account under which to run Tomcat. In my case, I created a ‘hyperion_system’ user on my laptop for this purpose. Give this user Administrative privileges on the server in order to run the service properly.
- Make sure that same username has a login on SQL Server.
- Map the user to have the following privileges on the Dodeca database:
- db_datareader
- db_datawriter
- db_ddladmin
- Open the Services dialog and select the Provider Services service. Right-mouse click and choose Properties. Choose the ‘Log On’ tab and configure the service to use this username (instead of the default ‘SYSTEM’ username.
- Save and restart the service.
Once this is complete, SQL Server integrated security works like a champ.
Note: Don't forget to place the sqljdbc_auth.dll that is delivered with the JDBC driver in the Windows System32 directory.
Posts
6 comments:
So if your app is having problems, or a user is causing problems in the database, how would you know which user is causing the problem? In SQL Server - you would only ever see the 1 user. This seems like a big security and management problem. Have you been able to get real integrated security to work - where is passess session security, not service account security?
If you are running a server-based service, integrated security doesn't seem like an option unless you actually started the service for each user. In this scenario, it seems easier, for most applications, to specify a username/password combination to use for the connection. Many applications, such as APS, are not architected to enable that scenario.
Tim
What about JESPA? This appears to do NTLM authentication. http://www.ioplex.com/ If properly configured, this could do your authentication and get around the security problems of having assigning 1 account do do everything. You could also then use SQL 2008's schema's with AD roles and the resource governor to get really fine grain control over your users. Good luck!
Thanks for the heads up re: JESPA.. For most operations on our server, we use Hibernate and thus the Hibernate team would need to implement a JESPA module or, as Hibernate is open source, we have the option of integrating it ourselves. There are legal ramifications to doing so.
For our SQLPassthrough functionality though, it may be an option.
This is a pretty old post but maybe someone can help. We have a webapp written a vendor that runs on Tomcat and the set it up to use integrated authentication like shown here. The problem is we are using an AD account to run the service as but the problem is with some devices when they reboot if the network connection is unavailable the service fails to start. Setting the service to restart on failure doesn't seem to work either. Anyone have any ideas?
I haven't observed this behavior. Maybe one of the blog readers has seen it?
Tim
Post a Comment