Microsoft has recently released a new JDBC driver (version 6.0.7507.100) with some very interesting features. I am mostly interested in the TVP feature. This is why I would like to replace the current jTDS driver with the Microsoft driver.
The problem arises when trying to log in to our production server. Lets say we use the domain user "mydomain\dbuser" to login to the sql server instance. This is the jtds jdbc url we have used so far:
jdbc:jtds:sqlserver:/sqlServer:1433/myDb;domain=mydomain;user=dbuser;password=secretPwd
It works like a charm.
But when I try to use the Microsoft driver, my application cannot establish a connection to the database. I tried the following URL strings:
jdbc:sqlserver://sqlServer:1433;database=myDb;username=dbuser;password=secretPwd
jdbc:sqlserver://sqlServer:1433;database=myDb;username=mydomain\dbuser;password=secretPwd
jdbc:sqlserver://sqlServer:1433;database=myDb;username=dbuser@mydomain;password=secretPwd
and many other permutations but with no success. The app fails with the following exception:
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'mydomain\dbuser'
in the SQL Server log we get the following error:
Login failed for user 'mydomain\dbuser'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: 10.10.10.10]
Note that the application is supposed to run under Linux.
Does anybody know how the Microsoft JDBC URL should look like when connecting as a domain user?
My research in this issue has led me to the following conclusions:
- The simple transition from the jTDS driver to the Microsoft driver is not possible as I intended in the question. This is because the the jTDS driver implements NTLM authentication which is not available on the Microsoft driver.
- An alternative is to use the JavaKerberos authentication scheme (as Gord Thomson suggested) which is available on Linux machines. Fortunately, we have the Kerberos infrastructure set up in our production environment.
I have successfuly managed to add Kerberos authentication in my service mainly with the help of these Microsoft (blog) posts:
- JDBC - This driver is- not-configured for integrated
authentication
- Using Kerberos Integrated Authentication to
Connect to SQL Server
so I'll be using that.
It seems that Microsoft is currently developing a JDBC driver that supports NTLM.
You can follow a discussion that includes a preview here:
https://github.com/Microsoft/mssql-jdbc/issues/696
I tested the driver and it works, using the following connection string:
jdbc:sqlserver://sqlServer:1433;databaseName=mydb;useCursorsAlways=true;IntegratedSecurity=true;authenticationScheme=NTLM;domain=domain;user=dbuser;password=xxxxx
I contacted Microsoft and now wait for a GA release date.
Not directly the answer to your question, but if you want to fetch the latest driver (which might resolve your issue):
Microsoft latest release is https://www.microsoft.com/en-us/download/details.aspx?id=11774
which also has the sqljdbc_xa and sqljdbc_auth dlls for integrated security
From maven you would get https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/
Directly from source https://github.com/Microsoft/mssql-jdbc/releases
see notice: https://github.com/Microsoft/mssql-jdbc#download-the-dlls
