Solved

TOUGH ONE: ODBC connection over VPN tunnel on 2 seperate domains!

Posted on 2010-09-16
5
940 Views
Last Modified: 2012-06-27
Hey guys - this is going to be a tough one i'm pretty sure :p

Here's the situation - we have two different sites - each with a different windows domain.  We have a SQL server on domain 1.  Then on domain 2, there is a computer that's not a member of either domain (it's just in a workgroup).  There is a VPN tunnel connecting the two sites together.  DNS doesn't work across the VPN tunnel, but i have edited the hosts file on the computer, and so i can ping the SQL server (across the VPN tunnel) by both hostname or by IP address.

I've tried setting up an ODBC connection to the SQL server using both IP address, hostname, the works.  I get the error that the SQL server doesn't exist, or access denied.  i've tried both TCP/IP, and Named Pipes, but neither work.

SQL server is running SQL 2005 standard, with all patches and updates.


CLIFF-NOTES VERSION:  VPN tunnel between two sites, and 2 domains.  One workstation (not on either domain) trying to connect to SQL server on domain across tunnel to set up ODBC.  it can ping the server, RDP to it, but can't set up ODBC connection.
0
Comment
Question by:Mystical_Ice
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:tfittsy
ID: 33697635
Is your SQL server set up to allow connections frmo that IP address?  Is the client able to supply the credentials that the SQL server needs when it is not part of a domain?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33697743
How quickly do you get the SQL Server does not exist or Access is Denied?

If it's quick, it's Access Denied.  
If it takes several seconds, it's SQL Server does not exist (or cannot be contacted).

Try using telnet to access SQL Server on the port it is listening on (1433 by default for single instance machines).

Verify via tracert that the traffic is in fact going over your VPN link.

"tracert NAMEOFSQLSERVER

Have your VPN administrator verify that port 1433 is open over the VPN link.
0
 

Author Comment

by:Mystical_Ice
ID: 33698183
Thanks for the answers - to answer your questions:

it takes a few seconds, so I figured it couldn't contact it, and wasn't a permission denied issue.  Also the SQL logs show that there are no requests or access denieds, so nothing was even getting to the SQL box.

I had tried accessing the SQL machine's sql port - 1433 - and wasn't able to, BUT THEN i just tried to telnet to the default port from a machine on its same domain, subnet, etc. and wasn't able to.  That's when i researched and figured out (as you mentioned) that NAMED instances (this is a named SQL instance) use a random port.  I researched a bit and found out how to find what port it was.  They said to dig through registry keys to find it, but i had a better idea - go to command prompt and type "netstat", to see which ports are open on the server.  I found out - port 2612.  Tried telnetting to it from another computer and it worked.

I am the VPN administrator, and all ports are open over the VPN link =P

I'm not at the client having the problem at the moment (and have no access to it right now), but tomorrow morning i will be, so will try it then.  Should work if i select "TCP/IP', and then pick the port in ODBC, right?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33698192
You don't want to specify the port unless you set a static part (even if it's not 1433).  If you are using dynamic ports, it can (and likely will) change with each service start.  Just because it's a named instance, doesn't mean it HAS to be a dynamic port.  Typically when you install a named instance, it's because the default instance is already installed.  You can always enable a static port.
0
 

Accepted Solution

by:
Mystical_Ice earned 0 total points
ID: 33769456
I could never get this to work in the end, so ended up just connecting to a computer on the remote end via terminal services.  Figured pulling SQL data over a (slow) VPN connection wasn't best practice anyway
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now