Solved

Remotely accessing database

Posted on 2015-01-09
8
42 Views
Last Modified: 2015-03-05
I've found varying articles on the web and none so far have been satisfactory and I haven't been able to get it to work. I'll explain what I have:

Hosted standalone Windows 2008 R2 server
SQL Server 2008 R2
SSRS
CRM Software

The above server is running without a problem and has been for a couple of years. It is not part of a domain. The CRM system is web facing and the customer accesses it without a problem. It connects to its own local database on the same server. I control that server and I have RDP access to it.

On the server is SSRS. It is not internet facing. It is accessible on the server only, which is fine and I'm happy with leaving it that way.

The customer has a need to get direct remote access to the database on the server. I don't want to open SQL to the world so, I have one option as far as I can tell. I can enable VPN access to the server so that the customer can load SQL Server Management Studio on their local computer and with an active VPN it will allow them to see the database server and connect to the database.

The customer doesn't wish to buy any VPN software. I don't want them to have RDP access to the server. I have set up a Windows VPN on the server which seems to allow me to connect, but when I load SSMS on my local machine, I cannot connect to SQL. What do I need to do (with instructions) to enable the customer to connect to the VPN and allow them to directly access the database from their side?

Thanks.
0
Comment
Question by:Lee Savidge
[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
  • 2
8 Comments
 
LVL 17

Accepted Solution

by:
Chris Millard earned 500 total points
ID: 40539951
Does the customer NEED to buy VPN software? Windows desktop software has the ability to connect to a VPN already built-in.

However, another option - if the customer has a static IP, you could open SQL up and set the firewall to allow SQL traffic only from the customers IP
0
 
LVL 25

Author Comment

by:Lee Savidge
ID: 40539954
As I said, I have set up a Windows VPN already which seems to connect ok, but it doesn't allow me to see the SQL database from my local machine.

The customer has developers all over the place so allowing through the firewall and restricting the IP isn't an option.

Thanks.
0
 
LVL 17

Expert Comment

by:Chris Millard
ID: 40539960
With the VPN connected, I can't see any reason why you shouldn't be able to connect - UNLESS - the VPN account that you are using doesn't have access to that specific database?

Are you connecting to the DB using Windows or SQL authentication?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 25

Author Comment

by:Lee Savidge
ID: 40539985
I'd connect using SQL authentication. I'd have thought I would have been able to see it as well but when I load SSMS and search for network servers, it doesn't find it. Now, I'm scratching around in the dark here because I've never set up a VPN on a server before and I'm not a Windows administrator. I'm a programmer by trade.

Now, in my blind fumbling, could it have anything to do with the IP ranges? The VPN connects with automatic protocol and when I connect it, the config shows:

Device Name: WAN Miniport (PPTP)
Devide Type: vpn
Authentication: MS CHAP V2
Encryption: MPPE 128
Compression: (none)
PPP multilink framing: Off
Client IPv4 address: 169.254.***.***
Server IPv4 address: 169.254.***.***
NAP State: Not NAP-capable
Origin address: (unknown)
Destination address: ***.***.***.***

The client and server IPv4 are both full IPs and different, I've just obscured them. The destination address is the live IP address I use for RDP purposes.

Any thoughts?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40546280
SQL Server Browser service is running on the server?
0
 
LVL 25

Author Comment

by:Lee Savidge
ID: 40548607
Yes. The browser service is running. Still at a loss on this one.
0
 
LVL 25

Author Comment

by:Lee Savidge
ID: 40561672
Surely there is someone that knows how to conenct to a remote hosted server via a VPN so I can connect to SQL over the net?
0
 
LVL 25

Author Closing Comment

by:Lee Savidge
ID: 40646714
Although I left the company where this was a customer requirement, this was what I did in the end despite it not being the option I wanted to use. I eventually found that the customer who had employed an external developer for this part of their system, did have, depsite what they told me, a static IP which meant I could simply restrict the access to the SQL port on the firewall to their IP only.

Thanks.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

738 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