Question on a remote connection error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Question on a remote connection error

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-04 : 11:44:15

Through the SMS tool, the following error is being received:

===================================
Cannot connect to MyServer\MyInstance.
===================================
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

Note: obviously the server/instance of MyServer\MyInstance is fake :).

The setup is the developers receiving this error are connecting to the domain remotely through a VPN connection (so they are on a different, non trusted domain). Also, the IP address our server is on uses an internal range that the remote developers already have in use for something else on their network. So we gave them a bogus IP to use where we then perform a 1-to-1 NAT translation on our end to the correct IP. This all appears to work but the developers still receive this error.

We do have remote connections enabled for both TCP/IP and NetBIOS and everyone at our office can connect just fine. With this in mind, what could be some other issues that can cause this? The only other thing I can think of is somewhere (there are 2 firewalls involved - ours and theirs) is that the UDP packets are not being received by the client.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-12-04 : 11:51:07

Have you verified that the server is visible to them ( ping )? Is the SQL Server port open on the firewall(s)?

Future guru in the making.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-04 : 12:22:02

Yes and yes. We have verified they can connect to the VPN and Remote Desktop into the server, they can execute both ping and tracert and see the server. When they RD into the server, they can use SQL SMS just fine on that server (obviously bypasses the entire firewall thing).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-04 : 12:26:23

Can they telnet to the SQL Server using the SQL port from their client machines? (telnet hostName portNumber)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-04 : 12:53:17

Not sure yet. I sent them this link that contains a checklist to try on both the server (me) and the client (them) - http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

They are in a different time zone then me and will be in their offices in about 10 hours. I'll find out more from their end tomorrow.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-05 : 12:07:34

Ok, the remote site is still having issues and they have completed the client side checklist I provided to them. To summarize their results:

ping server (name not IP) - Confirmed - results show IP on their end.

telnet <remoteservernamenotip> <port> - Fails with ports 1433 and 1434 - but probably will since we have our box to use random port numbers and utilize SQL Browser to facilitate that connection.

They can access the remote server via share name of \\<remoteservernamenotip>

Via SQL Server Configuration Manager - confirmed NP and TCP are enabled. Even disabled shared memory to make TCP top of the list. Connection still fails.

Via OSQL command of osql /S <remoteservername\instance> /E - Fails with error
[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Side note: they attempted to use OSQL to connect via IP but I just realized the error they got doesn't mean anything in this situation... they only entered the IP and not the instance name. Since there is no default instance on this server it won't connect anyway :).

They have attempted to connect via IP\INSTANCE and NAME\INSTANCE - no such luck.

So, is there anyone out there with experience connecting to a remote server through a VPN where NAT translation needs to be included due to IP conflicts (same IP in use on their network and our network).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 12:54:06

The telnet test is the most important one. Check the SQL Server Error Log for which port SQL Server is currently using, then have them test with that port. Using 1433 or 1434 is not a valid test since that isn't what you are using.

I doubt you'll be able to use dynamic ports with your network setup. I'm pretty sure you are going to have to specify a port and then open that up on the firewall. You'll then need to setup aliases on the client machines.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-05 : 13:47:20

What would be the benefits of using dynamic ports versus specific ports? I'm having some issues lauching the SQL Configuration Manager on that box ATM. I'm going to resolve that and return to this topic.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 13:51:29

The benefit is that you'll be able to punch firewall holes for a specific port and also setup aliases on the client machines.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-05 : 14:04:02

What is a practical use of using dynamic ports? Just security?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 14:08:18

I've never used dynamic ports before. Our network is too restricted to bother with it. We've got firewalls and security galore.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

saint168
Starting Member

1 Post

Posted - 2008-02-20 : 12:32:23

I would like to know if this issue was ever resolved. We are having the exact same problem with one of our client.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-21 : 15:41:22

Ensure sql server browser service is running when use dynamic sql port.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources