连接到本地 SQL Server 实例 [英] Connecting to local SQL Server instance

问题描述

我正在尝试连接到在我的机器上运行的本地 SQL Server 实例.我可以使用此代码从我们的服务器连接到本地实例，但它在我的本地计算机上失败.

I'm attempting to connect to a local instance of SQL Server running on my machine. I am able to connect to a local instance with this code from our server, but it fails on my local machine.

我在 SQL Server 配置中启用了命名管道和所有 ip.

I've enabled named pipes and all the ips in the SQL Server configuration.

我使用的代码如下:

from pymssql import connect
server = r'.\SQLEXPRESS2014' # I've also tried MORGANT-PC\SQLEXPRESS and SQLEXPRESS2014
username = 'MyUserName'
password = 'MyPassword'
master_database_name = 'SuperSecretDatabase'
port = 5000
server_args = {'host': server, 'user': username, 'password': password,
               'database': master_database_name, 'port': port} # I've tried having the first key be both host and server, because pymssql's docs are unclear on the difference.
master_database = connect(**server_args)

如果我使用实例名称，则会收到此错误:

If I use the instance name, I get this error:

pymssql.InterfaceError: Connection to the database failed for an unknown reason.

我将端口设置为 5000，以便我可以尝试使用

I set the port to 5000 so that I could try connecting to it with

server = 127.0.0.1
port = 5000

失败，错误消息略有不同:

which fails with the slightly different error message:

pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist\nNet-Lib error during Unknown error (10035)\n')

我在这里阅读了很多关于 SO 的答案，其中大部分似乎表明这是 FreeTDS 的问题，但我使用的是 Windows 8.1，所以我没有 FreeTDS.

I've read a bunch of answers here on SO, and most of them seem to indicate it's an issue with FreeTDS, but I'm on Windows 8.1, so I don't have FreeTDS.

我尝试将 sqlcmd 与主机\实例连接名称，效果很好.它也适用于 SSMS.

I've tried connecting with sqlcmd with the host\instance name and that works fine. It also works in SSMS.

我已经尝试将 .\SQLEXPRESS2014 传递给 pymssql.connect() 中的 host 和 server 参数code> 并且它们都失败并出现上述相同的错误.

I've tried passing .\SQLEXPRESS2014 to both the host and server parameter in pymssql.connect() and they both fail with the same aforementioned error.

我曾短暂尝试过使用 adodbapi，但我收到了完全相同的错误消息.

I briefly tried using adodbapi, but I'm getting exactly the same error messages.

推荐答案

最终的解决方案是将事情结合起来.

The solution ended up being a combination of things.

1. 我需要禁用除 127.0.0.1 以外的所有 IP.

2. 我需要使用以下文本创建 C:\freetds.conf:

1. I needed to disable all IPs other than 127.0.0.1.

2. I needed to create C:\freetds.conf with the following text:

<代码>[全局]端口 = 1433tds 版本 = 7.0

我需要将我的 SQL 实例登录的帐户更改为 LocalSystem.

I needed to change the account my SQL instance logs in with to LocalSystem.

这篇关于连接到本地 SQL Server 实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！