Python 无法通过 pymssql 连接到 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4150524/
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
Unable to connect to SQL Server via pymssql
提问by Christopher
I am attempting to connect to SQL Server running on Windows XP system from a *nix system on a local server via pymssql. However, the connection fails as shown below
我正在尝试通过 pymssql 从本地服务器上的 *nix 系统连接到在 Windows XP 系统上运行的 SQL Server。但是连接失败，如下图
db = pymssql.connect(host='192.168.1.102',user='www',password='test',database='TestDB')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "pymssql.pyx", line 457, in pymssql.connect (pymssql.c:6041)
raise InterfaceError(e[0])
pymssql.InterfaceError: Connection to the database failed for an unknown reason.
Things I've tried:
我尝试过的事情：
- Set SQL Server and browser to run as a network server.
- Setup a user 'www'. I also tested this user locally in SQL Studio.
- Turned off Windows firewall (temporarily of course).
- 将 SQL Server 和浏览器设置为作为网络服务器运行。
- 设置用户“www”。我还在 SQL Studio 中本地测试了这个用户。
- 关闭 Windows 防火墙（当然是暂时的）。
I am missing SOMETHING - I just don't know what it is. I tried all of the infinite menu options on Windows to no avail. One thing I noticed is that if the Windows Firewall is on (I setup an exception for SQL Server) python pauses a long time and then gives the error. If the firewall is off the error is instant.
我错过了一些东西 - 我只是不知道它是什么。我尝试了 Windows 上的所有无限菜单选项都无济于事。我注意到的一件事是，如果 Windows 防火墙打开（我为 SQL Server 设置了一个例外），python 会暂停很长时间然后给出错误。如果防火墙关闭，错误是即时的。
Are there any logs I can look at in SQL Server?
我可以在 SQL Server 中查看任何日志吗？
采纳答案by Christopher
Got it! I think the source of the problem was not giving Free TDS the attention it needs. Free TDS is apparently the driver behind pymssql and provides for connectivity to other databases - SQL Server being one of them.
知道了！我认为问题的根源在于没有给予 Free TDS 所需的关注。免费 TDS 显然是 pymssql 背后的驱动程序，并提供与其他数据库的连接——SQL Server 就是其中之一。
The freetds.conf file is located in /usr/local/etc on my system (Mac Book Pro).
freetds.conf 文件位于我的系统 (Mac Book Pro) 上的 /usr/local/etc 中。
This file contains the defaults from the install. However, I had previously added a definition so that I could connect but forgot about it and unfortunately did not take notes on it.
此文件包含安装的默认值。但是，我之前添加了一个定义，以便我可以连接但忘记了它，不幸的是没有做笔记。
Anyway, here is an example of what I appended to freetds.conf:
无论如何，这是我附加到 freetds.conf 的示例：
[SomeDB]
host = 192.168.1.102
port = 1219
tds version = 7.0
However, what is puzzling is that I set the port to 1219. I had it set manually to 1433 in SQL Studio. Also, I am using TDS version 0.82 so I don't know how 7.0 fits in.
然而，令人费解的是，我将端口设置为 1219。我在 SQL Studio 中将其手动设置为 1433。另外，我使用的是 TDS 0.82 版，所以我不知道 7.0 是如何适应的。
Next, I tested connectivity using 'tsql' as follows:
接下来，我使用 'tsql' 测试了连接性，如下所示：
tsql -S SomeDB -U www
I enter the password and get a command-line which allows for SQL queries.
我输入密码并获得一个允许 SQL 查询的命令行。
Next, I tested connecting using pymssql as follows:
接下来，我使用 pymssql 测试连接如下：
db = pymssql.connect(host='SomeDB',user='www',password='cylon',database='TestDB')
As you can see, I needed to use the host name from the freetds.conf file and NOT the IP directly. I then tested a simple query with additional python code to insure I could read from the database.
如您所见，我需要使用 freetds.conf 文件中的主机名，而不是直接使用 IP。然后我用额外的 python 代码测试了一个简单的查询，以确保我可以从数据库中读取。
I hope this helps someone else in the future.
我希望这会在将来对其他人有所帮助。
回答by mo.
is it a windows machine u working on? specify the port 1433. it seems to be a bug in the mssql client api, which tries to use Namedpipes instead of TCP/IP.
它是您正在使用的 Windows 机器吗？指定端口 1433。这似乎是 mssql 客户端 api 中的一个错误，它试图使用 Namedpipes 而不是 TCP/IP。
回答by notatoad
It looks like you've got this solved, but for anybody else from google that lands here: check to make sure mixed-mode authorization is turned on on your MS SQL Server. It defaults to only allowing Windows authorization, and that will cause this error in pymssql.
看起来你已经解决了这个问题，但对于来自谷歌的其他人来说：检查以确保在你的 MS SQL Server 上打开混合模式授权。它默认只允许 Windows 授权，这将导致pymssql.
