Remote ODBC connection to SQL Server 2005 Exp.

[Guest]

I am unable to connect to a SQL Server 2005 Exp. server on a three computer
network at my office from my home computer.

1. I am using Access 2000 as the front end with SQL 2005 Exp. as the back
end. The office network consists of 2 XP pro machines with Access and a
third W2K machine acting as the SQL Exp. server. The home machine is XP Home
Ed.

2. There is a Netgear RP 114 router for the office network. This router
has port forwarding set to forward any request via port 1433 to the SQL
server machine. I have also listed the SQL server machine as a "trusted
device" on the router meaning that it "has full access to the internet
without blocking of any resource."

3. I have completely uninstalled firewall software from the SQL server
machine. As far as I can figure, there is no additional firewall on the
router.

2. I am able to connect to the SQL from the two machines "inside" the
router on the office network, and my only problem is establishing the ODBC
connection from the home computer.

3. In SQL Server Configuration Manager, TCP/IP is enabled and running. In
SQL Server Surface Area Config., Local and Remote Connections with TCP/IP is
enabled.

4. My TCP/IP protocol is set up with a fixed IP address.

You need to establish the connection through Start/Settings/Administrative
Tools/Data Sources (ODBC) and you are guided through 4 screens to establish
the connection. I get to the screen where I have indicated to log on with
SQL authentication. I enter the WAN IP address for the router along with the
userID and password. When I click "next," I get an error message:

Connection Failed ... Error: 10060 ... Error: 17 ... SQL server doesn't
exist or access denied.

You would expect this to be a firewall problem or router problem. The
router is old, but it has been working satisfactorily with an ODBC connection
to an off-site SQL server prior to my attempt to use SQL Exp. on a machine in
my office.

There is one other glitch. The fixed IP address for my router is "behind"
another router. A large company in my building has a T1 line, and they have
set me up with my own IP address "behind" the router that is the IP address
for the company. The IT person tells me this should make no difference, as
my IP is different from theirs and stuff simply passes through. But I'm not
so sure.

Does anyone have any additional thoughts on any of this? Thank you.

 

[Sylvain Lafontaine]

Try scanning your Win2K server from the Internet to see if the port 1433 is
visible:
http://www.pcflank.com/scanner1.htm

You can also make some tests from home using the ping or tracert command or
using a port scanning software like the following:
http://insecure.org/nmap/man/man-port-scanning-basics.html

Please take note that ping or tracert don't scan for a particular port and
will only test if a machine is globabally accessible from the internet by
the TCP/IP protocol. These two services can also be blocked by your *little
glitch*; so a failure reported by Ping or Tracert doesn't necessarily mean a
bad installation in your case. Probably that scanning the ports will give
you a better idea of your problem but Tracert can help you to define its
extent.

Other tests are available on the Internet.

 

[Guest]

Sylvain, *Thank You* for all your help.

I now have it working. I want to add what (I think) happened following my
last post for anyone else with my problem. You will see from the explanation
that I'm not exactly sure what the various things I clicked are supposed to
do (but I'm learning!).

First, since I solved the problem without further recourse to the folks
downstairs in my building, the problem had nothing to do with the fact that
my IP address has to pass through their router.

Second, my particular router, the RP114 by Netgear, does not have a DMZ
option. Instead, it has a screen called "Port Forwarding." I had entered
1433 as the port to forward, and I gave the local machine address
(192.168.0.2) as the machine to forward to. This had not worked. Looking at
the manual for the machine, the picture showed a top line on this screen with
the ports already filled in as "default" and 0.0.0.0 as the entry. I found
no explanation of what this was about, but I noticed that my Port Forwarding
screen had different numbers on this default line. I changed to 0.0.0.0 (as
well as doing the things outlined below), and this corrected the problem.
However, once I got it working, I switched the numbers back to see what would
happen, and it is still working. This is a long explanation to say that the
Port Forwarding had nothing to do with my problem.

Third, and in addition to the other settings that must be set, I went into
the SQL Server Configuration Manager, clicked on "SQL Server 2005 Network
configuration," then clicked on "Protocols for SQL Server." In the right
hand panel, you see TCP/IP, and I right-clicked here to the Properties.
Under "Properties," you see two tabs. The first one is "Protocol" and the
only entry here is labeled "General." There is an "Enabled" line, and this
is set at "Yes." The other tab, is called "IP Addresses," and this tab has
as three sections: "IP1," "IP2," and "IPAll." When I looked at these
sections, I saw that one or both (I don't remember) of the "active" and
"Enabled" lines for IP1 and IP2 were set at "No." I changed everything to
"Yes." I think this is what solved the problem. I'm not sure why they were
disabled, but I had previously started/stopped various Services and re-booted
multiple times, so I don't think it was simply because I failed to reboot. I
haven't seen any discription anywhere about checking these settings. So for
whatever it is worth, that is something you should look at if you have any
problems like mine.

Again, thank you for all the help.

 

[Sylvain Lafontaine]

Using the port 1433 is not the only way to access a SQL-Server via tcp/ip:
not only other static ports can be used but also you can ask SQL-Server to
dynamically choose them. With SQL-2000, the starting point for allocating a
dynamic port is done via the port 1434 but with SQL-2005, any other port can
be used as the starting point using the SQL-Server browsing service.

This is why I told you to make a test with ODBC and TCP/IP from a local
machine to make sure that everything was OK before going to the Internet.

You should make sure that now, it's not all ports that have been
opened/redirected in your router to your web server by using one of the
scanning facilities already mentionned; otherwise, it shouldn't take long
before your network will be hacked.

With SQL-Server 2000, if you had more than one instance of SQL-Server
installed on your server, I would have made the suggestion of forwarding the
port 1434 too (and clearing the 10 nexts from the firewall) but with
SQL-2005, I don't know.

Take a look now before it's too late to make sure that your LAN is not as
this moment without any protection. (This why I wouldn't really suggest to
anyone to use the DMZ option of many routers; as it can be really dangerous
to use. (Some but not all routers will put a second firewall between the DMZ
zone and the local LAN; this is a grey zone here to me.))

 

[Kandar7272]

I am in the exact same position as this fellow below (except for the
second router part). I have tried the suggestions posted but have not
been able to resolve it. In addition, I have tried "netstat - an" and
the port appears to be open. I also tried to scan for the port 1433 is
from the suggested website but is listed as 'stealthed'. I'm really at
a loss as to what to try next.