TDSVersion keeps defaulting to 7.1 pymssql
<p>IMPORTANT CHANGE:</p>

<p>The following command also works and gets me the correct prompt.  There must then be an issue with pymssql.</p>

<pre><code>sudo TDSVER=7.1 tsql -H asdf.database.windows.net -p 1433 -U adf@ad -P adsf#adf -D adf
</code></pre>

<p>So I'm fighting with my pymssql and freetds drivers.</p>

<p>Platform Versions Etc:</p>

<p>Ubuntu 16.04</p>

<p>FreeTDS v0.91 (used by working tsql)</p>

<p>FreeTDS v0.95 (used by pymssql)</p>

<p>pymssql v2.1.3</p>

<p>Target Database: SQL Azure (latest)</p>

<p>Instructions for install: <a href="https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple/" rel="nofollow">https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple/</a></p>

<p>I've gone into every freetds.conf file I can find: /etc/freetds/freetds.conf ; /root/.freetds.conf</p>

<p>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</p>

<pre><code>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',
                   )
</code></pre>

<p>I run the diagnostics tools:</p>

<p>tsql -C and get back 4.2 as the version</p>

<p>I run the code dumping the logs to stdout, and notice, the version is 7.1.</p>

<p>net.c:202:Connecting to 191.238.6.43 port 1433 (TDS version 7.1)</p>

<p>The following tsql command works for me...</p>

<pre><code>sudo TDSVER=8.0 tsql -H asdf.database.windows.net -p 1433 -U adf@ad -P adsf#adf -D adf
</code></pre>

<p>Notice the version number.  Its 8.0.  I can validate I get data back and can do all I want with this.</p>

<p>So there is an obvious issue here with how pymssql is hooking up with freetds.</p>

<p>Here is all the output from the log dump in case somebody sees something I am failing to...</p>

<pre><code>&gt;&gt;&gt; import os
&gt;&gt;&gt; os.environ['TDSDUMP'] = 'stdout'
&gt;&gt;&gt; import pymssql  
&gt;&gt;&gt; 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-&gt;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" &lt;-&gt; "UCS-2LE" conversion
iconv.c:395:preparing iconv for "ISO-8859-1" &lt;-&gt; "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-&gt;msgtext = "Adaptive Server connection failed (xxx.database.windows.net:1433)"
dblib.c:8015:dbperror: dblib_err_handler for msgno = 20002; msg-&gt;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 "&lt;stdin&gt;", line 5, in &lt;module&gt;
  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')
</code></pre>

<p>Unfortunately you don't tell us:</p>

<ul>
<li>Which TDS version you wish to actually use.</li>
<li>Which pymssql version you are using and how you've installed it</li>
</ul>

<p>If you are trying to use TDS 8.0 and see pymssql+FreeTDS is using 7.1 then you </p>

<p>a) Don't need to worry as they are the same thing. See <a href="http://www.freetds.org/userguide/choosingtdsprotocol.htm#AEN910" rel="nofollow">http://www.freetds.org/userguide/choosingtdsprotocol.htm#AEN910</a></p>

<p>b) Actually use <code>"7.1"</code> as FreeTDS 1.0 deprecates usage of <code>"8.0"</code>. See <a href="https://github.com/FreeTDS/freetds/blob/1855d0f72aadd998ab133208fcd3f4d168074ab5/NEWS#L6-L7" rel="nofollow">https://github.com/FreeTDS/freetds/blob/1855d0f72aadd998ab133208fcd3f4d168074ab5/NEWS#L6-L7</a></p>

python database sql-azure freetds pymssql