Setting `use ntlmv2 = yes` within ctds

[kadrach]

We have some SQL Servers that require configuring FreeTDS via freetds.conf to use ntlmv2 = yes. This appears to be supported in ODBC connection strings for FreeTDS as well.

Is there a way to achieve this programmatically with ctds (without the configuration file)?

[kadrach]

Digging around a bit this should be possible with DBSETLNTLMV2 in FreeTDS, now I just need to figure out why what I hacked into ctds is not working yet :)

[joshuahlang]

Does this branch work for you?

https://github.com/zillow/ctds/tree/ntlmv2

[kadrach]

@joshuahlang Cheers, that patch is along the lines of what I had hacked together.

There seems to be an underlying issue with FreeTDS here - it doesn't like connecting when I build with the latest stable freetds. I'll dig around some more.

[kadrach]

Leaving a few notes here.

Confirm connectivity

[root@e6258fcb674f build]# telnet 10.74.49.35 1433
Trying 10.74.49.35...                             
Connected to 10.74.49.35.                         
Escape character is '^]'.                         

FreeTDS

Building

Using quay.io/pypa/manylinux1_x86_64

yum install -y openssl-devel

curl -L 'ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz' > /tmp/freetds-patched.tar.gz
tar -xzf /tmp/freetds-patched.tar.gz -C /tmp
pushd /tmp/freetds-*
./configure --with-tdsver=7.3 \
    ┆   ┆   --with-openssl
make && make install
popd

Compile-time settings (established with the "configure" script)
                            Version: freetds v1.00.97          
             freetds.conf directory: /usr/local/etc            
     MS db-lib source compatibility: no                        
        Sybase binary compatibility: no                        
                      Thread safety: yes                       
                      iconv library: yes                       
                        TDS version: 7.3                       
                              iODBC: no                        
                           unixodbc: no                        
              SSPI "trusted" logins: no                        
                           Kerberos: no                        
                            OpenSSL: yes                       
                             GnuTLS: no                        
                               MARS: no                        

Trying to connect without ntlmv2

[root@e6258fcb674f build]# tsql -S 10.74.49.35 -U "mydomain\\kadrach"
Password:                                                                      
locale is "en_US.UTF-8"                                                        
locale charset is "UTF-8"                                                      
using default charset "UTF-8"                                                  
Error 20002 (severity 9):                                                      
        Adaptive Server connection failed                                      
        OS error 110, "Connection timed out"                                   
There was a problem connecting to the server                   

Trying to connect with ntlmv2

Modify /usr/local/etc/freetds.conf to include use ntlmv2 = yes in global section.

[root@e6258fcb674f build]# tsql -S 10.74.49.35 -U "mydomain\\kadrach"
Password:                                                                      
locale is "en_US.UTF-8"                                                        
locale charset is "UTF-8"                                                      
using default charset "UTF-8"                                                  
1>                                                                             

FreeTDS all good.

ctds

export PATH=/opt/python/cp36-cp36m/bin:${PATH}
pip install git+https://github.com/zillow/ctds@ntlmv2
...

import ctds                                          
server = "10.74.49.35"                               
user = r"mydomain\kadrach"
password = "..."  

connection = ctds.connect(                           
    server,                                          
    user=user,                                       
    password=password,                               
)                                                    
with connection:                                     
    with connection.cursor() as cursor:              
        cursor.execute('SELECT @@VERSION AS Version')
        row = cursor.fetchone()                      
        print(row['Version'])                        

use ntlmv2 is still set in config!

python test.py
[root@e6258fcb674f build]# python test.py                                               
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)                                     
        Dec 28 2012 20:23:12                                                            
        Copyright (c) Microsoft Corporation                                             
        Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) 

Removing usentlmv2 config entry

[root@e6258fcb674f build]# python test.py                                                                 
Traceback (most recent call last):                                                                        
  File "test.py", line 9, in <module>                                                                     
    password=password,                                                                                    
_tds.OperationalError: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.                                                                                          

Now with ntlmv2 flag:

[root@e6258fcb674f build]# python test.py
Traceback (most recent call last):
  File "test.py", line 10, in <module>
    ntlmv2=True
_tds.OperationalError: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

Conclusion

This is the same issue I had when I was hacking around. DBSETLNTLMV2 seems to have no effect. Using the configuration file makes it all work.

[kadrach]

Comparing TDSDUMP logs for the last two scenarios shows that it's definitely not registering the ntlmv2 instruction. Setting this in config causes it to send a whole lot of additional info in the "challenge" packet.

[joshuahlang]

I believe this was fixed in FreeTDS here: https://github.com/FreeTDS/freetds/commit/3e11437b4c26c71d063984f360ff89a61e90cfc6

Doesn't appear that change has been merged to the stable (Branch-1_00) branch yet

[kadrach]

Building FreeTDS from snapshot instead of stable, I'm unable to establish connection with tsql at all. I'll try stable and patching in just the commit you mention.

login.c:537:login packet rejected

[kadrach]

Building FreeTDS stable with patch -p1 < 3e11437.patch makes it all work, thank you very much!

Are you planning to merge your ntlmv2 branch into master given this issue? I'll build my wheels based on this branch for now :)

[joshuahlang]

Yeah I'll merge it in and hopefully get an official release out in the next day or so.

[joshuahlang]

Fixed in v1.8.0.