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.

James Hay
  • 481
  • 1
  • 3
  • 10
  • 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 '11 at 22:05
  • For me the error message is clear. What didn't you understand? – mailq Nov 30 '11 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 '11 at 22:10
  • @mailq Yes the error message is clear, "Could not open a connection to SQL server". What I need is to find out why and fix it. – James Hay Nov 30 '11 at 22:10
  • Are you able to ping the remote database server from the development server? It could be a DNS fault, or network routing issue, or firewall, or... – Tak Nov 30 '11 at 22:11
  • @Tak Yes I can ping it by domain name and it resolves to it's IP fine. – James Hay Nov 30 '11 at 22:11
  • @JamesHay The way to find out is presented in the error message. – mailq Nov 30 '11 at 22:14
  • possible duplicate of [Cannot connect to SQL Server](http://serverfault.com/questions/58579/cannot-connect-to-sql-server) – mailq Nov 30 '11 at 22:15
  • @mailq Well the instance name IS correct, and in my edit I've explained why I cant configure SQL server. – James Hay Nov 30 '11 at 22:27
  • You pay for it. So lets get it configured!! – mailq Nov 30 '11 at 22:29
  • @mailq Configure what? I don't know what to do that's why I'm asking for help. – James Hay Nov 30 '11 at 22:32
  • 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 '11 at 22:34
  • You pay for it. So ask them!! – mailq Nov 30 '11 at 22:34
  • 1
    @JamesHay - Glad you got to the bottom of it. – Bryan Nov 30 '11 at 23:40

2 Answers2

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.

Bryan
  • 7,628
  • 15
  • 69
  • 94
  • 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 '11 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 '11 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 '11 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 '11 at 22:58
  • Sorry by local I meant the actual computer I'm working on. Development server is part of this network that my local machine is on, it is used for other server roles as well if that makes a difference. The production system is a VPS service that we pay for, as is the database system. Yes the telnet is all good from my local machine and production server – James Hay Nov 30 '11 at 23:00
  • Does your local PC sit on the same subnet as your development server? Do they both use the same gateway? Is there anyone that manages the network there that you could speak to? They might hold the answer to why you can't connect from your development server. Assuming the development server isn't internet facing, you could try disabling the firewall on the server. – Bryan Nov 30 '11 at 23:05
  • The server actually runs our emails domain etc, but I use IIS and a spare domain name to test our sites intermittently. We do however have 2 different routers, 1 for my office, and one for the rest of the staff (which includes dev server). I just tried telnet on a coworkers computer and it didn't work so perhaps it is a firewall in that router preventing it? – James Hay Nov 30 '11 at 23:15
  • I'll have a chat to our IT guy and see if he can change it or something. – James Hay Nov 30 '11 at 23:16
  • 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 '11 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.

Matej
  • 193
  • 1
  • 6
  • 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 '11 at 22:47
  • Can you ping sql server from local and dev? – Matej Nov 30 '11 at 22:50
  • Yes I can ping from local, dev, and production. – James Hay Nov 30 '11 at 22:58