Solved

Remotely accessing database

Posted on 2015-01-09
8
41 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
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 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 48

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

828 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