SQL Server 不存在或访问被拒绝

SQL Server does not exist or access denied

提问by Darren Cook

We are currently migrating servers (2005 to 2008) and our host backed up the SQL database and transferred it to the new server.

我们目前正在迁移服务器（2005 年到 2008 年），我们的主机备份了 SQL 数据库并将其转移到新服务器。

I have transferred the website files and changed the IP address in the connection string and the global.asa file.

我已经传输了网站文件并更改了连接字符串和 global.asa 文件中的 IP 地址。

So technically speaking we have a mirror image on the new server, but when I try to launch a page that connects to the SQL I get the following error.

所以从技术上讲，我们在新服务器上有一个镜像，但是当我尝试启动一个连接到 SQL 的页面时，我收到以下错误。

SQL_Server_does_not_exist_or_access_denied.

SQL_Server_does_not_exist_or_access_denied。

回答by Nitin P

Check your TCP/IP Port is enabled or not

检查您的 TCP/IP 端口是否启用

To check it:

要检查它：

-- Open SQL Server Configuration Managerfrom start program.
-- Expand SQL Server Network Configuration
-- Click on Protocols for XXXX
-- Right Click on TCP/IPand open properties
   - Enable TCP/IP
   - In IP Address Tab, Set Port 1433 in the last option (IPAll)

--从启动程序打开SQL Server 配置管理器。
-- 展开SQL Server 网络配置
-- 单击XXXX 的协议
-- 右键单击TCP/IP并打开属性
   - 启用 TCP/IP
   - 在 IP 地址选项卡中，在最后一个选项 (IPAll) 中设置端口 1433

回答by Tahbaza

The error message in this case is very likely correct. First, verify that the network path from you to the new server works and you can connect with the specified userid/pwd.

这种情况下的错误消息很可能是正确的。首先，验证从您到新服务器的网络路径是否有效，并且您可以使用指定的用户 ID/密码进行连接。

1) Open Management Studio and attempt to connect to the SQL Server instance by ip address. (By the way, you will probably need to use a SQL Server userid/pwd if you aren't already since you're on an Internet host and there is very likely no AD context for Windows auth.)

1) 打开 Management Studio 并尝试通过 IP 地址连接到 SQL Server 实例。（顺便说一句，如果您还没有使用 SQL Server 用户 ID/密码，因为您在 Internet 主机上并且很可能没有 Windows 身份验证的 AD 上下文，您可能需要使用它。）

2) If you can indeed connect to the instance and your database using Management Studio then you've probably missed a connection string somewhere. Try to maintain only 1 location for said connection string, usually in a config file. Examine the error line reported in detail to discover which connectionstring is being used and to verify that it reflects the new server ip.

2) 如果您确实可以使用 Management Studio 连接到实例和数据库，那么您可能在某处错过了连接字符串。尝试为所述连接字符串仅保留 1 个位置，通常在配置文件中。检查详细报告的错误行以发现正在使用哪个连接字符串并验证它是否反映了新的服务器 IP。

回答by user836107

This same error also occurs when a cloned server's IP is not configured correctly in SQL Config --> TCP/IP connection.

如果在 SQL Config --> TCP/IP 连接中未正确配置克隆服务器的 IP，也会发生同样的错误。

With misconfigured IP in SQL TCP/IP config does let you:

在 SQL TCP/IP 配置中使用错误配置的 IP 确实可以让您：

1. connect from client machine using server name using ODBC connection
2. connect using TELNET with 1433 port number with sever name

1. 使用 ODBC 连接使用服务器名称从客户端计算机连接
2. 使用带有 1433 端口号和服务器名称的 TELNET 连接

This happens when the server is cloned from another SQL server which carries the SQL IP config from the primary server as residue.

当服务器从另一个 SQL 服务器克隆时会发生这种情况，该服务器将主服务器的 SQL IP 配置作为残留物。

But client applications fail to connect using connection strings though using the server name with the following message:

但是客户端应用程序无法使用连接字符串进行连接，尽管使用服务器名称并显示以下消息：

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

Do the following to verify on the SQL Server:

执行以下操作以在 SQL Server 上进行验证：

1. Note the IP of the server itself (probably admins updated the machine's IP after cloning the box) by doing IPCONFIG
2. Open SQL Server Configuration Manager
3. Expand SQL Server Network Configuration
4. Select Protocols for MSSQLSERVER
5. Double click TCP/IP on the right
6. Switch to IP Addresses tab
7. Scroll down to IPv4 section to verify if it is the same as the IP address of the machine itself. Fix the IP, if it is not. If it is correct this is not the issue.

1. 通过执行 IPCONFIG 注意服务器本身的 IP（可能是管理员在克隆盒子后更新了机器的 IP）
2. 打开 SQL Server 配置管理器
3. 展开 SQL Server 网络配置
4. 为 MSSQLSERVER 选择协议
5. 双击右侧的 TCP/IP
6. 切换到 IP 地址选项卡
7. 向下滚动到 IPv4 部分以验证它是否与机器本身的 IP 地址相同。如果没有，请修复 IP。如果它是正确的，这不是问题。

回答by Bacon Bits

Are the credentials for your database configured to use SQL users and logins instead of Windows users and logins?

您的数据库的凭据是否配置为使用 SQL 用户和登录名而不是 Windows 用户和登录名？

If so, you may need to remap the SQL users in the database to the SQL logins on the server. To scan to see if you have this problem, you can use EXEC sp_change_users_login 'Report'. The 'Auto_Fix'option will automatically correct issues the system finds. You can also run ALTER USER $User WITH LOGIN $Loginto manually correct each mapping individually.

如果是这样，您可能需要将数据库中的 SQL 用户重新映射到服务器上的 SQL 登录名。要扫描以查看是否有此问题，您可以使用EXEC sp_change_users_login 'Report'. 该'Auto_Fix'选项将自动更正系统发现的问题。您还可以运行ALTER USER $User WITH LOGIN $Login以单独手动更正每个映射。

http://technet.microsoft.com/en-us/library/ms174378.aspx

http://technet.microsoft.com/en-us/library/ms174378.aspx

The issue is caused because the SIDs generated for the SQL user in the database on the old server don't match the SIDs for the SQL logins on the new database. The stored procedure corrects the discrepancy.

导致此问题的原因是旧服务器上数据库中为 SQL 用户生成的 SID 与新数据库上 SQL 登录名的 SID 不匹配。存储过程纠正了差异。

回答by Mohammad Kanan

I experienced this problem when connecting from Qt to Microsoft SQL Server 2014 running also on my computer ...

从 Qt 连接到也在我的计算机上运行的 Microsoft SQL Server 2014 时，我遇到了这个问题......

I used the following QODBC connection string:

我使用了以下 QODBC 连接字符串：

db.setDatabaseName("Driver={SQL Server};Server=DESKTOP-F6T7JPD\sql_Instance_Name;Database=master;Uid=sa;Pwd=your_pwd;");

I only suffered 2 issues:

我只遇到了两个问题：

1. identifying what to use for Server, this must be the Server Name as configured on SQL server instance, this can be found by opening "MicrosoftSql2014 management Studio", then by looking at properties I found the name (DESKTOP-F6T7JPD\sql_Instance_Name)

2. the second issue actually is that this name must be modified by adding another back slash \between my PC name and the instance name so the name found on management studio will be like: PC_Name\Sql_Instance. Note the "\", ... this must be put as Pc_Name\\Sql_Instance.
   Note the \\between PC name and Instance Name!

1. 确定用于服务器的内容，这必须是在 SQL 服务器实例上配置的服务器名称，这可以通过打开“MicrosoftSql2014 management Studio”找到，然后通过查看属性我找到了名称（DESKTOP-F6T7JPD\sql_Instance_Name）

2. 第二个问题实际上是必须通过\在我的 PC 名称和实例名称之间添加另一个反斜杠来修改此名称，以便在 management studio 上找到的名称将类似于：PC_Name\Sql_Instance。注意“\”，...这必须放在Pc_Name\\Sql_Instance.
   注意\\PC 名称和实例名称之间的区别！

回答by veeTrain

I experienced this issue as well when setting up an installation on a new computer.

在新计算机上设置安装时，我也遇到了这个问题。

The connection problem I experienced was due to a firewall setting on the server that hosts MSSQL. The setting gave individual IP addresses access to it which 'works' in our environment since IP addresses are described by our administrator as 'sticky'. Once the Firewall access was granted to my new machine's IP address, the PHP odbc_connect(...)statement I was running in my local WAMP setup completed immediately.

我遇到的连接问题是由于托管 MSSQL 的服务器上的防火墙设置。该设置允许单个 IP 地址访问它，这在我们的环境中“有效”，因为我们的管理员将 IP 地址描述为“粘性”。一旦防火墙访问权限被授予我的新机器的 IP 地址，odbc_connect(...)我在本地 WAMP 设置中运行的 PHP语句立即完成。

As with many problems, there is probably more than one way to run into them, but I wanted to be as explicit as possible about how I experienced it.

与许多问题一样，遇到它们的方法可能不止一种，但我想尽可能明确地说明我的经历。

This probably would have been a little more clear if I had my own installation of MSSQL management studio on my PC but I do not.

如果我在我的 PC 上安装了自己的 MSSQL 管理工作室，这可能会更清楚一些，但我没有。

Of the Inbound rules on the server housing MSSQL, I found a rule referring to the MSSQL instance and sure enough found the IP address of my old machine there underneath the properties tab "Scope" as a "Remote IP address". Here's to hoping I remember this whenever I get my next machine! (The decommissioned PC's IP has been removed)

在包含 MSSQL 的服务器上的入站规则中，我发现了一个引用 MSSQL 实例的规则，并且确实在属性选项卡“范围”下找到了我旧机器的 IP 地址作为“远程 IP 地址”。希望我每次拿到下一台机器时都能记住这一点！（退役PC的IP已被删除）

@Darren, if you could leave a comment under the answer you accepted, it might be nice to know which of @Tahbaza's suggestions led you to your resolution.

@Darren，如果你能在你接受的答案下发表评论，很高兴知道@Tahbaza 的哪些建议让你做出了决定。