Wednesday, June 19, 2013

1 NTLMv2 and FreeTDS

Windows use NTLM authentication for very long time. FreeTDS is very popular open source database driver.  Recently, I spend quite some time to trouble shooting how to use FreeTDS connect to SQL Server which has NTLMv2 enable.

 

How to determine the NTLM version

The NTLM setting can be configure on both Server side and client side. Here are the steps you can check and verify the NTLM setting.

gpedit.msc -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> Security Options –>  Network Security: LAN Manager Authentication Level.

image

image

reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa /v LmCompatibilityLevel

image

When set as “Send NTLMv2 response only. Refuse LM&NTLM”, the value reflect on the register key is 5.

Here list all the incompatibility :

  • 0: Clients use LM and NTLM authentication, but they never use NTLMv2 session security. Domain controllers accept LM, NTLM, and NTLMv2 authentication.
  • 1: Clients use LM and NTLM authentication, and they use NTLMv2 session security if the server supports it. Domain controllers accept LM, NTLM, and NTLMv2 authentication.
  • 2:Clients use only NTLM authentication, and they use NTLMv2 session security if the server supports it. Domain controller accepts LM, NTLM, and NTLMv2 authentication.
  • 3:Clients use only NTLMv2 authentication, and they use NTLMv2 session security if the server supports it. Domain controllers accept LM, NTLM, and NTLMv2 authentication.
  • 4:Clients use only NTLMv2 authentication, and they use NTLMv2 session security if the server supports it. Domain controller refuses LM authentication responses, but it accepts NTLM and NTLMv2.
  • 5:Clients use only NTLMv2 authentication, and they use NTLMv2 session security if the server supports it. Domain controller refuses LM and NTLM authentication responses, but it accepts NTLMv2.

Essentially, if the value is set to 5. the Server would only accept the NTLMv2.

FreeTDS support and configuration

The older version of the freeTDS (0.8)  does not support the NTLMv2 and it require the configuration in the newer release ( 0.9.)

Here is the sample of freetds.conf

[DB SERVER NAME]
        host = 127.0.0.1
        port = 8001
        use ntlmv2 = yes

 

The “use ntlmv2=yes” keyword is required for FreeTDS to use NTLMv2. If you use the older version of the FreeTDS, it would simply ignore this value. If the OS does not use NTLMv2, it will fail back to use NTLM.  Therefore I see no downside to set it up in the freetds.conf.

If the OS is set to NTLMv2 (5) but there is no NTLMv2 support turn on on the FreeTDS, we would get the error message like below :

 

Msg 18452 (severity 14, state 1) from DEPOTSQL Line 1: "Login failed. The login is from an untrusted domain and cannot be  used with Windows authentication.

Reference

1 comments:

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates