feature: support NTLMv2

[stephenlorenz]

trafficstars

I am trying to login to a SQL Server 2012 instance in a large enterprise computing environment. From other languages/platforms (e.g. Java JTDS) we typically use Windows domain credentials to authenticate without a problem. Instead of using full-fledged user accounts, we use so called "service accounts" to authenticate. Our "service accounts" are standard Windows domain account except they do not have permission to login to corporate workstations; they only have the permissions to connect to this SQL Server instance and other select web services around the organization. However, these "service accounts" do not seem to have the ability to authenticate using this driver. Instead they receive the error message:

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

I am able to use my personal Windows credentials with this driver without any problem. My connection string looks like this (sensitive parameters are redacted):

server=sqlserver.mydomain.org;port=1433;user id=mydomain\\serviceacct1;password=*******;database=test;log=63

SQL Server version details:

$ select @@version
Microsoft SQL Server 2012 - 11.0.5613.0 (X64) 
May  4 2015 19:05:02 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

I do not know much about the intricacies of SQL Server authentication but somehow the Java JTDS driver is able to authenticate with these "service accounts" without any issue, so I suspect this issue is resolvable.

[dimdin]

Hello @stephenlorenz

Does your client machine belong to mydomain.org ?

If it is joined mydomain.org and the problem persists, find from the SQL server log the server SPN and set it using the drivers ServerSPN connection parameter. The drivers default SPN for your case is MSSQLSvc/sqlserver.mydomain.org:1433

[stephenlorenz]

No, our client machines does not belong to mydomain.org. Unfortunately, the Linux servers and/or containers where this Go application will run will likely not be joined to the domain. Much of our development will likely be on a mix of Macs and Windows; the Macs will not be joined to the domain. Is there support for using domain accounts without the host environment joining the domain? This has been a typical configuration for us with other platforms/languages (Java, Python, etc) for years.

[dimdin]

For non windows clients, only SQL Server authentication and NTLM v1 are supported. Your best option is using SQL Server authentication since NTLM v1 support is dropped from recent windows servers because it is not secure.

[stephenlorenz]

Thanks @dimdin

Is it possible to use SQL Server authentication with DOMAIN\username accounts? If so, how can we specify this in the connection string? I didn't notice any documentation on how to specify which authentication scheme to use.

[dimdin]

You cannot use domain accounts in SQL Server authentication, SQL Server authentication means that the users and passwords are kept in the SQL Server (see https://msdn.microsoft.com/en-us/library/ms144284.aspx )

You might me able to use a user from the SQL Server machine (try sqlserver\sqlserverlocaluser )

If there is no backslash \ in the user name the driver is using SQL Server authentication.

[stephenlorenz]

Thanks @dimdin

That's what I suspected. Unfortunately, our development group doesn't administer the SQL Server so we can't easily add local SQL accounts. As you might suspect, any request to add local accounts will be met with derision from the DBAs!

Thank you for your guidance. I appreciate your time. Fortunately, our project is still early in development so this gives us the chance to reconsider our design or choice of platform.

[lrtanner]

+1 on this request. I have spent the last 4 days trying to figure out why I can't connect to SQL Server while using this driver and I think supporting NTLMv2 would have avoided me ever having an issue. From what I can find, NTLMv1 is ancient. I have been reading up on it, and it looks like NTLMv2 has been available since Windows NT 4.0 which was released in 1996. If that is accurate, any one using this driver that fails the initial Kerberos authentication will completely fail the connection since all modern networks will be using NTLMv2.

[akamac]

NTLMv2 is crucial to have, since we have to shut down v1 due to security requirements. That means Grafana can no longer bind to MS SQL.

[reuvygroovy]

Does anyone know the path where the source code for the datasource is stored?

[NikitaDef]

@akamac @stephenlorenz @reuvygroovy @dimdin I created the MR. #570 . please check nmltV2 after my fix

[ftjfo]

Is my understanding correct, that a client connection from a standalone linux instance (not in domain) will fail if I use use AD-domain account for authentication? I.e. user id=domain\username etc... At least I get the :

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

Then, if the linux instance is added to the AD-domain, can it work then, or is there some fundamental problem preventing it even in that case?

[NikitaDef]

Is my understanding correct, that a client connection from a standalone linux instance (not in domain) will fail if I use use AD-domain account for authentication? I.e. user id=domain\username etc... At least I get the :

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

Then, if the linux instance is added to the AD-domain, can it work then, or is there some fundamental problem preventing it even in that case?

Support added #570, the driver emulates the domain and username, which you write in the connection string

[ftjfo]

@NikitaDef For others that might encounter the same problem. It turned out, that in my case, the reason I got the "The login is from an untrusted domain and cannot be used with Integrated authentication." message was simply wrong password! Fixing the password to the correct one made all the difference. One would expect though, that the error message was a little more precise, but I guess this might origin from the sql engine or similar.

[bechtle-simon-mueller]

Thank you so much @ftjfo. I was almost at a point where I questioned my career and other life choices, but changing the password to something with less special characters like `~/^'|"&´ did the trick for me.