Add NTLM authentication to SqlClient

[ebekker]

While #4198 added support for integrated authentication when a client runs on a domain-joined host, it can be quite useful to support NTLM-based Windows authentication from a non-domain-joined host, especially in today's serverless or container environments.

I know this is possible simply at the protocol level (TDS) because this has been supported in the jTDS JDBC driver for quite some time.

The goal is to be able to connect to a SQL Server instance using Windows Authentication from a non-domain-joined host (and also, not necessarily a Windows host), simply by providing the connection credentials in the connection string (domain, username, password). Is this possible today?

[FireInWinter]

Actually #4198 doesn't support NTLM, but Kerberos. Kerberos is not the same as NTLM and, while more secure than NTLM, is much harder to setup and get working. Using SQL Logins from Linux/Mac is really a pain, but using Kerberos isn't something that one group can get working on their own. It requires AD admins, database and possibly network to get it working. Whereas if you have a domain, user, and password, you could get NTLM authentication working.

In the mssql-jdbc project #696 they just commented that they will are working on NTLM authentication support. I think the same thing should be done on this project as well to keep supported options in line with what JDBC will have.

[ebekker]

Correct, but I believe both are a form of integrated authentication.

Glad to hear about the mssql-jdbc enhancement, maybe if there is any overlap in implementation teams, SqlClient can benefit as well.

[divega]

As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.

[FireInWinter]

Any chance of this being implemented anytime soon? I was really hopeful for a little while when it was added for SqlClient v1.1.0 as a ToDo, but then it was removed and seems to have been put on the back burner.

At this point .net core is a bad development choice for developers on Mac or Linux. I've been suggesting they use DBeaver (or the Java based DB tool of their choice) to do database design. Since the MS JDBC SQL driver has added NTLM auth, we can manage their permissions using AD and groups. If we suggest they use Azure Data Studio, then we have to create SQL logins for them and try to manage the passwords (sort of like with Windows NT 3.1 before Domains - per server), not a good options when you have over a thousand databases ranging from 1GB to 50TB.

[ebekker]

If you're looking for this support for Linux, I was able to roll my own solution to this issue with the help of native Kerberos tooling, you can find the details here: https://github.com/zyborg/Zyborg.AWS.Lambda.Kerberos

It can pby be adapted to MacOS as well using a similar approach.

[andreabedini]

Can I suggest we write in the FAQ that NTLM authentication on Linux is not supported? Otherwise I am driven to think I can start using .NET on Linux when I actually cannot (apologies, this might sound like a rant and probably it just is, I am frustrated after having spent a couple hours figuring out why I couldn't connect, this after spending the night figuring out how to make FSharp SqlProvider work (which it won't because fsharp type providers don't work on Linux)).

Let's try again

Could we clarify the necessary steps to make this happen? Perhaps someone will be able to offer a contribution and speed things up.

<3

[FireInWinter]

@andreabedini Of course you can always skip to Java and use NTLM authentication just fine, since the MS SQL JDBC driver DOES support NTLM authentication by giving it the user and password. /sarcasm

Some people try to claim that Kerberos support is the same as NTLM support, but it isn't. There is a LOT more work outside of what DBAs/Devs can do to get Kerberos to work. Also, based on the effort it took the JDBC driver team to implement NTLM, it isn't a huge project, just very important to a lot of us.

For a developer working on this, a good starting place would be to see how they implemented it on the Java side. Looks like they released NTLM auth there about a year ago, but I think they had a bug that didn't get fixed for me until 8.2.0 (earlier this year). Now we don't have to create SQL Logins for our developers on Macs(or the mess of trying to manage their passwords on multiple servers). Of course we don't support Azure Data Studio on Macs, since that uses .net core and doesn't support NTLM.

I really hope a developer is soon willing to take this project on. I would, but I'm mainly a DBA and have only recently started learning programming.

[brianlagunas]

I see nothing has been happening regarding this feature for over a year now. NTLM support is critical to our product. We have had to fork and modify to implement this feature ourselves. It would be great if this could be added directly to the product. Would you take a PR?

[ErikEJ]

Why don't you create a PR?

This project accepts community contributions.