Authenticating to a SQL Server instance as a Windows User via JDBC

Kilian Foth picture Kilian Foth · Oct 9, 2014 · Viewed 24k times · Source

I'm having to support multiple database types for my tenant-enabled web application. Among others, I have successfully supported Microsoft's SQL Server, by using the net.sourceforge.jtds.jdbc.Driver class with a connection String like "jdbc:jtds:sqlserver://192.168.1.189:1433/ApplicationName". This works, but it requires that the user explicitly defines a user in the SQL Server instance and enables SQL Server authentication.

Now, inevitably, requirements changed, and we're supposed to support connecting to SQL Server via Windows Authentication. Evidently this requires some sort of change to the connection string, since the data base server must somehow be able to distinguish whether the credentials passed into the data base connection are for a user defined in the SQL Server installation or in the Windows OS. But what is it?

Acting on advice from the internet, if progressed as far as extending the connection string with ;useNTLMv2=true;domain=WORKGROUP. That seems to make the data base server aware that I want to authenticate as a Windows user, but the actual log-in fails with

The login is from an untrusted domain and cannot be used with Windows authentication. (code 18452, state 28000)

Now im my testing set-up, both the J2EE app and the SQL server instance are in fact on the same machine (although in production they may not be), and still this computer isn't trusted enough to log on to itself? Evidently I'm missing a big part of the puzzle here. What does one have to do to convince an SQL Server instance that the user who started it can in fact log on to it via JDBC?

Edit

Since we have already sunk too much unsuccessful effort trying to integrate our web application with a full Microsoft infrastructure stack (SQL Server, Active Directory, Domain Name Service...), I have to restrict this question:

Does anyone know a way to access an SQL Server installation with a user account defined as a "Windows User" via JDBC form a J2EE application, without having to use Active Directory, a Windows machine running the web application and a proprietary DLL? The bounty is for any solution of that sub-problem. The entire problem is clearly too broad to be answered in one forum post.

Answer

beaudet picture beaudet · Sep 18, 2018

I ran into the error

The login is from an untrusted domain and cannot be used with Windows authentication

when a 2012 SQL Server DB instance was recently upgraded to 2016. In order to use AD based authentication with the JTDS driver and SQL Server 2016, it seems necessary to specify both the useNTLMv2=true and the domain=example.com suffix in order to establish a connection. The name of the domain is absolutely necessary and I confirmed that through testing. This is with JTDS driver version 1.3.1.

Example of a working connection string using AD based authentication to SQL Server 2016 DB with JTDS 1.3.1:

jdbc:jtds:sqlserver://sqlserver2016db.example.com/MY_DB_NAME;domain=example.com;prepareSQL=2;useNTLMv2=true