[RESOLVED] connect to SQL Server from home fails (working fine at work)

[dannystommen]

Hi guys,

I'm facing the following problem. I created a winfor app which is in use for a year now. Since last month, some employee's are migrating from a desktop pc to a notebook (which they also take home).

When they are at the office, the app works just fine. But when they are at home, the connection to the SQL server can't be established

IBatisNet.DataMapper.Exceptions.DataMapperException: Unable to open connection to "Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0".
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

The Sql server is configured to accept remote connections (otherwise it would not work at the office as well).

Any ideas what is causing this?

(SQL Server 2005 Express Edition )

[Arjay]

The IP address for the SQL server at home is different than when they're in the office. Or if it isn't, see if they can ping the sql server while at home.

[rliq]

When you are at home on your PC, are you able to access the SQL Database (at work) using the SQL Server Management Studio?

[dannystommen]

The IP address for the SQL server at home is different than when they're in the office. Or if it isn't, see if they can ping the sql server while at home.

The IP address is the same in both locations. In the connectionstring, as datasource I'm using mydomain.com//SQLEXPRESS. Pinging mydomain.com results at both places to the same IP.

When you are at home on your PC, are you able to access the SQL Database (at work) using the SQL Server Management Studio?

I haven't tried that yet. Will try it when I'm at home tonight.

[foamy]

Unless there is some Port Forwarding or Network Address Translation set up, I can't see how the SQL Server would be accessible from outside the company network (really, it shouldn't be, unless something is configured!)
So if your users aren't using a VPN client or have a hardware VPN-tunnel configured then it should be no surprise that the database isn't accessible to them when they are not at work.

[dannystommen]

When you are at home on your PC, are you able to access the SQL Database (at work) using the SQL Server Management Studio?

It results in exactly the same error.

Unless there is some Port Forwarding or Network Address Translation set up, I can't see how the SQL Server would be accessible from outside the company network (really, it shouldn't be, unless something is configured!)
So if your users aren't using a VPN client or have a hardware VPN-tunnel configured then it should be no surprise that the database isn't accessible to them when they are not at work.

Why is Port Forwarding necessary? The IP of the domain leads directly to the server.

Network Address Translation... this is new for me...

The company doesn't want to setup a VPN network.


What should I do to make it accessible from home..?

[foamy]

What should I do to make it accessible from home..?

You would need to configure the company network to forward traffic on a specific port (1433 by default) to your SQL Server. If nothing is configured, it will not be accessible from outside the network. I'm not an expert in this sort of configuration (since I'm a developer :-P), but it is necessary if the company does not have some kind of VPN or similar Virtual Connection setup.

[Arjay]

Generally, due to security concerns, it's not considered best practices to expose SQL server directly to the outside world.

Instead, you generally expose a web service (or WCF service) that accesses the db and expose the endpoint externally.

[dannystommen]

You would need to configure the company network to forward traffic on a specific port (1433 by default) to your SQL Server. If nothing is configured, it will not be accessible from outside the network. I'm not an expert in this sort of configuration (since I'm a developer :-P), but it is necessary if the company does not have some kind of VPN or similar Virtual Connection setup.

Our network administrator says that technically it doesn't make any difference if the application is inside or outside the companynetwork. But seems that something is still blocking calls from outside the network. He though t it had something to do with the dynamic port SQL server was using, so I disabled that en set it to the default 1433. Still doesn't work.

Generally, due to security concerns, it's not considered best practices to expose SQL server directly to the outside world.

Instead, you generally expose a web service (or WCF service) that accesses the db and expose the endpoint externally.

I agree, but it's not an option at this time. It's quite a large application, some migrating to webservices would take a while.


Any other suggestions?

[Arjay]

Any other suggestions?

Were you able to connect at home using Sql Svr Management Studio?

[dannystommen]

No, exact the same error!

[Arjay]

Are you using integrated security to connect or sql security?

If using integrated sec, try using Sql security (specify a un and pw in sql).

[foamy]

Our network administrator says that technically it doesn't make any difference if the application is inside or outside the companynetwork.

That's wrong. There's a router or firewall you have to get through when you're outside the company network and there isn't one when you're inside. That's the difference, and if this router/firewall isn't configured to allow the SQL connection then you won't be able to connect.

[dannystommen]

Are you using integrated security to connect or sql security?

If using integrated sec, try using Sql security (specify a un and pw in sql).

The connectionstring I'm using:

Data Source="mydomain.com\SQLEXPRESS";Initial Catalog="some_DB";User ID="user";Password="pass";

So I assume that I'm not using integrated security.

That's wrong. There's a router or firewall you have to get through when you're outside the company network and there isn't one when you're inside. That's the difference, and if this router/firewall isn't configured to allow the SQL connection then you won't be able to connect.

The firewall of the server is disabled. But I think you could be right. When using http://www.yougetsignal.com/tools/open-ports/ (a simple port checker site), and I try to connect to port 1433 on my server, it says that the port is closed. I'll contact the network administrator after the weekend.

[foamy]

Just to be clear: I'm not talking about a software firewall installed on the server, but a hardware unit that needs to be configured to forward traffic on a specific port to the IP address of the server.