无法通过 pymssql 连接到 SQL Server

发布于 2024-10-02 07:54:41 字数 785 浏览 5 评论 0原文

我正在尝试通过 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.

我尝试过的事情：

将 SQL Server 和浏览器设置为作为网络服务器运行。
设置用户“www”。我还在 SQL Studio 本地测试了该用户。
关闭 Windows 防火墙（当然是暂时的）。

我错过了一些东西——我只是不知道它是什么。我尝试了 Windows 上的所有无限菜单选项，但均无济于事。我注意到的一件事是，如果 Windows 防火墙打开（我为 SQL Server 设置了例外），python 会暂停很长时间，然后给出错误。如果防火墙关闭，错误就会立即出现。

我可以在 SQL Server 中查看任何日志吗？

原文

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

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).

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.

Are there any logs I can look at in SQL Server?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

未央 2024-10-09 07:54:41

知道了！我认为问题的根源在于没有给予免费 TDS 所需的关注。免费 TDS 显然是 pymssql 背后的驱动程序，并提供与其他数据库的连接 - SQL Server 就是其中之一。

freetds.conf 文件位于我的系统 (Mac Book Pro) 上的 /usr/local/etc 中。

该文件包含安装时的默认设置。然而，我之前添加了一个定义，以便我可以连接，但忘记了它，不幸的是没有做笔记。

无论如何，这是我附加到 freetds.conf 的一个示例：

[SomeDB]
    host = 192.168.1.102
    port = 1219
    tds version = 7.0

然而，令人费解的是我将端口设置为 1219。我在 SQL Studio 中将其手动设置为 1433。另外，我使用的是 TDS 版本 0.82，所以我不知道 7.0 如何适应。

接下来，我使用“tsql”测试连接，如下所示：

tsql -S SomeDB -U www

输入密码并获取允许 SQL 查询的命令行。

接下来，我使用 pymssql 测试连接，如下所示：

db = pymssql.connect(host='SomeDB',user='www',password='cylon',database='TestDB')

如您所见，我需要使用 freetds.conf 文件中的主机名，而不是直接使用 IP。然后，我使用附加的 Python 代码测试了一个简单的查询，以确保我可以从数据库中读取数据。

我希望这对将来的其他人有帮助。

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.

The freetds.conf file is located in /usr/local/etc on my system (Mac Book Pro).

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:

[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.

Next, I tested connectivity using 'tsql' as follows:

tsql -S SomeDB -U www

I enter the password and get a command-line which allows for SQL queries.

Next, I tested connecting using pymssql as follows:

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.

I hope this helps someone else in the future.

回复收藏 0 原文