IMPORTANT CHANGE:
The following command also works and gets me the correct prompt. There must then be an issue with pymssql.
sudo TDSVER=7.1 tsql -H asdf.database.windows.net -p 1433 -U adf@ad -P adsf#adf -D adf
So I'm fighting with my pymssql and freetds drivers.
Platform Versions Etc:
Ubuntu 16.04
FreeTDS v0.91 (used by working tsql)
FreeTDS v0.95 (used by pymssql)
pymssql v2.1.3
Target Database: SQL Azure (latest)
Instructions for install: https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple/
I've gone into every freetds.conf file I can find: /etc/freetds/freetds.conf ; /root/.freetds.conf
I have set the global TDS version to 8.0. I have overwritten from the python perspective in my pymssql.connect to overwrite the version to 8.0
import os
os.environ['TDSDUMP'] = 'stdout'
import pymssql
conn = pymssql.connect(server='adsf.database.windows.net',
user='asdf@adfs',
password='asdf#adfad',
database='asdd',
tds_version='8.0',
)
I run the diagnostics tools:
tsql -C and get back 4.2 as the version
I run the code dumping the logs to stdout, and notice, the version is 7.1.
net.c:202:Connecting to 191.238.6.43 port 1433 (TDS version 7.1)
The following tsql command works for me...
sudo TDSVER=8.0 tsql -H asdf.database.windows.net -p 1433 -U adf@ad -P adsf#adf -D adf
Notice the version number. Its 8.0. I can validate I get data back and can do all I want with this.
So there is an obvious issue here with how pymssql is hooking up with freetds.
Here is all the output from the log dump in case somebody sees something I am failing to...
>>> import os
>>> os.environ['TDSDUMP'] = 'stdout'
>>> import pymssql
>>> conn = pymssql.connect(server='xxx.database.windows.net',
... user='xxx@xxx',
... password='xxxx',
... database='xxx',
... tds_version='8.0',
... )
log.c:167:Starting log file for FreeTDS 0.95
on 2016-08-27 20:47:18 with debug flags 0x4fff.
dblib.c:1160:tdsdbopen(0x15d1070, xxx.database.windows.net:1433, [microsoft])
dblib.c:1186:tdsdbopen: dbproc->dbopts = 0x15fa760
dblib.c:1193:tdsdbopen: tds_set_server(0x14eacf0, "xxx.database.windows.net:1433")
dblib.c:258:dblib_get_tds_ctx(void)
dblib.c:1210:tdsdbopen: About to call tds_read_config_info...
config.c:168:Getting connection information for [xxx.database.windows.net:1433].
config.c:172:Attempting to read conf files.
config.c:353:... $FREETDSCONF not set. Trying $FREETDS/etc.
config.c:366:... $FREETDS not set. Trying $HOME.
config.c:296:Found conf file '/root/.freetds.conf' (.freetds.conf).
config.c:495:Looking for section global.
config.c:554: Found section egserver50.
config.c:554: Found section xxx.database.windows.net.
config.c:568: Reached EOF
config.c:495:Looking for section xxx.database.windows.net:1433.
config.c:554: Found section egserver50.
config.c:554: Found section xxx.database.windows.net.
config.c:568: Reached EOF
config.c:302:[xxx.database.windows.net:1433] not found.
config.c:296:Found conf file '/etc/freetds/freetds.conf' (default).
config.c:495:Looking for section global.
config.c:554: Found section global.
config.c:557:Got a match.
config.c:580: text size = '64512'
config.c:554: Found section egserver50.
config.c:554: Found section egserver70.
config.c:568: Reached EOF
config.c:495:Looking for section xxx.database.windows.net:1433.
config.c:554: Found section global.
config.c:554: Found section egserver50.
config.c:554: Found section egserver70.
config.c:568: Reached EOF
config.c:302:[xxx.database.windows.net:1433] not found.
config.c:353:... $FREETDSCONF not set. Trying $FREETDS/etc.
config.c:366:... $FREETDS not set. Trying $HOME.
config.c:296:Found conf file '/root/.freetds.conf' (.freetds.conf).
config.c:495:Looking for section global.
config.c:554: Found section egserver50.
config.c:554: Found section xxx.database.windows.net.
config.c:568: Reached EOF
config.c:495:Looking for section xxx.database.windows.net.
config.c:554: Found section egserver50.
config.c:554: Found section xxx.database.windows.net.
config.c:557:Got a match.
config.c:580: host = 'xxx.database.windows.net'
config.c:617:Found host entry xxx.database.windows.net
config.c:620:IP addr is 191.238.6.43.
config.c:580: port = '1433'
config.c:580: tds version = '8.0'
config.c:886:Setting tds version to 8.0 (0x701).
config.c:568: Reached EOF
config.c:300:Success: [xxx.database.windows.net] defined in /root/.freetds.conf.
config.c:765:Setting 'dump_file' to 'stdout' from $TDSDUMP.
config.c:689:tds_config_login: client_charset is UTF-8.
config.c:696:tds_config_login: database_name is xxx.
config.c:765:Setting 'dump_file' to 'stdout' from $TDSDUMP.
dblib.c:1237:tdsdbopen: Calling tds_connect_and_login(0x15fae30, 0x15fb4f0)
iconv.c:328:tds_iconv_open(0x15fae30, UTF-8)
iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
iconv.c:187:local name for UTF-8 is UTF-8
iconv.c:187:local name for UCS-2LE is UCS-2LE
iconv.c:187:local name for UCS-2BE is UCS-2BE
iconv.c:346:setting up conversions for client charset "UTF-8"
iconv.c:348:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:395:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:400:tds_iconv_open: done
net.c:202:Connecting to xxx.xxx.xxx.xxx port 1433 (TDS version 7.1)
net.c:275:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:314:tds_open_socket() succeeded
packet.c:740:Sending packet
0000 12 01 00 34 00 00 00 00-00 00 15 00 06 01 00 1b |...4.... ........|
0010 00 01 02 00 1c 00 0c 03-00 28 00 04 ff 08 00 01 |........ .(......|
0020 55 00 00 02 4d 53 53 51-4c 53 65 72 76 65 72 00 |U...MSSQ LServer.|
0030 08 4d 00 00 - |.M..|
packet.c:639:Received packet
0000 04 01 00 25 00 00 01 00-00 00 15 00 06 01 00 1b |...%.... ........|
0010 00 01 02 00 1c 00 01 03-00 1d 00 00 ff 0c 00 03 |........ ........|
0020 2b 00 00 03 00 - |+....|
login.c:1106:detected flag 3
login.c:472:login packet rejected
query.c:3772:tds_disconnect()
util.c:165:Changed query state from IDLE to DEAD
util.c:322:tdserror(0x14aafa0, 0x15fae30, 20002, 0)
dblib.c:7925:dbperror(0x15fa390, 20002, 0)
dblib.c:7993:dbperror: Calling dblib_err_handler with msgno = 20002; msg->msgtext = "Adaptive Server connection failed (xxx.database.windows.net:1433)"
dblib.c:8015:dbperror: dblib_err_handler for msgno = 20002; msg->msgtext = "Adaptive Server connection failed (xxx.database.windows.net:1433)" -- returns 2 (INT_CANCEL)
util.c:352:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:375:tdserror: returning TDS_INT_CANCEL(2)
dblib.c:1241:tdsdbopen: tds_connect_and_login failed for "xxx.database.windows.net:1433"!
dblib.c:1463:dbclose(0x15fa390)
dblib.c:243:dblib_del_connection(0x7f7f78fbd980, 0x15fae30)
mem.c:648:tds_free_all_results()
dblib.c:290:dblib_release_tds_ctx(1)
dblib.c:5873:dbfreebuf(0x15fa390)
dblib.c:743:dbloginfree(0x15d1070)
Traceback (most recent call last):
File "pymssql.pyx", line 635, in pymssql.connect (pymssql.c:10734)
File "_mssql.pyx", line 1902, in _mssql.connect (_mssql.c:21821)
File "_mssql.pyx", line 637, in _mssql.MSSQLConnection.__init__ (_mssql.c:6581)
File "_mssql.pyx", line 1630, in _mssql.maybe_raise_MSSQLDatabaseException (_mssql.c:17524)
_mssql.MSSQLDatabaseException: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (ea1eg7cgdn.database.windows.net:1433)\n')
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "<stdin>", line 5, in <module>
File "pymssql.pyx", line 641, in pymssql.connect (pymssql.c:10824)
pymssql.OperationalError: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (xxxx.database.windows.net:1433)\n')
It appears that freetds v0.95 is not compatible with Ubuntu 16.04 and neither is the latest version of pymssql since it ships with v0.95 and it appears there isn't much you can do to swap the version it uses out.
I was able to get this to work by doing the following:
sudo apt-get install freetds-dev freetds-bin
sudo pip3 install pymssql=2.1.1
Also note that it will not work with the Anaconda Interpreter. I have only tested the Anaconda Interpreter and the standard CPython interpreter.