1

I met a really weird issue: I'm able to connect to my SQL Server with tsql but I'm not able to do the same with pymssql. Let me give you more details.

With this kind of tsql command everything seems to be ok:

$ tsql -H '10.10.10.2' -U 'DOMAIN\myuser' -p 63849
Password: 
locale is "fr_FR.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

But with the following basic python script I got an issue:

$ cat test_mssql.py
# -*- coding: utf8 -*-
import pymssql
import pandas as pd

conn = pymssql.connect(server=r'10.10.10.2', user=r'DOMAIN\myuser', password='mypassword', port='63849')

$ python test_mssql.py
Traceback (most recent call last):
File "api-vinci-rh/current/test_mssql.py", line 60, in <module>
conn = pymssql.connect(server=r'10.10.10.2', user=r'DOMAIN\myuser', password='mypassword', port='63849')
File "pymssql.pyx", line 641, in pymssql.connect (pymssql.c:10788)
pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed\n')

By activating TDSDUMPCONFIG env variable, I can see a difference between the tls command and my python script:

TDSDUMPCONFIG tls trace
config.c:224:Final connection parameters:
config.c:225:            server_name = 10.10.10.2
config.c:226:       server_host_name = 10.10.10.2
config.c:227:                ip_addr = 10.10.10.2
config.c:228:          instance_name = 
config.c:229:                   port = 63849
config.c:230:          major_version = 7
config.c:231:          minor_version = 1
config.c:232:             block_size = 0
config.c:233:               language = us_english
config.c:234:         server_charset = 
config.c:235:        connect_timeout = 0
config.c:236:       client_host_name = myhost
config.c:237:         client_charset = UTF-8
config.c:238:               app_name = TSQL
config.c:239:              user_name = DOMAIN\myuser
config.c:242:                library = TDS-Library
config.c:243:              bulk_copy = 0
config.c:244:      suppress_language = 0
config.c:245:          encrypt level = 0
config.c:246:          query_timeout = 0
config.c:249:               database = 
config.c:250:              dump_file = /tmp/freetds.log
config.c:251:            debug_flags = 0


TDSDUMPCONFIG pymssql trace
config.c:224:Final connection parameters:
config.c:225:            server_name = 10.10.10.2:63849
config.c:226:       server_host_name = 10.10.10.2
config.c:227:                ip_addr = 10.10.10.2
config.c:228:          instance_name = 
config.c:229:                   port = 63849
config.c:230:          major_version = 7
config.c:231:          minor_version = 1
config.c:232:             block_size = 0
config.c:233:               language = us_english
config.c:234:         server_charset = 
config.c:235:        connect_timeout = 0
config.c:236:       client_host_name = myhost
config.c:237:         client_charset = UTF-8
config.c:238:               app_name = pymssql=2.1.2
config.c:239:              user_name = 
config.c:242:                library = DB-Library
config.c:243:              bulk_copy = 0
config.c:244:      suppress_language = 0
config.c:245:          encrypt level = 0
config.c:246:          query_timeout = 0
config.c:249:               database = 
config.c:250:              dump_file = /tmp/freetds.log
config.c:251:            debug_flags = 0
config.c:252:              text_size = 64512
config.c:253:           broken_dates = 0
config.c:254:     emul_little_endian = 0
config.c:255:      server_realm_name = 

I think the 2 main differences is about the user_name which is empty with the python script and the library which is DB-Library instead of TDS-Library.

I currently find no way to force these parameters in the python script and I find no clue about my issue (the error message is a generic one).

I'm using python 2.7.9 and pymssql==2.1.2 More info about my env:

$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                          iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes

$ grep -v '^#' /etc/freetds/freetds.conf 
[global]
    tds version = 7.1
    dump file = /tmp/freetds.log
    text size = 64512
    enable gssapi delegation = off

If you have any clue to help let me know.

Edit: I have another SQL Server instance for my test and my python script works... the username in the TDSDUMPCONFIG is set. So Iguess there is something strange on my env but don't know what

4
  • Adaptive Server connection ? That's smells like Sybase. Are you sure you aren't using a Sybase provider by mistake? Microsoft bought code to ... Adaptive Server 20 years ago. Both companies use names like TDS and T-SQL Commented Nov 16, 2016 at 12:42
  • In any case, you didn't post any T-SQL code. What you posted are command-line parameters of different command line tools. tsql is a FreeTDS utility. Unless your Python code also uses FreeTDS, you can't use the results of tsql as an indication that your code is OK Commented Nov 16, 2016 at 12:46
  • @PanagiotisKanavos you right: I didn't post and T-SQL code but I can confirm with tsql client I'm able to request the data Commented Nov 16, 2016 at 13:19
  • @PanagiotisKanavos - (1) FreeTDS routinely returns error messages that refer to "Adaptive Server", even when connecting to Microsoft SQL Server. (2) pymssql is built on top of FreeTDS, so if the Python code is using pymssql then it is (indirectly) using FreeTDS. Commented Nov 17, 2016 at 19:07

1 Answer 1

1

I find a clue and I'm quite surprised byt it... If I use a username with less than 32 characters, I have no more issue with pymssql ! I didn't find anything about this weird bug (?) but I promess it fixes my issue.

I need to see the code of pymssql and see if I find any lenght restriction or issue about the lenght of the username

Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for reporting your findings. The limitation you encountered is discussed here. The good news is that current and future versions of pymssql should not be affected now that FreeTDS versions later than v0.91 have been patched to allow longer usernames and passwords via the db-lib API, which is the API that pymssql uses.
thank you for these details @GordThompson :) I missed it!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.