0

I'm using VS2008 on my local machine for writing an ASP.Net app. Our SQL Server 2008 instance is hosted remotely on a dedicated database server. I can connect to the server through my app on both my local machine and production server, but not on our development server.

I have opened port 1433 on the dev server but that didn't work. The connection string is the same on both servers. Both production and dev servers are using windows server 2008. The error i get it

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

EDIT: I think I should make it clear that the SQL Server is run by a hosting company which we pay for so I don't have access to any configuration of this server. Also, as far as I am aware, the server is working fine since I can connect to it from 2/3 different places through my application.

EDIT 2: So it turns out that one of the routers in our office was blocking traffic through port 1433. Thanks Bryan for your persistent help.

Improve this question
14
  • 2
    Rules on a firewall somewhere between your development server and your database server, perhaps? My hunch would be that this isn't SQL Server itself at fault.
    – Andy Smith
    Nov 30, 2011 at 22:05
  • For me the error message is clear. What didn't you understand?
    – mailq
    Nov 30, 2011 at 22:06
  • @AndySmith yeah that's what I thought because I can connect both locally and on production fine, so it can't be the SQL Server. I have opened 1433 both ways to be sure but it hasn't worked. The Sql server isn't using a different port to the default.
    – James Hay
    Nov 30, 2011 at 22:10
  • 1
    James, I'd take note of Andy's helpful comment. I guess mailq is referring to the part of the message that states The server was not found or was **not accessible**. Clearly it's not the SQL server config.
    – Bryan
    Nov 30, 2011 at 22:34
  • 1
    @JamesHay - Glad you got to the bottom of it.
    – Bryan
    Nov 30, 2011 at 23:40

2 Answers 2

Reset to default
2

Clearly, the server config is fine, as you've explained that you can already connect from other remote systems, but are having problems with this one specific system. You can resolve and ping the server from your development system, but it seems that you can't make a connection to the SQL port (1433). As Andy has already pointed out in the comments, that is probably because a firewall between your development system and the SQL server may be blocking your connection attempts.

You can test connecting to the SQL server using telnet (obviously replace the hostname with that of your SQL server).

telnet sql.server.com 1433

If you see a response similar to:

Trying sql.server.com...
Connected to sql.server.com.
Escape character is '^]'

...then it isn't a firewall problem, however if you see a message similar to:

Unable to connect to remote host

...then it's almost certainly a firewall that's blocking you.

There is a good MSDN article that explains how to troubleshoot connections to SQL server, which goes into far more detail.

Improve this answer
9
  • Locally the telnet works almost instantly. On dev server it doesnt connect at all "Could not open connection to host". So It's a firewall problem? I have opened 1433,1434 TCP and 1433,1434 UDP so far but nothing has happened. I'll read throught that article now..
    – James Hay
    Nov 30, 2011 at 22:51
  • 'Locally' = Local to you, or local to the SQL server? From what you've said the telnet test should work fine from your production and your local system.
    – Bryan
    Nov 30, 2011 at 22:54
  • Where is your development system located? Is it on the same network as either the production system or your local system? I'm guessing not, but it might be.
    – Bryan
    Nov 30, 2011 at 22:56
  • 1
    Bear in mind, if it is a firewall, it might not be one you have control of, but one located somewhere between the two systems.
    – Bryan
    Nov 30, 2011 at 22:58
  • 1
    Indeed, it sounds like it's the router that the dev server uses. Are you able to check the config of the router yourself? If not, speak to the person who manages it. (Edit: You beat me to it with your last comment!)
    – Bryan
    Nov 30, 2011 at 23:18
0

Try to force TCP connection type (it seems that named pipes has higher priority). Change server part of the connection string to server=tcp:hostname,1433.

Improve this answer
3
  • Thanks for the suggestion. Tried it on local machine, and it continued to work as it did before. Tried it on dev server and the error message changed: "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"
    – James Hay
    Nov 30, 2011 at 22:47
  • Can you ping sql server from local and dev?
    – Matej
    Nov 30, 2011 at 22:50
  • Yes I can ping from local, dev, and production.
    – James Hay
    Nov 30, 2011 at 22:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .