UKC

This is definitely NOT my area of expertise, and I have asked/paid for advice from elsewhere but I am not convinced I am getting straight answers - so where else to go than the fountainhead of all knowledge?

I want to link 5 shops - each with a Workgroup Lan of, say, 5 PCs, to a central instance of SQL Server and a shared filestore. Now the SQL Server bit I can do fine, using port forwarding, and that works great; but I have tried using Microsoft PC based VPN clients to connect to the server, and then map drives to the shared files, and although I can make it work it is very unreliable: sometimes the VPNs won't connect, sometimes the VPNs are connected but one or more of the mapped drives have disconnected, and so on. In short, the users don't get the experience that the shared files are as accessible as their local ones.

My questions are these: 1) Is it intrinsically flaky to attach multiple PCs on a LAN via individual VPNs - is this what the facility was in fact designed for, and 2) Am I right in thinking that a router to router VPN would be a much better way to go, even if a little bit trickier to set up in the first place?

Both closed questions I know, so do feel free to support your answers with a bit more detail! Any help gratefully received.

In reply to Rob Exile Ward:

This is possibly way too annoyingly complex to go into detail about here as there as so many "factors". This however might be a good jumping off point for you for some things to consider.http://stackoverflow.com/questions/669372/not-connecting-to-sql-server-over... (I googled some of the most common root causes that might be giving you problems and this neat description came up. It might well be something else entirely giving you problems though)

Would help more but heading out for the good weather in an hour

In reply to Rob Exile Ward:

It sounds like you're be much better off renting a VPS and chucking your SQL server on that, then connecting the 5 networks to it individually. This is a weird way to use a VPN.

In reply to Rob Exile Ward:
Router to router VPN would be more stable.
You'll need a static public IP at each location.
You'll need a router that will support LAN to LAN VPNs.

Bear in mind that windows file sharing works badly over high latency networks such as ADSL, even if you have lots of bandwidth.

In reply to Rob Exile Ward:
Site to site VPN is to be preferred. So if I've understood correctly you have 5 shop LANs, each containing 5 PCs each. Plus you have your SQL Server in your office and or data centre. So currently you've setup 25 individual VPN connections from 5 separate LANs to the LAN your Sql server is in.

You can often run into problems if you have IP overlap across your LANs. The clients will be pointed to the IP of your server within its local LAN, plus it's shared drive. If it overlaps a potential IP address in the shop LANs then the routing will be flaky. When you shared the drive it should have been to the userid used by each client to establish the vpn, not the userid they log in with. Have you setup 25 sets if credentials, or are they sharing the same identity? This generally is why you do a site to site as your just dealing with one Vpn client identity then (or potentially 5). Note netbios doesn't generally work across a Von connection.

Do the PCs have a common workgroup or not? Often having different workgroups can cause problems in this area.

Lastly is the Vpn established through certificates or just userid and password based auth? The former is to be preferred

In reply to Rob Exile Ward:

I've been playing with VPN recently and some issues I've had is that windows XP won't do L2TP.
Also, if the lan for the client has the same subnet as the lan for the VPN server you can get issues resolving IPs.

If you want to use VPN then 5 standard routers which support hardware VPN will do the VPN bit, but the VPN server needs to be able to handle 5 connections.

What about using a cloud service like Owncloud to sync the shared drive data to the 5 shops and just accessing the SQL server via the WAN side of the Server's router using a high port number for obscurity and MAC or IP address filtering on the firewall.

That gets around windows limitation (anything invented since 1990)

In reply to Rob Exile Ward:

Router to router is the way to go. Use a dedicated bit of hardware, like a low end cisco device, that can do an encrypted (SSL/TLS) tunnel between sites. The sub sites would all connect to a central site, where the SQL and file store are located. Local traffic between PC's stays on the local LAN. Any traffic to the file or SQL server is routed over the VPN. Far more reliable than having lots of PC's running VPNs.

If it's shops and you're taking card payments then there are lots of rules about shipping or storing card data (PCI DSS). You don't want to go down that route and have audits etc. So keep any card traffic local and on a physically separate network.

As others have mentioned, look into hosting the file server and/or SQL server with a cloud provider. That should provide you with backups and hardware resilience (I take it that these are important servers), as well as a lower latency connection. If you only use them from 8am - 6pm, it may even work out cheaper than hosting a physical server(s) yourself.

Another option might be to host a local file and/or SQL server at each sub site and keep data in sync using replication. Then if your central site goes down each local site will continue to function. You would only need a VPN to sync data amongst the servers not from multiple PC's.

It's a common problem with many solutions. All of which vary in complexity, price and reliability.

In reply to dread-i:

Thanks for all the contributions - seems pretty unanimous. No I have no intention of getting involved with card payments!

Router to router to a VPS it is then.