Solved

Remotely accessing database

Posted on 2015-01-09
8
37 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 45

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article I will describe the Detach & Attach 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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

16 Experts available now in Live!

Get 1:1 Help Now