Thursday, September 10, 2009

Unable to connect to Sql Server 2008 Express Edition Remotely using the IP and the named instance

If you are trying to connect your sql server 2008 express edition instance remotely and facing problems while connecting it although you have tried all the possible solutions in the book then the problem can be a really very silly thing and what’s that thing , i will describe it shortly. If you are getting the below mentioned error then there might be many reasons for that error.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (.Net SqlClient Data Provider)

So in the list of probable reasons is :-

  • Check if the sql server 2008 express edition service and sql server browser services are running or not, if not then please run them first.

  • First check whether TCP/IP is enabled in the Sql Server Configuration Manager Tool and select the instance of sql server 2008 express edition for that, if not enabled enable it.

  • If TCP/IP is enabled then check your firewall settings open two ports 1433 and 1434 on the firewall, generally 1433 is meant for Sql server and 1434 is meant for sql server browser. 1433 is TCP port and 1434 is UDP port
  • The silly mistake which happens when working with sql server 2008 express edition is that by default it runs on dynamic ports so you can’t run it remotely with all the above configuration and it will lead to frustration.So what to do then, the solution is just go to properties of the TCP/IP and in the TCP Port just give whatever port you want your sql server to be run on.That will do the trick for you.

 

  • After doing the port settings now check the firewall rules and open the respective ports which you have just configured and your sql server instance will run remotely.

Happy Programming!!!!!!!!!!!!!!!!!!!!

Submit this story to DotNetKicks

del.icio.usSave Total0 users

4 comments:

Anonymous said...

Great this resolved my mystery.
thanks!

Anonymous said...

great post, fixed my issue as well

There is no cow level.

There is no surface area configuration manager.

Anonymous said...

Also check windows firewall settings

Anonymous said...

Whoa! This worked for me! Thanks so much.
Based on the misleading error message I was checking SQL Server > Properties > Connections > Allow remote connections to this server

Which was checked ofcourse. After following your instructions, turns out it was the TCP/IP which was disabled. After enabling it, I was able to connect to it without any problems.

Post a Comment