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.
