Cannot perform non-integrated NTLM authentication

[NathanStrong-Tripwire]

Driver version or jar name

6.4.0.jre8

SQL Server version

SQL Server 2017

Client operating system

Windows 10

Java/JVM version

Oracle Java 8 1.8.0_144

Table schema

N/A

Problem description

Our application currently utilizes jTDS to communicate with SQL Server databases. We
would like to switch to the Microsoft JDBC driver, for a few reasons:

• jTDS does not support using an application-provided trust store for TLS connections
• jTDS does not support advanced SQL Server features
• jTDS is not in active development
• jTDS does not support integrated authentication

The problem we're having is that the Microsoft driver doesn't seem to offer one of the
authentication modes that our application relies on heavily: the ability to log into
the database as an arbitrary domain user.

Please see the JavaDoc comments in the reproduction code for details.

And of course, there's always the case that I'm doing something wrong.

Expected behavior and actual behavior

I've written a really simple test case. See "Repro code" below.

Expected behavior: "PASS"
Actual behavior: "FAIL" with an error that user '' failed to log in.

Repro code

Please see the Java class for an A/B example of what we currently do with the jTDS
driver that I've been unsuccessful in getting to work with the Microsoft driver.

https://github.com/NathanStrong-Tripwire/mssql-jdbc-usecase

[ulvii]

Hi @NathanStrong-Tripwire ,

Currently Microsoft JDBC driver does not support authentication as an arbitrary domain user. We will investigate what needs to be done to implement it.

[vaibhavjain3]

Not really sure why Microsoft team is taking it lightly. It is such an important feature and blocked me from upgrading to latest Sql Server driver.

[David-Engel]

@vaibhavjain3 It's not taken lightly. The main reason it has not been implemented up to now is that NTLM is known to be an insecure protocol. Old drivers like jTDS implemented it a long time ago when NTLM wasn't considered so insecure. We are looking into implementing it because users are asking for it. But we will probably recommend that it only be used when you are communicating over a known secure network. We are also investigating options to support Windows username and password authentication using Kerberos which I believe may be a viable alternative to NTLM in this use case.

[vaibhavjain3]

@David-Engel Sorry If I was bit harsh, But trust me, this is very important feature and hundreds of developers are stuck on it. Including my self.

[sudiotan]

@David-Engel I agree that Kerberos is more secure and understand the decision on this issue. However, as a user, it comes with usability trade-off. To make Kerberos working is not an easy task. There are a lot of possibilities for Kerberos to fail. As an example, to set the SPN alone, there are many variation to consider:

• Domain topology (single domain, multiple/cross domain)
• SQL Server topology (standalone, named instance, cluster)
• Service account or local account

To solve the problem, there are many parties involved such as Domain, Database and Security administrators which is very common for large enterprises. To make it worse, it happened in the customer environment where you don't have any visibility/control.

After exhausting debugging effort, many of these users ended up switching to SQL authentication which in my opinion is not better than NTLM. The remaining that can't switch because of company policy, keep enduring this pain.

Don't take wrong, this is not the problem of MSJDBC but rather Kerberos usability. However, NTLM is helpful, we do need alternative fallback domain-user authentication (non-integrated) whenever Kerberos is failed.

[stolsvik]

What is the "ActiveDirectoryPassword" that evidently works for Azure ADs? Why shouldn't that also just work for normal, on-prem Active Directories?

[peterbae]

Hi @stolsvik, that's a good question. ActiveDirectoryPassword is Azure AD specific, but we didn't choose to name it AzureActiveDirectoryPassword due to the name length being too long. Hope this clarifies your question.

[stolsvik]

@peterbae Well, that's not really a large name for a java class. Also, if it really is Azure-specific, then you should definitely call it that.

Why can you not make a similar solution for on-prem AD? (And call that ActiveDirectoryPassword!) I am not the only one, apparently and obviously, that find that I am stuck on jtds because of this limitation. Going the nightmare-way that Kerberos represents, is pretty much out of the question for the foreseeable future. We'd rather go back to SQL auth than Kerberos - but that is also a crap option since we have many services, with many credentials, and our admins much prefer to use AD for managing them.

This cannot be that hard!

[stolsvik]

To alleviate the problem somewhat: You could maybe make a page that describes step-by-step how to get JavaKerberos to work from scratch on a clean Linux system? From a newly installed e.g. Ubuntu system, to a working SELECT in a simplest-as-possible Java main-class?

[cheenamalhotra]

Hi @stolsvik

We will surely work on adding clear documentation as requested.

About On-Prem AD authentication, I don't think we can extend ActiveDirectoryPassword authentication model on Azure to On-Prem AD, since for Azure AD, we make use of OAuth Authentication by acquiring accessToken from user credentials provided. OAuth is apparently not supported on On-Prem AD, and the only possible ways to authenticate user accounts are Kerberos and NTLM. Kerberos is already supported, and we are in process of working on NTLM (Domain based authentication), similar to how it works on jTDS driver.

Also, about authentication mode terminology, the terms 'ActiveDirectoryPassword' and 'ActiveDirectoryIntegrated' are in sync with all SQL Server drivers (ODBC, .NET, OLEDB, PHP, etc) to avoid any confusions.

I hope that answers your questions.
Let me know if I misinterpreted something.

[FireInWinter]

I agree that NTLM really needs to be added. Kerberos might be more secure, but is only viable if other groups in a company have made it a priority and got everything working for it. In my case we have 2 forests with a forest transitive trust. Kerberos should work, but only actually works when connecting between resources in the same forest. Across forests it doesn't work. As the database people are the only ones actively trying to use Kerberos, we aren't getting any priority to dig into AD and figure out what is wrong with our Kerberos config.

NTLM on Windows works fine however, so it means users on Linux or Mac have to use SQL Logins. SQL Logins are a much worse solution than NTLM ( and very insecure since you now have many different accounts to manage). So if a developer on a Mac has access to 10 SQL Server instances, he needs to have 10 distinct logins (not related to each other) and has to mange the passwords of all of those. How is that secure? He'll make sure the passwords are as simple as possible and probably write them down or save them to a text file. Using SQL Server logins is like going back to before 1993 Windows NT 3.1 (which is when domains were added) when every server had to manage the users locally and you had a lot of logins.

[David-Engel]

To update the issue, the team is moving forward with an implementation that does include NTLM support.

[ebekker]

Not sure if there is any overlap in the implementation teams, but perhaps this feature could also be implemented in the CoreFX SqlClient with the benefit of some knowledge sharing?

[Smorgasbordq]

Out of curiosity and personal need, I forked and committed a branch with non-integrated NTLM support that mostly references ol' JTDS code. I'd much rather use an official implementation (JTDS code is ancient and makes many assumptions), but figured I'd mention it if anyone wants to take a peak.

[FireInWinter]

To update the issue, the team is moving forward with an implementation that does include NTLM support.

@David-Engel any idea on a timeline for this? We have some upcoming projects that could use it and it would be nice to know if it might be a possibility.

[David-Engel]

@FireInWinter It's being worked on right now. It's not going to be complete for the production release this month, but assuming it does not hit any blockers, it should make the next one in July (and maybe a preview release in between).

[ebekker]

@Smorgasbordq -- is the code easily accessible on GitHub?

[kafran]

Finally SQL Server will be easily accessed from non Windows environment through NTLM \o/
Thank you guys.

[stolsvik]

@cheenamalhotra @David-Engel Any update on how this feature is progressing? Looked through releases just now, and it's not out in any preview release yet, at least.. Is it being developed in the github tree? - if so, any link to where would be nice!

[David-Engel]

@stolsvik It's coming along. Right now the most difficult part is getting past the security review. Due to the nature of NTLM, the security team is generally against this feature. We are working through roadblocks and making sure we implement as securely as we can, given the nature of NTLM, and document the dangers for users so they know how to limit their exposure. There is a personal branch somewhere on GitHub with the work in progress but I'd rather not post a link to it right now.

[FireInWinter]

@David-Engel Any update on this? It's been 2 months from the last update and I'm hoping that it might show up in a preview release soon.

[cheenamalhotra]

Hi @FireInWinter

PR #998 implements NTLM Authentication support in driver and it's currently in review. If you would like to try it out, please go ahead and we'd like your feedback on the same! 🙂

[joenmoreno]

@TarasTielkes, my IntelliJ is running on JDK 1.8 and I am using mssql-jdbc-7.3.1-SNAPSHOT.jre8-preview.jar as well for my driver. I am curious if anyone has tried this on a Mac. Some comments already mention that it's working on Linux so I'm pretty sure that it should work on Linux, at least in those situations.

[TarasTielkes]

@joenmoreno, can you try a simple public static void main from the Mac? This would eliminate any (potential) interference that IDEA itself might add to the mix. Here's what I used:

Connection connection = DriverManager.getConnection("jdbc:sqlserver://secret.foo.local;integratedSecurity=true;authenticationScheme=NTLM;domain=XX;user=me;password=Tiger;databaseName=mydb");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select id, name from something");
while (resultSet.next()) {
    String name = resultSet.getString("name");
    System.out.println(name);
}

Make sure to configure the provided SNAPSHOT jar from @ulvii as a dependency, either through your favorite build system or directly in IDEA.

And maybe throw the following line in, for good measure:

System.out.println(connection.getMetaData().getDriverVersion());

[joenmoreno]

@TarasTielkes, I just tried your suggestion. I am getting the same exact error message ("...The login is from an untrusted domain...").

Just to test if it's truly using the driver, I also tried running the code without the Jar as a dependency which gave me a "No suitable driver found..." error (which is expected).

[TarasTielkes]

@joenmoreno, that is good to know - a smaller testcase is always better.
Does the old jTDS driver work in an equivalent minimal testcase?
If so, can you list the sample code you're using for completeness?

[ulvii]

Thank you all for testing the feature. 👍

@joenmoreno, We have tested the implementation on Mac machines and everything seems to be fine. Please check SQL Server logs if you have access, the logs are usually more descriptive than the exception thrown.

Please also enable JDBC tracing in FINEST level, instructions can be found here.

[joenmoreno]

@TarasTielkes, yes, old jTds driver works. Below is the code I used:

import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Statement

public class MainApp {

public static void main (String[] args) {
    Connection connection = DriverManager.getConnection('jdbc:jtds:sqlserver://host_name:1433/db_name;domain=domain_name;useNTLMv2=true;user=user_name;password=user_password');
    System.out.println(connection.getMetaData().getDriverVersion());
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery('select count(*) as foo_count from foo');
    while (resultSet.next()) {
        String name = resultSet.getString('foo_count');
        System.out.println(name);
    }
}

}

[NathanStrong-Tripwire]

I am using Mac OSX 10.14.5 with IntelliJ Ultimate 2019.1 and using a JDK 11 project.

I was able to connect successfully.

Here is the connection string I used:

# given a user MYDOMAIN\myuser
jdbc:sqlserver://sqlhost.domain.local;integratedSecurity=true;authenticationScheme=NTLM;domain=MYDOMAIN;user=myuser;password=myser_password_in_plaintext;databaseName=db_name

I used the main method posted above, slightly altered to 1) handle SQLException and 2) use a query and result compatible with my db schema.

Scenarios tested:

[x] domain lowercase and uppercase
[x] using both the netbios name and the full domain name (i.e. given a domain mydomain.local with netbios name MYDOMAIN, both domain=MYDOMAIN and domain=mydomain.local work)

[joenmoreno]

@ulvii, I took your advice and cranked up the logging to ALL. The log I am seeing is mostly SSL Handshake prior to failing. Is there some special configuration on the SQL Server side for this? I tried connecting to SQL Server 2016 and SQL Server 2012 and both gave me similar error messages.

[ulvii]

Hi @joenmoreno,

Is there some special configuration on the SQL Server side for this?

Which OS is your server running on? Have you tried to connect to a server that is on a different machine/OS?

Please post the SQL Server logs, JDBC trace and stack trace.

Would you also try connecting with SQL username/password using our driver? I wonder if this is a generic authentication issue or only related to NTLM.

Another suggestion would be to specify SPN explicitly using serverSpn connection property, from what I know driver's SPN detection logic might fail depending on the OS.

[ulvii]

Hi @joenmoreno,
Any luck with connecting? Please let us know if you still have questions.

[lilgreenbird]

Addressed in PR #998

[thejas10]

Hi @joenmoreno,
I am also facing the same problem as yours "The login is from an untrusted domain and cannot be used with Windows authentication" when I tried preview jar whereas JTDS is working fine. Any help would be appreciated.

[ulvii]

Hi @thejas10 ,

Please take a look at the comments #696 (comment) and #696 (comment) and provide more details, we will look into it.

[thejas10]

Hi @ulvii,

As you requested here are the details -

Microsoft SQL Server 2016 - 13.0.5149.0 (X64
Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3

The server is running on windows. And I don't have access to server-side logs.

[ulvii]

Please try the following steps to eliminate variables:

• Try connecting with SQL username/password using our driver. The issue is not NTLM related if you are still not able to connect.
• Specify server SPN explicitly using serverSpn connection property.
• Follow suggestions from @TarasTielkes above for Firewall changes.
• Check Computer Configuration\Windows Settings\Security Settings\Local Policies\Security Options Network Security: LAN Manager authentication level to make sure the server accepts NTLMv2 requests.
• Try NTLM against a different server and make sure you have access to the server logs. Server logs are more descriptive, the error message sent to the client is generic for all NTLM authentication failures. Post the server logs here.
• Try NTLM on a different client machine.
• Post FINEST JDBC trace and JAVA stack trace.
• More details such as: Is the client/server domain-joined? Are they on same/different domains? What is the client OS?
• Provide mssql-jdbc and jTDS connection string.

Please let us know how it goes.

[thejas10]

@ulvii

• client os - Linux
• JTDS connection string - jdbc:jtds:sqlserver://host:1433/db_name;domain=domain_name;useNTLMv2=true;user=user_name;password=user_password
• yes I am able to connect via SQL username and password, will try out other steps as you mentioned and keep you posted.

[elijahgagne]

I've been using sqlline to test this driver. I've found that this works

sqlline \
-u "jdbc:sqlserver://MY_SERVER;integratedSecurity=true;authenticationScheme=NTLM;domain=MY_DOMAIN" \
-n MY_USERNAME \
-p MY_PASSWORD \
-d com.microsoft.sqlserver.jdbc.SQLServerDriver \
-e 'select name from sys.databases'

But I get an error trying

sqlline \
-u "jdbc:sqlserver://MY_SERVER;integratedSecurity=true;authenticationScheme=NTLM;domain=MY_DOMAIN;user=MY_USERNAME;password=MY_PASSWORD" \
-d com.microsoft.sqlserver.jdbc.SQLServerDriver \
-e 'select name from sys.databases'

Jul 16, 2019 8:41:47 PM com.microsoft.sqlserver.jdbc.SQLServerConnection connectInternal
SEVERE: ConnectionID:1 "User" (or "UserName") and "Password" connection properties must be specified for NTLM authentication.
Error: "User" (or "UserName") and "Password" connection properties must be specified for NTLM authentication. (state=,code=0)
com.microsoft.sqlserver.jdbc.SQLServerException: "User" (or "UserName") and "Password" connection properties must be specified for NTLM authentication.
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1620)
...
No current connection
sqlline version 1.8.0

Is that expected?

[ulvii]

Hi @elijahgagne,

I am not sure how sqlline makes use of the connection string, but we have tested the driver with jdbc:sqlserver://<server>;integratedSecurity=true;authenticationScheme=NTLM;domain=<domain>;user=<domainUser>;password=<domainUserPassword>; and everything seems to be fine. I suspect sqlline fails to pass either user or password property to the driver when integratedSecurity=true.

[snuyanzin]

Hi @ulvii
I am from sqlline community.
The only thing sqlline does with connection string is breaking it into properties, so for sqlline there is no difference between integratedSecurity, password and others.
Could you please confirm/reject that it is the behavior that your driver expects?

And there is one more strange thing for me:
@elijahgagne mentioned connection string -u "jdbc:sqlserver://MY_SERVER;integratedSecurity=true;authenticationScheme=NTLM;domain=MY_DOMAIN;user=MY_USERNAME;password=MY_PASSWORD"
however you said about jdbc:sqlserver://<server>;integratedSecurity=true;authenticationScheme=NTLM;domain=<domain>;user=<domainUser>;password=<domainUserPassword>;
Is it intentional that your connection string has a semicolon at the end which differs from the original one?

[ulvii]

Hi @snuyanzin ,

If both userName/user and password properties are provided to the driver by the application, the connection is expected to succeed, the exception above happens only when at least one of these properties is missing. Semicolon at the end does not make a difference for the driver. I would suggest to try the following standalone JDBC code without sqlline to narrow down the issue.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerDriver;


public class NTLMConnect {
    public static void main(String[] args) throws SQLException {
        new SQLServerDriver();
        String connectionUrl = "jdbc:sqlserver://<server>;integratedSecurity=true;authenticationScheme=NTLM;domain=<domain>;user=<domainUser>;password=<domainUserPassword>;";
        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement()) {
            String SQL = "select auth_scheme from sys.dm_exec_connections where session_id=@@spid";
            ResultSet rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        }
    }
}

[elijahgagne]

@ulvii Thanks for providing that code sample. I used it to test things and using my connect string, I got the output:

NTLM

So I think that confirms I have a good connect string and that the driver does support it.

[elijahgagne]

@joenmoreno and @thejas10 I've found that I get the following error if my account is locked out.

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