Solved

Problem connecting to MS SQL server via VPN

Posted on 2016-08-28
10
153 Views
Last Modified: 2016-08-29
We have a VPN that we connect to via Remote Desktop (address format xxx.xxx.xxx.xxx:xxxx).  When connected to the remote server we uses ms sql 2008 r2.  We wanted to know if we can use our local ms sql 2014 to connect to another sql instance via that same vpn.  What we have doen the following in our ms sql 2014:

Just the address,           xxx.xxx.xxx.xxx
Address\InstanceName,       xxx.xxx.xxx.xxx:xxxx\InstanceName
Address\(local),            xxx.xxx.xxx.xxx:xxxx\(local)
Address-port,               xxx.xxx.xxx.xxx:xxxx   - (THIS IS HOW WE CONNECT TO VPN)
Address-port\InstanceName,  xxx.xxx.xxx.xxx:xxxx\InstanceName
Address-port\(local),       xxx.xxx.xxx.xxx:xxxx\(local)

Open in new window

0
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 78

Expert Comment

by:arnold
ID: 41774229
You have to know what restrictions exist on the Vpn meaning the access might be limited to the remote server (RDP) and no other access permitted via the VPN.

See if you can after establishing the VPN, use ssms to see if you can connect to the ip\instance of the remote SQL.
Telnet remoteip port1433 or it can be anything else configured.....
0
 

Author Comment

by:rayluvs
ID: 41774245
Yes, we'll check on those restriction tomorrow.

As to "connect to the ip\instance of the remote SQL", that's line 5 (already tried it)

Telnet remoteip port1433 failed, but the port is the correct one.  Ran a script at the remote, and returned 1433

2016-08-24 16:59:24.690	Server	Server is listening on [ 'any' <ipv6> 1433].
2016-08-24 16:59:24.690	Server	Server is listening on [ 'any' <ipv4> 1433].
2016-08-24 16:59:24.700	Server	Server is listening on [ ::1 <ipv6> 1434].
2016-08-24 16:59:24.700	Server	Server is listening on [ 127.0.0.1 <ipv4> 1434].

Open in new window

0
 
LVL 78

Expert Comment

by:arnold
ID: 41774266
When the VPN is established run
netstat -rn to see the routing table.
See whether the ip of the SQL is part of the included ip ranges known on your system.
Let's say your local lan is 192.168.0.0/24
The SQL server is 172.16.0 2
VPN ip 10.0.0.3

Your routing table
0.0.0.0 0.0.0.0 192.168.0.2
10.0.0.3 255.255.255.255 ...
10.0.0.0 255.0.0.0  10.0.0.3
172.16.0.0 255.255.255.0 10.0.0.3

Etc,,.
You could try if the SQL segment is not in the routing table..
route 172.16.0,0 mask 255.255.255.0 10.0,0.3
This will add the routing entry, but would require that the remote site has a path from the SQL segment to the VPN segment for this to work.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:rayluvs
ID: 41774270
Thanx, just tried it; it's not in the routing table.

We'll check tomorrow.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 41774370
Even if you get the VPN to route to MSSQL, only the VPN client machine (nothing else on the same LAN) is able to use that - assuming you connect with a VPN client.
0
 

Author Comment

by:rayluvs
ID: 41774621
Assuming when you say VPN client machine you are referring to us (our pc connecting to that VPN address)?

Reading again your entry in 41774266, we didn't check if the SQL was part of it (instead we were looking for the VPN address, which wasn't there).

We will check again when we get to the PC.

Question, what exactly should we ask the admin of the PC we are trying to connect it's SQL instance? (So he/she can tell us with clarity the what-to-do)
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 41774633
The VPN Client machine is the one initiating the connection (and using RDP). If this is the same machine which has the MSSQL 2014 instance, fine. If not, you can't use the client's connection from a different machine, the MSSQL machine has to run its own VPN client connection. However, having more than one VPN connection from the same public IP (yours) causes failures or unstable connections.

To stop confusing us please use different symbolic addresses for public and private addresses, and for each side (making it at least 4 addresses).
Also tell us which kind of VPN you use: Did you have to install a VPN Client, then tell us its name (e.g. "Cisco AnyConnect Secure Mobility Client"), and if you know the type (PPTP, SSL, IPsec, ...). This helps us to give advice on what to look for, and maybe change on your side.
0
 

Author Comment

by:rayluvs
ID: 41774668
Gotta tell u, each answer u give us even clears more our understanding!  Great info, thanx!!

As to comments,

- we are the the one initiating the connection with Windows Remote Desktop (so we are the VPN client)
- our machine isn't the PC that have the MSSQL we want to connect to.  The remote address we connect to has the instance we want to connect to.
- our PC has SQL2014 and we want to use this apps to connect to the remote address SQL (which is version 2008/r2)
- what we want to do is the same as when a SQL instance is found with our local LAN, we use our SQL2014 apps and connect to that local (other) SQL instance by pc-name\instance.  We wants to do this but with the remote address.

We understand that the remote address we connect to is a private address; it's only one.  When we write xxx.xxx.xxx.xxx we mean only one address (hope this is your question on this part).

We didn't install no VPN client installed on our PC.  We were given one (1) remote address and  just connect to that remote address via Windows Remote Desktop (no other apps).

Hope this clears ups bit on our end.

As to what we going to ask the admin on the remote address, is "what configuration do we need to do at our end in order to connect directly to your SQL2008 r2 instance?"
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 41775134
What do you mean by "MSSQL 2014 apps", exactly? I read it as you want to connect from your local MSSQL 2014 server instance to the remote MSSQL 2008(r2) one.

If I understand
We didn't install no VPN client installed on our PC.  We were given one (1) remote address and  just connect to that remote address via Windows Remote Desktop (no other apps).
correctly, you are not using a VPN. You use RDP against a public IP address. If so, then no, "you cannot do that". RDP only transports RDP, i.e. the graphic session info and keyboard/mouse data (plus some security related stuff and some sharing options for mapping local drives and printers remotely). That connection is not able to tunnel anything else.
You would need a direct MSSQL connection (usually port 1433) - but hell no, this will not happen if the remote site's admins are in their senses, as the connection is unsecured and unencrypted.

You can ask for getting VPN access, but since the MSSQL server needs to have that connection always-on, it might get tricky and/or flaky.

Bad news for you, I know. To change it, significant effort and knowledge is required on both sides. A simple PPTP VPN can work well and from start with Windows OS, but there are many pitfalls, and the path might by a long one ...
0
 

Author Comment

by:rayluvs
ID: 41775729
Thank you very much! We feel very informed and have discussed our situation with the IT and it will be attended!

Thanx
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to set-up an On Demand, IPSec, Site to SIte, VPN from a Draytek Vigor Router to a Cyberoam UTM Appliance. A concise guide to the settings required on both devices
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

728 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