pymssql连接Azure SQL Database
使用pymssql访问Azure SQL Database时遇到“DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (xxxx:1433)\n')”这样的错误，具体错误如下所示：
# python test.py
Traceback (most recent call last):
File "src/pymssql.pyx", line 636, in pymssql.connect
File "src/_mssql.pyx", line 1957, in _mssql.connect
File "src/_mssql.pyx", line 676, in _mssql.MSSQLConnection.__init__
File "src/_mssql.pyx", line 1683, in _mssql.maybe_raise_MSSQLDatabaseException
_mssql.MSSQLDatabaseException: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (xxxx.database.chinacloudapi.cn:1433)\n')
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "test.py", line 7, in <module>
charset="utf8")
File "src/pymssql.pyx", line 642, in pymssql.connect
pymssql.OperationalError: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (xxxx.database.chinacloudapi.cn:1433)\n')
官方文档关于如何使用pymssql连接Azure SQL Database有明确的规定（http://pymssql.org/en/stable/azure.html）：
· Use FreeTDS 0.91 or newer
· Use TDS protocol 7.1 or newer
· Make sure FreeTDS is built with SSL support
· Specify the database name you are connecting to in the database parameter of the relevant connect() call
· IMPORTANT: Do not use username@server.database.windows.net for the user parameter of the relevant connect() call! You must use the shorter username@server form instead!
·
具体翻译如下：
必须使用FreeTDS 0.91 或后续版本
必须是使用TDS protocol 7.1或后续版本
必须确保FreeTDS编译安装时支持SSL
在相关的connect（）调用的数据库参数中指定要连接的数据库名称
重要提示：相关的connect（）调用时，参数不要使用username@server.database.windows，而是应该使用username@server替代，否则会遇到错误（请见下文）
官方文档https://www.freetds.org/userguide/ 有介绍TDS协议版本，有兴趣可以了解一下，简单描述如下所示：
关于FreeTDS的安装，我们可以从FreeTDS的从官方网址下载（FreeTDS的官方网址：https://www.freetds.org/），目前的稳定版本为Stable Release (1.1.15)，下载地址ftp://ftp.freetds.org/pub/freetds/stable/freetds-1.1.15.tar.gz
安装过程：
#tar -xzvf freetds-1.1.15.tar.gz
#cd freetds-1.1.15
# ./configure --prefix=/usr/local --with-openssl=/usr/include/openssl --with-tdsver=7.4
#make
#make install
验证过程：
# tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v1.1.15
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: 7.4
iODBC: no
unixodbc: no
SSPI "trusted" logins: no
Kerberos: no
OpenSSL: yes
GnuTLS: no
MARS: yes
# pip show pymssql
Name: pymssql
Version: 2.1.4
Summary: DB-API interface to Microsoft SQL Server for Python. (new Cython-based version)
Home-page: http://pymssql.org
Author: Damien Churchill
Author-email: damoxc@gmail.com
License: LGPL
Location: /usr/local/lib/python3.6/site-packages
Requires:
Required-by:
然后我测试验证pymssql是否可以访问Azure SQL Database，但是还是继续报这个错误，相当纳闷，然后我用tsql验证了一下，确认FreeTDS是可以访问Azure SQL Database的。如下所示：
#tsql -H eworker.database.chinacloudapi.cn -U EWorkerAdmin -D EWorker -p 1433 -P 5#wJ9p@#T46ncy3Zf
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Setting EWorker as default database in login packet
1> select @@version
2> go
Microsoft SQL Azure (RTM) - 12.0.2000.8
Jul 3 2019 10:02:53
Copyright (C) 2019 Microsoft Corporation
(1 row affected)
1>
瞬时懵逼了，静下心来仔细看了官方文档（这里就要说到通读文档的重要性了，很多时候，我们去看官方文档，都是挑一下重要的看或是敷衍了事的看看）,才发现Linux下的pymssql Wheel软件包捆绑了FreeTDS不支持SSL,而且它是静态的dll,即使我安装了支持SSL的FreeTDS，但是pymssql依然不会用到后续安装的FreeTDS的dll，因此连接不到Azure SQL Database:
The statically-linked FreeTDS version bundled with our official pymssql Linux Wheel package doesn’t have SSL support so it can’t be used to connect to Azure. Also it doesn’t have Kerberos support so it can’t be used to perform domain logins to SQL Server
另外github上面 https://github.com/pymssql/pymssql/issues/399 也有相关问题的较详细的介绍:
From Ramiro on the ML regarding this issue:
For 2.1.2, I made the decision to stop shipping FreeTDS linked in staticall in pymssql and to depend on external FreeTDS build as DLLs instead.
In our docs (http://pymssql.org/en/v2.1.2/freetds.html#windows) we link to binaries of FreeTDS DLLS I maintain (https://github.com/ramiro/freetds/releases).
But there is more: These FreeDTS DLLs also depend on other DLLs: The OpenSSL ones chosen are these maintained at: http://www.npcglib.org/~stathis/blog/precompiled-openssl/.
(I decided to go with these because it's a binary distribution of OpenSSL which includes binaries built with the full gamut of Visual C versions used to build the official Windwos binaries of our supported Python versions [VC of VS 2008 for Python 2.7, VC of VS2010 for Python 3.3 and 3.4 and VC of VS2015 for Python 3.5] and thus won't risk causing strange errors at runtime because of mixed C runtime DLLs. This is also true for the FreeTDS DLLs above).
So, the situacion on Windows has changed like this:
pymlssql 2.1.1 pymssql 2.1.2
+
+------------------------------------------+ +-------------------------------------------+
|
| | | |
|
| pymssql | | pymssql |
|
| | | |
|
| | | |
|
| | +-------> | |
|
| | +-------------------------------------------+
|
| +-----------------------------------+ |
|
| | freetds (statically linked) | | +-------------------------------------------+
|
| | | | | |
|
| | | | | freetds (in DLL form) |
|
| +-----------------------------------+ | | |
+
+------------------------------------------+ +-------------------------------------------+
+-------------------------------------------+
| |
| OpenSSL (in DLL form) |
| |
+-------------------------------------------+
Unfortunately, as the Python package tooling and file format don't allow to ship third party binaries, these DLLs can't be bundled with the pymssql Wheel binary file. And it woldn't be a good idea anyway (being the package responsible of putting extraneous DLL files on the global site-packages dir of the users' Python installation woldn't be something t be proud of.)
Why this? Because SSL is needed to open secure connections to SQL Server. Particularly it's required when one is trying to use pymssql to connect to Azure 'cloud' SQL Server flavor. And I didn't want to include OpenSSL linked statically with the pymssql binaries because we have a somewhat slow release cycle and in case security adivsores/udpates are published for OpenSSL I didn't want to either a) expose users to keep running older, insecure binaries or b) Have to rush to a new release of pymssql simply because a new version of OpenSSL was released to fix security problems.
I'm still not sure this was a good deicision, and we might revisit it in future releases.
An alternative (which has been kown and used by many in prior releases) is to use pymssql binaries maintained (among many other) by Christoph Gohlke at http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
I hope this helps clifying the situation and the reasoning I followed when making these technical decisiones.
pymssql IS a wrapper for FreeTDS: It can't use other drivers/stacks/libraries.
If you are using an official pymsql 2.1.3 wheel downloaded from pypi then you copy of pymssql includes a statically linked cpy of FreeTDS. But Such copy doesn't include the feature to use TLS-protected connection which are a mandatory requirement to connect to Azure.
To be able to do so you will need to build/use a copy of FreeTDS linked against OpenSSL or similar. and then build pymssql against that version of FreeTDS.
This means your setup should heve never worked.
Pleae post support quetions like this to our mailing list, not here. I'm closing this issue.
所以我们卸载pymssql，然后用pip3从源码安装pymmsql，必须先安装Cython，因为pymssql依赖Cython，另外，必须创建libsybdb.so.5的软连接，否则会报错（如下所示）
[root@db-server python]# pip uninstall pymssql
[root@db-server python]# pip3 install --user Cython
[root@db-server python]# pip3 install --user --no-binary pymssql pymssql
[root@db-server python]# python test.py
Traceback (most recent call last):
File "test.py", line 2, in <module>
import pymssql
ImportError: libsybdb.so.5: cannot open shared object file: No such file or directory
[root@db-server ~]# ls /usr/local/lib/libsybdb.so.5
/usr/local/lib/libsybdb.so.5
[root@db-server ~]# ln -s /usr/local/lib/libsybdb.so.5 /usr/lib/libsybdb.so.5
[root@db-server ~]# ldconfig
然后测试验证确认可以连接Azure SQL Database了。问题终于解决。
参考资料:
https://www.freetds.org/userguide/
http://pymssql.org/en/stable/azure.html
https://github.com/pymssql/pymssql/issues/399
