Solved

Remotely accessing database

Posted on 2015-01-09
8
43 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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

695 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