| Author |
Topic |
|
hfchen86
Starting Member
12 Posts |
 Posted - 2006-08-22 : 15:35:48
|
| Hi everybody,I am developing windows application using C#. I need to access sql server 2000 through Internet. However, I always get the "SQL Server does not exist or access denied" message.There are many same posts across various sites. The solution, as I understand, is to just use the IP address plus port number as the data source. I tried that and still got the same message.The port(1433) is open. The IP address I used is the router address, and I set up port-forwarding to the server (XP professional). The connection string is;"user id=*****;data source=192.168.0.150,1433;network library=DBMSSOCN;persist security info=False;initial catalog=pubs;password=******";Can somebody tell me what I have been missing or what I did wrong?Thanks a lot,Haifeng |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-22 : 15:46:02
|
| Do you have a firewall in between the client machine and the database server? If so, is port 1433 open to the client? You said 1433 is open, but you didn't specify from where and to where.Tara Kizer |
 |
|
|
hfchen86
Starting Member
12 Posts |
Posted - 2006-08-22 : 16:08:37
|
| The port 1433 is open on both the router and the server. The port 1433 on the router is forwarded to the server. There is only Window XP firewall on the server, but the port 1433 is added to the exceptions.Haifeng |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-22 : 16:16:05
|
| Did you check to see if the SQL Server service was started? If it is, then the problem has something to do with your configuration. SQL Server is not rejecting the connection.Tara Kizer |
|
|
|
hfchen86
Starting Member
12 Posts |
Posted - 2006-08-22 : 16:42:55
|
| Hi, Tara,Thanks for your reply.The sql server service is running. I have configured SQL server to allow network connection, or so I thought. Here is what I have configured.On the client network utility on the client machine, the "TCP/IP" and "Named Pipes" protocols are enabled. However, I don't think it's necessary to install SQL server on the client machine when I am developing applications that access server remotely. That is for remote server registration using Enterprise Manager. The same two protocols are also enabled on the server machine. So what else am I missing?Haifeng |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2006-08-22 : 16:43:38
|
| see this, if it helpswww.connectionstrings.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-22 : 17:07:37
|
| That is correct that you don't need to add SQL Server to the client machine, but it does need MDAC as that has the drivers. I don't think that you are missing anything as far as what you have described. Do you have access to a network sniffer to figure out where you are being blocked?Are you able to remotely connect to this SQL Server from a machine that isn't across the Internet? Do you have a machine to test that is perhaps on the same subnet as the database server?Also, as an FYI, you really shouldn't use 1433 as the port to talk to SQL Server when your clients aren't on the same side of the firewall as the database server. The recommendation is to use a port that is 5 digits. Tara Kizer |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-22 : 17:09:09
|
quote:
Originally posted by afrika
see this, if it helpswww.connectionstrings.com
His connection string looks correct to me.Tara Kizer |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-22 : 17:40:21
|
| Are you trying to connect to IP address 192.168.0.150 across an Internet connection?Any 192.168 address is a private network address and will not be routed across the Internet.CODO ERGO SUM |
|
|
|
hfchen86
Starting Member
12 Posts |
Posted - 2006-08-22 : 17:40:37
|
| Thanks guys,I tried that connection string. I have posted my connection string in the initial message. There is no problem when I try this in the subnet. As a matter of fact, I don't even need the IP address when I try this in the subnet. All I need is the server name. Everything is fine. BTW, Tara, could you suggest any network sniffer software that I might be able to use to see where my program is blocked?Haifeng |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-22 : 17:51:54
|
| What happens when you try to ping the SQL Server from the remote machine you are trying to connect from?CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-22 : 17:57:13
|
| I think Michael's first post probably is it. 192.168...I've never used a network sniffer before. I leave that to our network admins. I've only seen them use hardware sniffers before. I know that there are software sniffers out there, I just don't have any experience with them.Tara Kizer |
|
|
|
hfchen86
Starting Member
12 Posts |
Posted - 2006-08-22 : 18:39:55
|
| We have two levels of subnet here. My machine is on the top level right beneath the ISP, the same level with the router. The server is one level below. That's why I need to port-forward from the router to the server. 192.168.0.150 is the outside IP of the router.BTW, the IP address of my machine is 192.168.0.112.When I ping the IP 192.168.0.150, I got normal response. I could also use the same IP to remote-desktop to the server. when I try this from outside, I used static IP that port-forwards to the router. It works for remote desktop, so it should work for sql server as long as the port is set up correctly. However, it still doesn't work and I got the same message.Did I make it clear or am I missing something? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-22 : 18:49:27
|
| So you can RDP into the database server from the client machine where you are receiving this SQL Server error? If so, ensure that SQL Server is listening on port 1433 by using Server Network Utility or by viewing the SQL Server Error Log.Tara Kizer |
|
|
|
hfchen86
Starting Member
12 Posts |
Posted - 2006-08-22 : 19:03:45
|
| Yes, I can RDP to the database server. In addition, port 1433 is listening and added to the firewall exceptions. I am really stuck here. Of all the same questions posted elsewhere, it seems to me that half of them get solved by using the IP address and the other half doesn't work.Anybody has any suggestion? Thanks.Haifeng |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-22 : 19:07:04
|
| But did you verify that SQL Server is listening on port 1433?Tara Kizer |
|
|
|
hfchen86
Starting Member
12 Posts |
Posted - 2006-08-23 : 10:18:57
|
| The server is listening on port 1433.Is there anybody having the exact same problem as I do? I really appreciate what you have done for the configuration because I think that's where I am having the problem.Haifeng |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-23 : 10:54:25
|
quote:
Originally posted by hfchen86
The server is listening on port 1433.Is there anybody having the exact same problem as I do? I really appreciate what you have done for the configuration because I think that's where I am having the problem.
I would recommend you use a couple of tools to help solve this problem.1. ethereal sniffer http://www.ethereal.com/2. portqry http://support.microsoft.com/kb/310456/doing the sniffer traces is going to take some time, and you are going to have to run the sniffer on both segments of your network to see what is going on. This means you need to run the sniffer on your workstation as well as the db server you are having a problem on.portqry (actually portqryui) is what I use to make sure that a port is actually open. Run that first from your workstation to see if you can actually connect through port 1433 to your server.also, some questions:1. you say you are needing internet connectivity, yet you specify that you are using private IP address ranges. Requests for 192.168.x.x ip ranges will be dropped when it hits real internet routers. Are you actually needing to connect from a real internet address, or are all your connections going to be limited to this private ip range?2. What is the IP address for you SQL Server? is it also 192.168.0.x? 3. Are there any errors in your router? your network admins should be able to see if anything funky is going on. 4. Is your database Server clustered? Are you up to date on service packs?5. Have you tried making an ODBC connection (data sources control panel in admin tools) from your workstation to your SQL Server using the credentials your app is using? This eliminates any C# code from being the problem and tests just the databas connectivity piece.-ec |
|
|
|
hfchen86
Starting Member
12 Posts |
Posted - 2006-08-23 : 12:24:35
|
| Thanks very much, ec. I am still working on it, but you have given me some really good suggestions. So let clarify the situation by answering your questions.The db server is not clustered, and I don't find any network errors. We are up to date on the service packs.The IP address of the server is 192.168.1.104. The router with IP 192.168.0.150 is a subnet router, it is on the same level with my workstation (IP 192.168.0.112). There are multiple port-forwarding applications set up on the router, and so far every one of them works except sql server.I have tried with ODBC connection and I got the same message. I believe it's the same as with sql server registration. Since I have sql server installed on my workstation, it's the first step I tried.In addition, I tried with portqry. Here is the message I got: Starting portqry.exe -n 192.168.0.150 -e 1433 -p TCP ...Querying target system called: 192.168.0.150Attempting to resolve IP address to a name...Failed to resolve IP address to namequerying...TCP port 1433 (ms-sql-s service): NOT LISTENINGportqry.exe -n 192.168.0.150 -e 1433 -p TCP exits with return code 0x00000001.At first, I thought the port is not listening. However, I tried again with port 3389 (RDP), which I know is working. I got the same message. Therefore, portqry didn't tell me anything or I am still missing something here with the set up.I am looking forward to your suggestion.Haifeng |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-23 : 12:57:10
|
| I don't know why portqry is having problems with your setup, it should work as far as I know. Maybe there is configuration problems in your router. Does the router show any errors?what happens if you use the actual IP address of your db server instead of the router?I guess I don't quite understand why you have a setup like this. A router is supposed to route packets right? You are requesting data from another subnet which is not a big deal for the router. You should not need any port forwarding rules becuase you don't seem to be connecting to a NATed subnet. -ec |
|
|
|
hfchen86
Starting Member
12 Posts |
Posted - 2006-08-23 : 14:31:43
|
| We are a small company developing windows application using C#. So we only have very simple network set up. To mimic remote sql server access, I have to put client and server on two different LAN to do testing. When I try to connect from outside of our office, I would use the static IP plus the port number as the data source in the connection string. In addition, static IP is directed to the router, which port-forwards various applications to different workstations so that people can access them outside the office. I hope I have explained the situation clearly. Now I am open to suggestions. |
|
|
|
Next Page
|
remote server access problem