Solved

Remotely accessing database

Posted on 2015-01-09
8
38 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
  • 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
 
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 46

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 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

20 Experts available now in Live!

Get 1:1 Help Now