客户端 pyodbc 错误:“服务器不存在或访问被拒绝." [英] Client-side pyodbc error: "Server does not exist or access denied."

问题描述

我有一个 Python 应用程序，旨在使用 pyodbc 从远程数据库服务器中提取数据，然后在电子表格中组织和显示数据.几个月以来，它一直运行良好，我部门的多名同事通过共享网络文件夹使用它.

I have a python application designed to pull data from a remote database server using pyodbc, then organize and display the data in a spreadsheet. I've had it working fine for several months now, with multiple coworkers in my department using it through a shared network folder.

我的连接:

pyodbc.connect('DRIVER={SQL Server};
                SERVER=<myServer_name>;
                DATABASE=<myDB_name>;
                UID=personsUser;
                PWD=personsPassword')

我们同一网络中的另一位员工最近尝试使用该程序并收到此错误:

A different employee within our same network recently tried to use the program and got this error:

pyodbc.Error: ('08001','[08001][Microsoft][ODBC SQL Server Driver]
[DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect)')

对我来说这看起来像是一个简单的权限问题，所以为了确认我用自己的硬编码替换了用户 ID 和密码，但它给出了同样的错误.此外，同一名员工可以通过 SQL Server Management Studio 登录并执行查询而不会出现问题.

It looked like a simple permissions issue to me, so to confirm I replaced the userID and password with my own hardcoded in, but it gave the same error. Furthermore the same employee can log in and execute queries through SQL Server Management Studio without issue.

由于我们部门的其他人仍然可以正常使用该应用程序，我知道这一定是客户端问题，但我无法查明问题所在.任何输入将不胜感激，谢谢！

Since everyone else in our department can still use the application fine, I know it must be a client-side issue, but I just can't pinpoint the problem. Any input would be greatly appreciated, Thanks!

更新:

根据下面的 flipperPA 的回答，我更新了我的连接字符串以包含端口:

Per flipperPA's answer below, I updated my connection string to include the port:

con = pyodbc.connect('''DRIVER={SQL Server};
                        SERVER=<myServer_name>;
                        PORT=1433;
                        DATABASE=<myDB_name>;
                        UID=personsUser;
                        PWD=personsPassword;''')

不幸的是，我们仍然遇到了同样的错误.

Unfortunately we still got the same error.

他在 HP 机器上运行 32 位 Windows 7，与小组其他成员的设置相同，因此这不应该是操作系统级别的问题.

He is running 32-bit Windows 7 on an HP machine, the same setup as the rest of the group so it shouldn't to be an os-level issue.

他确实在同一台机器上运行 SSMS，但我通过 telnet 检查进行了确认 - 没有问题.

He does operate SSMS on the same machine, but I ran through the telnet check just be sure - no issue there.

我自学了 pyodbc API 和基本 SQL，但我对数据库和远程连接的基本概念还比较陌生.你能再解释一下 TDS 驱动程序吗?

I've taught myself the pyodbc API and basic SQL, but I'm still relatively new to the underlying concepts of databases and remote connections. Could you explain the TDS driver a little more?

推荐答案

在与一位知识渊博的朋友交谈后，我终于找到了我的问题！

After talking with a knowledgeable friend I was finally able to figure out my issue!

出于某种原因，用户的系统被配置为使用命名管道连接，但我连接的服务器仅启用了 TCP/IP 协议.解决方案是通过在服务器名称前面添加tcp:"来强制应用程序使用 TCP/IP.

For some reason, the user's system was configured to connect using named pipes, but the server I was connecting to only had TCP/IP protocol enabled. The solution was to force the application to use TCP/IP by adding "tcp:" to the front of the server name.

固定连接字符串:

pyodbc.connect('''DRIVER={SQL Server};
                  SERVER=tcp:<myServer_name>;
                  PORT=1433;
                  DATABASE=<myDB_name>;
                  UID=personsUser;
                  PWD=personsPassword
               ''')

这篇关于客户端 pyodbc 错误:“服务器不存在或访问被拒绝."的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！