When connecting to SQL Server 2005, this failure may be caused by the fact that under the defa

最新推荐文章于 2022-02-18 15:02:55 发布

张扬的狄仁杰

最新推荐文章于 2022-02-18 15:02:55 发布

阅读量2.4k

点赞数

文章标签： sql server sql server 服务器 browser button

本文链接：https://blog.csdn.net/lovemay520/article/details/2726294

版权

当连接到微软SQL服务器2005年数据库事例，包括SQL服务器2005明确时，开发商和企业版，为认证或访问和检索和更新数据，以下错误信息也许出现。问题发生连接起源于远程计算机或在SQL服务器地方计算机的没有问题，并且可以由所有节目造成，例如SQLCMD公共事业，用于连接到SQL服务器。

Sqlcmd ：错误：微软SQL当地客户：错误生成了，当建立与服务器时的连接。当连接到SQL服务器2005年时，这失败也许由在缺省设置SQL服务器之下不允许遥远的连接的事实造成。

或者，

错误生成了，当建立与服务器时的连接。当连接到SQL服务器2005年时，这失败也许由在缺省设置SQL服务器之下不允许遥远的连接的事实造成。 (提供者：命名导管提供者，错误： 40 -不可能打开与SQL服务器) (微软SQL服务器，错误的连接： 2)

这个问题也许发生，当2005年没有配置SQL服务器接受接踵而来的地方和遥远的连接时，默认情况下在SQL服务器2005明确编辑， SQL服务器2005年开发商编辑并且SQL服务器2005年企业版失去能力。要解决连接禁止的问题， SQL服务器2005需要配置允许接踵而来的地方和遥远的连接。

首先，保证2005年在数据库服务器事例适当地配置SQL服务器允许传入连接，使能和转动地方和遥远连接设置。

点击开关，然后去节目或所有节目，然后选择 微软SQL服务器2005年由跟随 配置工具. 点击并且跑 SQL服务器表面配置.
在“SQL服务器2005表面配置”页，点击 表面配置为服务和连接.
在“表面配置为服务和连接”呼叫，扩展 数据库引擎 树，点击 遥远的连接.
精选 地方和遥远的连接或者 仅本地连接 哪可适用，只有当没有远程系统设法连接到SQL服务器，有用，当您设法连接和证实用服务器在安装以后。
选择适当的协议使能对地方并且/或者遥远的连接环境。 To ensure maximum compatibility, select Using both TCP/IP and named pipes.
Click Apply button when done.
Click OK button when prompted with the message saying that “Changes to Connection Settings will not take effect until you restart the Database Engine service.”
On the “Surface Area Configuration for Services and Connections” page, expand Database Engine, then click Service.
Click Stop button to stop the SQL Server service.
Wait until the MSSQLSERVER service stops, and then click Start button to restart the MSSQLSERVER service.

Secondly, SQL Server Browser service has to be enabled to allow for local and remote connections if SQL Server 2005 is running by using an instance name and users are not using a specific TCP/IP port number in the connection string.

Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Surface Area Configuration.
On the “SQL Server 2005 Surface Area Configuration” page, click Surface Area Configuration for Services and Connections.
On the “Surface Area Configuration for Services and Connections” page, click SQL Server Browser.
Select Automatic as the Startup type to start SQL Server Browser service automatically every time system starts.
Click Apply button.
Click on Start button to start the service immediately.
Click OK button.

Finally, if remote computer needs to connect and access SQL Server, an exceptions in Windows Firewall included in Windows XP SP2 (Service Pack 2), Windows Server 2003 and Windows Vista needs to be created. If you’re using third-party firewall system, the exception rules also needed to be created to allow external remote connections to the SQL Server 2005 and SQL Server Browser Service to communicate through the firewall, else connections will be blocked. Consult the firewall manual for more details. Each instance of SQL Server 2005 must have its own exception, together with an exclusion for SQL Server Browser service.

SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you must identify the correct instance ID. To obtain an instance ID, follow these steps:

Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Configuration Manager.
In “SQL Server Configuration Manager”, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
On the “SQL Server Browser Properties” page, click the Advanced tab, locate the instance ID in the property list.
Click OK button.

Then create an exception for SQL Server 2005 in Windows Firewall.

Click on Start button, the click on Run and type firewall.cpl, and then click OK. For Windows Vista, type firewall.cpl in Start Search box and press Enter key, then click on Allow a program through Windows Firewall link on left tasks pane.
In “Windows Firewall”, click the Exceptions tab, and then click Add Program.
In the “Add a Program” window, click Browse button.
Click the C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Binn/sqlservr.exe executable program, click Open button. MSSQL.1 with is a placeholder for the instance ID that is obtained from previous procedure. Note that the path may be different depending on where SQL Server 2005 is installed.
Click OK button.
Repeat steps 1 through 5 for each instance of SQL Server 2005 that needs an exception.
For SQL Server Browser service, locate the C:/Program Files/Microsoft SQL Server/90/Shared/sqlbrowser.exe executable program, and click Open button.
Click OK button.

IMPORTANT: You're reading a machine translated page which is provided "as is" without warranty. Unlike human translation, machine translation does not understand the grammar, semantics, syntax, idioms of natural language, thus often produce inaccurate and low quality text which is misleading and incomprehensible. Thus, please refer to original English article whenever possible.

MDL blog postings now continue at Tip and Trick, and readers are welcome to join My Digital Life Forums.