pymssql and Adaptive Server connection failed

Question

When I try to connect into Azure database by Pymssql in python I face this error:

pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (iprice-bi.database.windows.net:1433)\n')

I connect to the database by tsql command:

 tsql -H server -p 1433 -U username -P password

locale is "en_US.UTF-8"

locale charset is "UTF-8" using default charset "UTF-8" 1> SELECT @@version 2> GO

Microsoft SQL Azure (RTM) - 12.0.2000.8 
Mar  1 2016 22:36:40 
Copyright (c) Microsoft Corporation

(1 row affected)

I checked freetds.conf as well to be sure I am using correct version.

[global]
# TDS protocol version
tds version = 7.0
dump file = /tmp/freetds.log
dump file append = yes

results for log file also does not give any clue.

log.c:167:Starting log file for FreeTDS 0.95.87
on 2016-03-25 16:50:51 with debug flags 0x4fff.
dblib.c:1237:tdsdbopen: Calling tds_connect_and_login(0x23f9b00, 0x23fde90)
iconv.c:328:tds_iconv_open(0x23f9b00, 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 23.100.117.95 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 d7 33 00 00            -                        |.3..|

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 02 |........ ........|
0020 bf 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(0x24f4290, 0x23f9b00, 20002, 0)
dblib.c:7925:dbperror(0x23f8fb0, 20002, 0)
dblib.c:7993:dbperror: Calling dblib_err_handler with msgno = 20002;      msg->msgtext = "Adaptive Server connection failed (SERVERNAME:1433)"
dblib.c:8015:dbperror: dblib_err_handler for msgno = 20002; msg->msgtext  = "Adaptive Server connection failed (SERVERNAME: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 "SERVERNAMR:1433"!
dblib.c:1463:dbclose(0x23f8fb0)
dblib.c:243:dblib_del_connection(0x7f066cb036a0, 0x23f9b00)
mem.c:648:tds_free_all_results()
dblib.c:290:dblib_release_tds_ctx(1)
dblib.c:5873:dbfreebuf(0x23f8fb0)
dblib.c:743:dbloginfree(0x24439f0)

Has anybody faced such a problem, or can you give me some suggestions for solving it?

Delilahdelimit

Can you include: your Python code that connects, version of pymssql, version of FreeTDS, version of Python (2.7? 3.5?) - thanks. – Hasty

connection = pymssql.connect('host','username','password', 'db name') – Delilahdelimit

freetds.conf directory: /usr/local/etc MS db-lib source compatibility: no Sybase binary compatibility: no Thread safety: yes iconv library: yes TDS version: 5.0 iODBC: no unixodbc: no SSPI "trusted" logins: no Kerberos: no OpenSSL: no GnuTLS: no – Delilahdelimit

Version: freetds v0.95.87 , I am using Python 2.7 Anaconda 64 bits. – Delilahdelimit

Possible duplicate of How to configure pymssql with SSL support on Ubuntu 16.04 LTS? – Insolate

Answer 1

Can you try this connection string form instead to be explicit? Your log in is being rejected:

login.c:472:login packet rejected

conn = pymssql.connect(
    server="yourhost.example.com",
    port=1433,
    user="your_user",
    password="your_pw",
    database="your_db")

I'm assuming your Azure DB is running on 1433. This is the connection string I use for SQL Server, I've only used pyodbc with Azure in the past. You can also set your TDS Version to be 7.1 instead of 7.0 in your configuration. If that doesn't do the trick, a few more questions (and I'll amend my answer until we figure it out):

• How long in characters are your username and password? Just for testing purposes, try it with a username and password of 10 characters if they're really long.
• Are you using a SQL Server login or a Windows auth login?

Hasty

Answer 2

Per my experience, I think you can try to modify the tds version with 7.3 that be for MS SQL Server 2008+. The tds version 7.0 is too low for Azure SQL Database.

Please see my answer for the other SO thread pymssql: Connection to the database only works sometimes.

Neighborly

Answer 3

Looks like no one here has answered it in this way:

Upgrade pymssql. Versions from 2.1 doesn't work with older versions of SQL Server. We upgraded from 2012 to 2019 and got the errors from SQL Server:

DESCRIPTION:    The prelogin packet used to open the 
connection is structurally invalid; the connection has been closed.
Please contact the vendor of the client library. [CLIENT: your-ip]

Which manifest on the python side as that exact error "Adaptive Server connection failed"

Upgrade pymssql at or above 2.2.2 and it will fix your issue.

Well

Answer 4

In my case it was simply an authentication issue. I think the Nagios does not support Windows Authentication Mode. Once I enabled SQL Server authentication mode, the message disappeared, and some critical errors were displayed. After some cycles these cleared out and counters were populated with sensible numbers.

Tatary