Sunday, October 7, 2012

Windows Authentication for service running on Windows Server 2008 connecting to SQL Server running on Windows Server 2003

When running Tomcat on Windows, it is useful to run it as a service using a service account that has permissions to your SQL database so that you do not have to keep your credentials in a config file that can be compromised.

I ran into a problem where my service was unable to authenticate against a SQL instance running on Windows Server 2003 from a system running Windows Server 2008. It didn't seem to matter if I was running jTDS or the Microsoft provided JDBC drivers.

On the client side, I would immediately get I/O errors saying the DB server closed the connection. On the server side, I would see the following errors in the Event Log produced by MSSQL (catagory: Logon):

"Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library."

When running a vanilla installation of Windows Server 2003, the server will not be able to support NTLMv2, where on a vanilla installation of Windows Server 2008, it will not drop down to NTLM.

The best fix I have found was by changing the security policy on the client to drop down in authentication. (There is a forum post here that also references the fix, but the post is specific on another application)

Go to Local Security Policy (or set it on your domain), and under "Security Options", you will find "Network security: LAN Manager authentication level" with a default value of "Not Defined". Change it to "Send LM & NTLM - use NTLMv2 session security if negotiated".

Click apply, and restart your service, and you will have database connectivity via Single Sign On Windows Authentication.