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.

[brianlagunas]

I'm not creating a PR until a conversation is had with the maintainers. They may not want this feature added at all ,and may not accept a PR.

[JRahnama]

@brianlagunas as @ErikEJ mentioned, as an open source project we always welcome contribution from the community. If the feature is high in demand and many are requesting it we would love to have a PR prepared so we can go over it and start reviewing it as soon as possible. I call our PM @David-Engel and our team lead @cheenamalhotra to get their opinion on this matter as well.

[David-Engel]

NTLM is not a secure protocol. Research it to understand the security resistance to supporting it.

It was only added to the JDBC driver in order to help the large, existing base of applications that relied on it to move off of older, less secure drivers like jTDS. It was added with the caveat that documentation would clearly state that NTLM auth is not secure and not recommended for use. Since SqlClient comes from a lineage that has never had NTLM username/password auth (different from integrated NTLM auth), it's a lot harder to justify the security risks around adding it.

Even if someone else contributed the PR, there would be strong resistance from the security team to accepting the feature.

We do understand that Kerberos is not as easy to configure and use from a user perspective. I wish there was an easy way to solve that problem.

Regards,
David

[FireInWinter]

@David-Engel NTLM is not secure as compared to Kerberos. That is very true. However, NTLM is more secure than SQL Authentication. Kerberos is not something that a SQL Admin can get working on their own, and in large environments, they might not be able to push to get issues worked out with cross domain/forest authentication. A SQL admin can, however, get NTLM working on their own in almost all cases as it isn't as complex (or as secure). I looked at the documentation for JDBC connection to SQL with NTLM, and they don't mention NTLM with username/password as an additional security risk. They only talk about the dangers of NTLM in general. If it is that terrible, you should really mention it in your documentation.

https://docs.microsoft.com/en-us/sql/connect/jdbc/using-ntlm-authentication-to-connect-to-sql-server?view=sql-server-ver15

So effectively, you are telling a lot of SQL environments to switch entirely to SQL Authentication. Ok, how does that work with 100 servers? You now have users with 100 logins each, all with potentially different passwords. Do you think they will use good passwords with those? Or mainly have a simple pattern that barely passes the domain requirements (if you have it setup to use those on each login/server), but could still be easily guessed. Do you think they will want to change their passwords ever? If they make enough noise, they effectively won't in many companies. What if users switched from Windows to Mac? They used to be able to use NTLM (integrated), but now they can't. Trying to get end users to get Kerberos working correctly on their workstations isn't too likely to happen, so now they have to switch to Java JDBC(with NTLM) or SQL Authentication. I guess .net core is out of the question for those developers(as is Azure Data Studio), unless they want more work.

I was involved in the discussion about NTLM in the JDBC driver before they added it and there wasn't much talk about it being for large installed applications using jTDS. That was mentioned, but most of it was similar to the above arguments. It's easier to use/configure and more secure than SQL Authentication were some of the biggest points (while acknowledging that it is less secure than Kerberos).

@brianlagunas So please do create a PR. If MS wants to shoot developers in the foot, they can do that, but let them do it. I for 1 would really appreciate it being added and I think there are a lot of other people who would appreciate it too. I already have permissions to SQL through my AD account. I'd like to get Powershell Core working on Linux, but I have to create a new SQL login everywhere to get it working as Kerberos isn't working correctly across domains in our environment. I can however use DBeaver just fine from a Linux desktop to get to SQL using my existing AD account(using NTLM).

[brianlagunas]

This would be no small PR, and I am hesitant to try due to this statement:

"We have no plans to support NTLM Authentication in SqlClient as it's insecure and deprecated now. Kerberos is more advanced and secure way of authentication, and always recommended."

See comment #143 (comment)

I do not want to spend a ton of time working on this if it will not even be considered for implementation. I also had to take a dependency on an old project called SharpCifs.Std so the important part of that code would need to be moved into source as well. I'm not confident that MS will accept this PR.

For now, I will use my private build, but I may consider publishing my build publicly so all can benefit from it. I would need to get clearance from my company first.

[David-Engel]

I was involved in the discussion about NTLM in the JDBC driver before they added it and there wasn't much talk about it being for large installed applications using jTDS. That was mentioned, but most of it was similar to the above arguments. It's easier to use/configure and more secure than SQL Authentication were some of the biggest points (while acknowledging that it is less secure than Kerberos).

@FireInWinter Yes, the nature of cross-plat was another big argument in Java. I was involved in those discussions, too. I went to bat for the feature with the security team. Given the security discussions we had back then, I'm very hesitant to bring NTLM to the table again. From what I've learned since then, I kind of wish I hadn't pushed for it in JDBC. I do understand the user pain around Kerberos, especially cross-plat. But enabling users to do things that are insecure runs counter to the current security trend and when they are exploited against customers, it obviously makes the provider of the software look bad.

From a security perspective, while NTLM is arguably more secure than SQL authentication, compromised Windows/Domain credentials offer bad actors a much larger attack surface than a compromised SQL account.

Regards,
David

[FireInWinter]

@David-Engel I guess we'll have to agree to disagree.

I find that most users set their SQL login password to the same password as their AD account and set all of their SQL logins to the same password. So if any one of the 101 logins (1 AD and 100 SQL) get's compromised, they all are. Although with that many logins I guess they are including a pattern in their password (they do the same thing with their AD account) so they can identify old passwords if they can't log into some system with their current one (based on talking with a number of people). So I find the risk higher as a compromised SQL account can likely give them access to their AD account as well. Same thing in reverse as well, if the AD account is compromised, most likely the SQL ones are as well.

@brianlagunas I can understand not wanting to go through the effort of creating a PR based on the conversations that have happened here. I still think the feature is one that would be beneficial to have, but if MS is against it and won't allow people to contribute it, then there isn't much more to do other than to potentially fork it (and all the work that goes with maintaining it). So thanks for offering the code, I'm sorry it didn't amount to much.

[3dbrows]

I may consider publishing my build publicly so all can benefit from it. I would need to get clearance from my company first.

@brianlagunas , I was wondering if you had published your build publicly? Is this you? https://www.nuget.org/packages/IG.Data.SqlClient/

If so, does the connection string need to specify NTLM somehow?

[brianlagunas]

@3dbrows yes that's our package that we use for our product that requires NTLM on Linux. As far as I know just using the username/password with intergrated security = true should work, but it's been a while since we had to mess with this code.

[capcom923]

Thanks @brianlagunas & @3dbrows , it(SqlConnection) works fine both in Windows and Linux after installed IG.Data.SqlClient package in case we only use this package. I also think the support NTLM is a significant feature for SqlClient, especially in the docker world nowadays.

[brianlagunas]

@capcom923 I agree. There should be a way to opt into this behavior instead of us having to maintain a separate package for it.

[capcom923]

@capcom923 I agree. There should be a way to opt into this behavior instead of us having to maintain a separate package for it.

Yes @brianlagunas , and this separate package way seems hard to work with ORM.
For example, given a project which used EntiryFrameworkCore as ORM, the IG.Data.SqlClient is not working any more after installed. The UID and PWD will not be used when set intergrated security = true. The connection will still use the identity of the running application.
So, is that possible to make IG.Data.SqlClient work with EFcore v5.0.17?

[brianlagunas]

To add this functionality yourself, add a package reference to SharpCifs.std. Then in the netcore/Microsoft.Data.Sql/Data/SqlClint/TdsParser.SNISSPIData, use this code instead

        private void SNISSPIData(byte[] receivedBuff, uint receivedLength, ref byte[] sendBuff, ref uint sendLength)
        {
            if (_connHandler.ConnectionOptions.IntegratedSecurity
                && !string.IsNullOrEmpty(_connHandler.ConnectionOptions.UserID) && !string.IsNullOrEmpty(_connHandler.ConnectionOptions.Password))
            {
                if (receivedBuff == null) // first message
                {
                    var type1Message = new SharpCifs.Ntlmssp.Type1Message(); //[review] FLAGS
                    type1Message.SetSuppliedWorkstation(_connHandler.ConnectionOptions.ObtainWorkstationId());
                    sendBuff = type1Message.ToByteArray();
                    sendLength = (uint)sendBuff.Length;
                }
                else // challenged
                {
                    var type2MessageBytes = new byte[receivedLength];
                    Array.Copy(receivedBuff, type2MessageBytes, receivedLength);
                    var type2Message = new SharpCifs.Ntlmssp.Type2Message(receivedBuff);
                    var userId = _connHandler.ConnectionOptions.UserID;
                    var domain = userId.Split('\\')[0];
                    var user = userId.Split('\\')[1];
                    var type3Message = new SharpCifs.Ntlmssp.Type3Message(type2Message, _connHandler.ConnectionOptions.Password, domain, user, _connHandler.ConnectionOptions.ObtainWorkstationId(), type2Message.GetFlags());
                    sendBuff = type3Message.ToByteArray();
                    sendLength = (uint)sendBuff.Length;
                }
            }
            else // the following block is the original.
            {
                if (TdsParserStateObjectFactory.UseManagedSNI)
                {
                    try
                    {
                        _physicalStateObj.GenerateSspiClientContext(receivedBuff, receivedLength, ref sendBuff, ref sendLength, _sniSpnBuffer);
                    }
                    catch (Exception e)
                    {
                        SSPIError(e.Message + Environment.NewLine + e.StackTrace, TdsEnums.GEN_CLIENT_CONTEXT);
                    }
                }
                else
                {
                    if (receivedBuff == null)
                    {
                        // if we do not have SSPI data coming from server, send over 0's for pointer and length
                        receivedLength = 0;
                    }

                    // we need to respond to the server's message with SSPI data
                    if (0 != _physicalStateObj.GenerateSspiClientContext(receivedBuff, receivedLength, ref sendBuff, ref sendLength, _sniSpnBuffer))
                    {
                        SSPIError(SQLMessage.SSPIGenerateError(), TdsEnums.GEN_CLIENT_CONTEXT);
                    }
                }
            }
        }

I believe that is all that was needed.

We will soon make our repo public and update the fork and our NuGet package. Until then, this should unblock you.

[capcom923]

Thank you so much @brianlagunas for your talent code~ Now, I understand that you split the connStr and modify the sendBuff using SharpCifs.
Looking forword your public repo~

[brandonros]

@brianlagunas any movement on making a NuGet package for this available/repo public/more fleshed out example? https://www.nuget.org/packages/SharpCifs.Std/ says it's for netstandard1.3, I'm guessing it's a bit out of date for those of us looking to use net6.0+?

[brianlagunas]

@brandonros Not really. This hasn't been a high priority for me since it's working for my project, and I don't really want to have to support this for the public 😄. I just don't have the time.

[cdibbs]

We're running into this too and also burned too many hours assuming it was supported (it would seem a rather big omission) and trying to figure out what we were doing wrong. This became especially true since early testing showed that UseDefaultCredentials = true with HttpClient works fine.

Any movement on it?

[twsouthwick]

FYI - I've got a plan and PRs slowly getting merged in to enable this. Please see the #2253 for details. The general idea is to add a hook at the right point to handle the sspi blobs.

I have a project at https://github.com/twsouthwick/Swick.SqlClient.Sspi that I'll release when it's merged in to enable some easy providers for it. Initially I have one that enables NTLM that I currently need for another project, but wanted to share it in case others have use of it.

[JeremiasBadilla]

hi,
I have implemented
https://github.com/twsouthwick/Swick.SqlClient.Sspi
when trying to perform a savechanges() with Entityframework core, it generates an error.

I am using two connection strings
using var connection = new SqlConnection(_conectionString.Value.ConnectionString)
{
SSPIContextProviderFactory = Ntlm.CreateLoggingProvider
}
;
await connection.OpenAsync();
using var command = new SqlCommand(sql, connection);
using var reader = await command.ExecuteReaderAsync();
_conectionString.Value.ConnectionString = "Server=xxxxxx\xxxxxx;Database=xxxxxx;MultipleActiveResultSets=true;Uid=xxxxxxx;Pwd=xxxxx;"
(I also tried Integrated Security=True;)

It is essential to have NTLM implementation in the sqlclient because the application will run in a production environment with nginx.
However, implementing the package causes Entity Framework Core (6.0.19) to stop working with another connection string.

("Server=yyyyy\yyyyyyy:1435;Database=yyyyy;MultipleActiveResultSets=true;Integrated Security=True;TrustServerCertificate=True;")

Do you have any ideas how to resolve this scenario?

[ErikEJ]

@JeremiasBadilla which error?

Please create an issue with full repro details and stack trace

[David-Engel]

Closing this issue in favor of #2253 moving forward.

[qingdao05]

@capcom923
You say
it(SqlConnection) works fine both in Windows and Linux after installed IG.Data.SqlClient package in case we only use this package.

I installed IG.Data.SqlClient but it does not work properly with NTLM authentication on Windows.
Could you tell me how you programmed it?

[roji]

@qingdao05 please read the above comments before posting a new one (specifically this one).