Python pymssql 和 Adaptive Server 连接失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36217327/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pymssql and Adaptive Server connection failed
提问by Moohebat
When I try to connect into Azure database by Pymssql in python I face this error:
当我尝试通过 python 中的 Pymssql 连接到 Azure 数据库时，我遇到了这个错误：
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 命令连接到数据库：
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
语言环境字符集是“UTF-8”，使用默认字符集“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.
我也检查了 freetds.conf 以确保我使用的是正确的版本。
[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)
does Anybody face such problem or can give me some suggestions for solve it?!
有没有人遇到过这样的问题，或者可以给我一些解决的建议？！
回答by FlipperPA
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):
我假设您的 Azure DB 运行在 1433 上。这是我用于 SQL Server 的连接字符串，我过去只将 pyodbc 与 Azure 一起使用。您还可以在配置中将 TDS 版本设置为 7.1 而不是 7.0。如果那还不行，再问几个问题（我会修改我的答案，直到我们弄清楚为止）：
- 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?
- 您的用户名和密码的字符长度是多少？仅出于测试目的，如果用户名和密码很长，请尝试使用 10 个字符的用户名和密码。
- 您使用的是 SQL Server 登录名还是 Windows 身份验证登录名？
回答by Peter Pan
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.
根据我的经验，我认为您可以尝试使用适用于 MS SQL Server 2008+ 的 7.3 修改 tds 版本。tds 7.0 版本对于 Azure SQL 数据库来说太低了。
Please see my answer for the other SO thread pymssql: Connection to the database only works sometimes.
请参阅我对另一个 SO 线程pymssql 的回答：与数据库的连接仅有时有效。
回答by Emilio
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.
就我而言，这只是一个身份验证问题。我认为 Nagios 不支持 Windows 身份验证模式。启用 SQL Server 身份验证模式后，消息消失，并显示一些严重错误。经过一些周期后，这些被清除，计数器填充了合理的数字。
