Cannot connect to SQL Server database using pymssql but can connect using underlying freetds tsql

Question

I have no idea why I am getting this error and cannot find any solutions for it. I can connect to a SQL Server database using freetds tsql but I keep getting an error when connecting using pymssql.connect.

The specific error is:

pymssql.OperationalError: (18456, "Login failed for user 'xxx'.DB-Lib error message 18456, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed\n")

I have the configuration set for freetds as:

[custom_config]
    host = myhost
    port = 1433
    tds version = 7.0
    encryption = request
    dump file = /tmp/freetds.log

running:

tsql -S custom_config -U tsmv -P xxx

returns:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

which allows me to query the database.

However, running:

python
>> import pymssql
>> pymssql.connect(server='custom_config', user='user', password='xxx',    database='database')

raises the above error.

I am using Linux CentOS, python 2.6.6, freetds 0.92 dev (I have tried other versions compiling with tdsver=7.0).

The freetds log is:

log.c:196:Starting log file for FreeTDS 0.92
on 2012-04-12 10:39:15 with debug flags 0x4fff.
iconv.c:330:tds_iconv_open(0x1391b70, ISO-8859-1)
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:349:setting up conversions for client charset "ISO-8859-1"
iconv.c:351:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:394:tds_iconv_open: done
net.c:205:Connecting to xx.x.x.xxx port 1433 (TDS version 7.1)
net.c:270:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:310:tds_open_socket() succeeded
util.c:156:Changed query state from DEAD to IDLE
net.c:741: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 c7 39 00 00            -                        |.9..|

net.c:555:Received header
0000 04 01 00 25 00 00 01 00-                        |...%....|

net.c:609: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 0a 00 0f |........ ........|
0020 a0 00 00 02 00         -                        |.....|

login.c:1057:detected flag 2
login.c:782:quietly sending TDS 7+ login packet
token.c:328:tds_process_login_tokens()
net.c:555:Received header
0000 04 01 00 72 00 51 01 00-                        |...r.Q..|

net.c:609:Received packet
0000 04 01 00 72 00 51 01 00-aa 5e 00 18 48 00 00 01 |...r.Q.. .^..H...|
0010 0e 1d 00 4c 00 6f 00 67-00 69 00 6e 00 20 00 66 |...L.o.g .i.n. .f|
0020 00 61 00 69 00 6c 00 65-00 64 00 20 00 66 00 6f |.a.i.l.e .d. .f.o|
0030 00 72 00 20 00 75 00 73-00 65 00 72 00 20 00 27 |.r. .u.s .e.r. .'|
0040 00 74 00 73 00 6d 00 76-00 27 00 2e 00 0c 4d 00 |.t.s.m.v .'....M.|
0050 43 00 53 00 2d 00 44 00-41 00 54 00 41 00 42 00 |C.S.-.D. A.T.A.B.|
0060 41 00 53 00 45 00 00 01-00 fd 02 00 00 00 00 00 |A.S.E... ........|
0070 00 00                  -                        |..|

token.c:337:looking for login token, got  aa(ERROR)
token.c:122:tds_process_default_tokens() marker is aa(ERROR)
token.c:2588:tds_process_msg() reading message 18456 from server
token.c:2661:tds_process_msg() calling client msg handler
dbutil.c:85:_dblib_handle_info_message(0x14e2e30, 0x1391b70, 0x7fff8b047e40)
dbutil.c:86:msgno 18456: "Login failed for user 'xxx'."
token.c:2674:tds_process_msg() returning TDS_SUCCEED
token.c:337:looking for login token, got  fd(DONE)
token.c:122:tds_process_default_tokens() marker is fd(DONE)
token.c:2339:tds_process_end: more_results = 0
    was_cancelled = 0
    error = 1
    done_count_valid = 0
token.c:2355:tds_process_end() state set to TDS_IDLE
token.c:2370:                rows_affected = 0
token.c:438:tds_process_login_tokens() returning TDS_FAIL
login.c:466:login packet accepted
util.c:156:Changed query state from IDLE to DEAD
util.c:331:tdserror(0x14e2e30, 0x1391b70, 20002, 0)
dblib.c:7929:dbperror(0x1383c70, 20002, 0)
dblib.c:7981:20002: "Adaptive Server connection failed"
dblib.c:8002:"Adaptive Server connection failed", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
dblib.c:1443:dbclose(0x1383c70)
dblib.c:258:dblib_del_connection(0x7fa462faf540, 0x1391b70)
mem.c:615:tds_free_all_results()
dblib.c:305:dblib_release_tds_ctx(1)
dblib.c:5882:dbfreebuf(0x1383c70)
dblib.c:739:dbloginfree(0x1533a40)

I am completely lost as to why this is not working. Any help would be much appreciated.

Selfseeking

You may want to be using servername instead of server. Also, I ran into issues (at least with pyodbc) where a connection string didn't seem to behave exactly the same as passing kwargs (may be my mistake though...). You could also try using pyodbc instead of pymssql (no experience with pymssql here). – Overshadow

Thanks Derek, I've taken your advice, bailed out from using pymssql and started using pyodbc with Microsoft's Linux driver. All works fine now (although I know this doesn't answer my original question). – Selfseeking

Answer 1

The "Adaptive Server connection failed" seems to be a fairly generic message, but here are some things to try.

1. This mailing list thread (http://lists.ibiblio.org/pipermail/freetds/2010q3/026060.html) says that using the incorrect TDS protocol results in an "Adaptive Server connection failed" message. That doesn't seem to be the case in chewynougat's log, but perhaps it helps others.

2. This FAQ gives a lot of steps to try: https://github.com/pymssql/pymssql/blob/87f4383ec153962b7ca7e63a05042d3f09005178/docs/faq.rst,

One is attempting to test the tds connection via tsql -H, which bypasses reading from the conf and only reads in the passed in values. Given that the conf above holds both the port and protocol version, it might be worthwhile to check that along with tsql -C to see if there are adjustments needed.

3. Also, at the bottom of the FAQ, it states that

   real "Login incorrect" messages has code=18456 and severity=14

That is the error message being sent, so perhaps try Login Auditing (http://msdn.microsoft.com/en-us/library/ms175850.aspx) to see if pymssql is passing your credentials in properly.

4. That same section talks about different character sets messing up the mssql.connect, so also perhaps try a basic password (i.e., ASCII 65-90) to ensure that nothing is lost in translation. It looks like Aki works with Japanese, so perhaps this is a cause as well.

Afloat

Answer 2

I faced the same problem when I installed via pip install pymssql because this installed a pre-built OS-specific binary wheel which does not support encryption.

This, even though I had installed a specific version of freetds, that I had expected would be used. Installing instead with pip install --no-binary pymssql pymssql gave an installation that works.

If you want to encrypt your database connections you will need to first build/install freetds, and then install pymssql as described here.

In this event I highly recommend ensuring your freetds.conf file specifies 'require' as opposed to 'request', to avoid silently falling back to unencrypted traffic. Using tcpdump -A and grepping for SQL keywords can help determining whether traffic is really encrypted.

Playback

Answer 3

I faced the same problem. Fortunately, I found out what is wrong. I had two different versions of FreeTDS on my machine. I installed one of them(v 0.91)by:

   sudo apt-get install freetds-dev

later I found out it is not the last version and I downloaded the tar file of Freetds from freetds.org. When I ran tsql -C. it showed the correct path and I manipulated that freetds.conf correctly. With changing environment variable (http://www.freetds.org/userguide/envvar.htm), I could connect to database. However, each time I tried to connect by Pymssql I got error.

Finally, I looked at the log file and I figured out that Python is using the old version (v 0.91) but my last one was version 0.95.

>>> import os
>>> os.environ['TDSDUMP'] = 'stdout'
>>>
>>> import pymssql
>>> conn = pymssql.connect(server="sqlserverhost")

So, I deleted the version 0.91 with:

sudo apt-get purge freetds-common

and pymssql connected to the right version with correct configuration.

It may help you as well.

Ylem

Answer 4

try follow the new notes in the pymssql documentation: azure need to take care of the user part. it's so weird, but worked. ms sql warehouse makes work on linux/mac so difficult..

http://pymssql.org/en/latest/azure.html

IMPORTANT: Do not use [email protected] for the user parameter of the relevant connect() call! You must use the shorter username@server form instead!

Panorama