techSvcMgr_4_smb on Mon, 16 Sep 2013 22:19:49
Hello, trying to establish an ODBC connection from the main office (192.168.1.x) to a branch office (192.168.3.x)
Our Watchguard firewall is setup to let all traffic flow between the 2 sites and we do not have any issues pinging or remoting to the branch office and the branch office can do the same towards the Main office.
But I am unable to create an ODBC connection from the Main office (tried with W7 and XP machines) to the SQLServer running on an XP box in the Branch office. The other machines in the branch office can connect to the SQLServer at the branch office.
I've tried Named Pipes and TCP/IP , just get errors 10061 and 17. At this point I am plain out of ideas.
Addendum: The branch office can map a drive at the Main office,
net use \\192.168.1.137\Test /user:test test
but the Main office can not map a drive at the branch office.
Jeremy A - SQL on Mon, 16 Sep 2013 22:53:20
Using the ODBC connection after the IP address try adding a comma and then the port. Example serveripaddress,1433. You should be able to connect using TCP/IP. You might also verify that the SQL port is on 1433.
There may also be a firewall issue in which case you will need to get the networking team involved.
Make sure that port 1433 can communicate both ways. You can troubleshoot the network port by using the following from a command prompt. (Without the less than and greater than symbols.)
telnet <ipaddress> 1433
If you receive a blinking cursor it worked, there will be an error if there is something wrong.
techSvcMgr_4_smb on Mon, 16 Sep 2013 23:28:26
Both Watchguard firewalls are letting the traffic on 1433 flow through. telnet'ing to the branch office pc 1433 does result in a 'connection denied'. So I turned off the windows firewall on that pc. same issue.
Is there a configuration in the SQLServer that would allow only the local ip space to connect?
Candy_Zhou on Wed, 18 Sep 2013 07:49:00
To be able to connect to sql server instance of different network we will need to consider following things,
1) Windows authentication will not work since both machines (SSMS and instance) are in different domains
2) Firewall rule has to be created to allow communication on ports 1433 (TCP) and 1434 (UDP) (default) on instance machine.
OK,let's try to follow the steps below to troubleshoot your issue.
First, make sure the main office and branch office and ping through with each other(Using command prompt in windows).
Secondly,download a SQL Server Management Studio(short for SSMS,it is better to download a higher edition for example:SSMS2012) in main office machine, we can use this client tool to connect remote SQL Server service.
Then, create a SQL Server login with appropriate permission in SQL Server locate at branch office for main office SSMS client, we can test by grant sysadmin permission for this login, and don't forget modify the Server authentication with "SQL Server and Windows Authentication Mode". After the modification, please restart the SQL Server Service.
Now, at Main office, please try to use SSMS to connect to SQL Server locate at Branch office. For example, I have a login locate at a computer with IP address"172.22.109.X", and I try to connect to it from my local machine with IP address"172.22.108.X", open the SSMS, then the picture appears:
Please modify the Server name replace with your branch office IP(If that SQL Server is default instance, just type the ip is OK, if it is a named instance, please replace with 172.22.109.X\[instanceName]). And replace with your own login and password which created in SQL Server locate at branch office.
If you have any concern, please let me know.
Jeremy A - SQL on Wed, 18 Sep 2013 15:13:43
In my experience this is usually a problem with network firewalls. The telnet should work, unless your default port is set to something else on the SQL server. Sometimes this can be set dynamically. Please remote control the branch office and open ssms connect using "localhost ,1433" without the quotes. If you are able to connect then you are likely experiencing a network related block somewhere (especially since you are having problems using telnet). If you are not able to connect check the TCPIP port in the SQL Server configuration manager. Please see image below.
techSvcMgr_4_smb on Wed, 18 Sep 2013 15:33:47
I'm going on vacation , will try these out next week. But I did turn off the firewall on the pc doing the SQLServer and i can see the 1433 traffic being allowed through on the WatchGuard logs.
But hey, i'm stumped as it is so trying these 2 things again is probably a good idea.
techSvcMgr_4_smb on Mon, 30 Sep 2013 18:47:10
oh boy, now I am really confused.The program that is dependent on the SQL-Server is running just fine but when I tried telnet localhost 1433 on that pc running the SQL-Server I got the error message:
could not open connection to the host, on port 1433
Same result when I tried the pc's actual ip address.
"netstat -ab" does not show any listening on 1433
so what is going on? My confusion is increasing.
Jeremy A - SQL on Tue, 01 Oct 2013 16:26:23
There are two possible firewalls, you already eliminated windows advanced firewall as the problem by turning it off. Next step would be to find out if that port is being blocked by your network administrators, or your ISP.
Please click the Mark as answer button if I answered your question, and vote as helpful if this reply helps you.
techSvcMgr_4_smb on Fri, 04 Oct 2013 17:15:28
i posted that the pc itself could not communicate with the SQL-Server, no firewall is involved.
So why can't Microsoft's own OS communicate with Microsoft's own SQL-Server ?