Connect to SQL Server 2008 with TCP/IP

I get the following error when I try to connect with TCP/IP from SQL Server Management Studio. I need a step by step description to solve my problem. What's wrong here?

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开发者_Python百科.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()

---

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.

1. Click on the Protocols for MSSQLServer as listed above.
2. Then in the right hand pane enable TCP/IP.
3. Now double click TCP/IP to get a dialog window.
4. If you want to listen on all the IP addresses for the server Select Yes in the Listen All box on the first Protocol tab; otherwise
5. Select the IP Addresses tab and enable the desired IP addresses by setting Enabled to Yes.

---

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:

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

The key is the Network Library=DBMSSOCN; which tells your code to connect using TCP/IP.

---

Also worth checking that the SQL Server Browser Service is running as TCP/IP connections will use this service

---

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.

---

There is a good walkthrough for configuring this, including

• Enabling TCP/IP protocol for the database server
• Enabling remote connections for the database server
• Allowing the TCP traffic through the firewall

at http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx.

---

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

---

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.

---

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".

---

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 :)