使用 TCP/IP 连接到 SQL Server 2008
当我尝试从 SQL Server Management Studio 使用 TCP/IP 连接时，出现以下错误。我需要一步一步的描述来解决我的问题。这是怎么回事？
Cannot connect to
===================================
A network related or instance specific error when a connection to SQL Server...
(provider: Named pipe-provider, error: 40 - SQL Server)
(.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
------------------------------
Error Number: 53
Severity: 20
State: 0
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
我经常忘记的是，即使您在 SQLServerNetwork->MSSQLServer 协议中将 tcp 标记为启用，您仍然需要为您的服务器启用可能不同的 IP 地址。即使只有一个 NIC 的服务器也会有多个 IP 地址。
第一个“协议”选项卡上的“全部监听”框；否则
是。
The thing I usually forget is that even if you mark tcp as enabled in SQLServerNetwork->Protocols for MSSQLServer, you still need to go and enable the potentially different IP addresses for your server. Even a server with a single NIC will have multipe IP addresses.
Listen All box on the first Protocol tab; otherwise
to Yes.
当您检查 SQL Server 网络配置（
开始菜单 > Microsoft SQL Server > 配置工具 > SQL Server 配置管理器 > SQL Server 网络配置）时，您会看到什么？您的服务器上真的启用了 TCP/IP 协议吗？它默认处于关闭状态，并且在大多数情况下保持关闭状态（除非您专门将其打开）。
仅使用基于 IP 的服务器地址并不意味着您正在使用 TCP/IP 协议进行连接......检查 http://www.connectionstrings.com/sql-server-2008 用于将使用 TCP/IP 的示例连接字符串：
关键是
Network Library=DBMSSOCN;它告诉您的代码使用 TCP/IP 进行连接。When you check the SQL Server Network Configuration (
Start Menu > Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager > SQL Server Network Configuration), what do you see?Is the TCP/IP protocol really enabled on your server? It's off by default, and stays off in most cases (unless you specifically turn it on).
Just using a IP-based server address doesn't mean you're connecting using the TCP/IP protocol.... check http://www.connectionstrings.com/sql-server-2008 for a sample connection string that will use TCP/IP:
The key is the
Network Library=DBMSSOCN;which tells your code to connect using TCP/IP.还值得检查 SQL Server 浏览器服务是否正在运行，因为 TCP/IP 连接将使用此服务
Also worth checking that the SQL Server Browser Service is running as TCP/IP connections will use this service
请访问 www.connectionstrings.com。错误消息看起来您正在使用命名管道而不是 tcp/ip。不要忘记您必须在 SQL Server 的服务器计算机上显式启用 TCP/IP。
Have a look at www.connectionstrings.com. Error message looks like you're using named pipes and not tcp/ip. Don't forget you must enable TCP/IP on server machines for SQL Server explicitly.
有一个很好的配置方法，包括
为 http://blogs.msdn.com/b/walzenbach /archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx。
There is a good walkthrough for configuring this, including
at http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx.
按照此操作：
开始
->节目
->微软 SQL Server 2008
->配置工具
-> SQL Server 配置管理器
SQL Server 网络配置
-> [SQLInstanceName] 的协议
右键单击 TCP/IP ->使能够
Follow this:
Start
-> Programs
-> Microsoft SQL Server 2008
-> Configuration Tools
-> SQL Server Configuration Manager
SQL Server network Configuration
-> Protocols for [SQLInstanceName]
Right click TCP/IP -> Enable
如果 TCP/IP 已启用，那么听起来您的防火墙正在阻止连接。打开相关端口应该就可以了。
您应该检查连接两端的防火墙。
If TCP/IP is already enabled then it sounds like your firewall is blocking the connection. Open the relevant ports and it should work.
You should check the firewall on both ends of the connection.
要使用"TCP/IP"连接到MSSQL(SQL Server)，您需要使用SQL启用"TCP/IP"服务器配置管理器（SSCM）。这是如何查找 SQL Server 配置管理器(SSCM ）在您的 Windows 计算机 中。
因此，在SSCM上，从“SQL Server Network Configuration/Protocols for SQLEXPRESS”启用“TCP/IP”，如下所示。 *默认情况下禁用MSSQL的“TCP/IP”：
点击“启用”后，如图所示上面，您将收到以下消息：
因此，如上面的消息所示，从“SQL Server 服务”重新启动 SQL Server(SQLEXPRESS) > 如下图：
最后，您可以使用“TCP/IP” 连接到MSSQL。
To connect to MSSQL(SQL Server) with "TCP/IP", you need to enable "TCP/IP" with SQL Server Configuration Manager(SSCM). This is How to find SQL Server Configuration Manager(SSCM) in your Windows Machine.
So, on SSCM, enable "TCP/IP" from "SQL Server Network Configuration/Protocols for SQLEXPRESS" as shown below. *"TCP/IP" for MSSQL is disabled by default:
After clicking on "Enable" as shown above, you will get the message below:
So, as the message above says, restart SQL Server(SQLEXPRESS) from "SQL Server Services" as shown below:
Finally, you can connect to MSSQL with "TCP/IP".
并且 - 您可以在 SQL Server 配置管理器的协议屏幕中更改它，但只有重新启动 sql server 服务后才会生效。我只是重新启动 - 这是确定的唯一方法（那里有外星人参考:)
And - you can change it in the protocols screen of the SQL Server Configuration Manager but it won't take effect until the sql server service is restarted. I just reboot - it's the only way to be sure (Aliens reference there :)