0

I am setting up a laptop for a customer (Windows 7). He needs to be able to access SQL server on his office network while on the road.

What we are trying to do: Since he will be out of the office, he will need to start his laptop with his local login (computername/Steve). I have a VPN set up, through which he is able to access network drives. However, when I open management studio I am unable to access databases on the office SQL server.

We cannot rely on Remote Desktop as he will at times be working without an internet connection.

The bottom line: We need to be able to modify a local copy of a database. Then merge those changes via Sync Center once connected to the VPN while using the local computer login.

Is there any way to get this to work?

P.S. I have tried this with the firewall on both the laptop and the server turned off. So it is not a firewall blockage.

Thank you

2 Answers 2

0

I'm guessing that the problem here is that you are using Windows Authentication for his Sql Server account.

To fix this, have him use his domain login, even when on the road. Windows has this wonder feature called "Cached Credentials" (the linked article is for Server 2003, but still mainly accurate), so that he will still be able to login, even if the domain is not available. Now, when he tries to connect to sql server the Windows Authentication will work.

It may also be the vpn. Some vpns require you to make specific resources on the protected network available, rather than just adding the remote machine as a full member of the network.

1
  • 1
    The problem is not really one of connectivity. If he has no internet access he won't be able to connect to the remote SQL instance (just as he can't connect via RDP when there's no internet connection). The problem (or challenge rather) as I surmise it is how to make changes to a local SQL database and have those changes "merged" to the remote SQL database when connectivity is established, which is a much more complex undertaking... and not a candidate for Sync Center.
    – joeqwerty
    Feb 24, 2012 at 16:08
0

You have a person who's on the road without Internet access, and you want him to have access to a database that's at the office without being connected.

The only way I could see simplifying this is to do a nightly dump of the database, then create a batch file or powershell script that he can run from his laptop when connected to a secured shared drive with that SQL dump that will import it into his local copy of the database.

He'll always be behind in what information is available, but your constraints effectively require him to not be in sync, and there's no way I know of to automagically replicate to his laptop when it just sees him. Not without creating some really roundabout scripting that detects when he logs in and does a dump and import of the database each time.

You don't say what's in the database, but if it's small enough you could do hourly database dumps to that directory and delete dumps of a few hours old, so he can grab one that's more up to date than once a night.

There's no way with these requirements that it's not going to be kludgey and not-so-pretty...

If you're having him change things on his laptop and you want it into the other database, then you'd reverse this.

If you're making changes on both databases,...I'm not sure that can be easily done. You'd essentially be describing what happens with a database cluster that is constantly hitting a split-brain situation. You would need to combine the "log in with cached credentials" answer (don't log in locally to the laptop) with research into setting up and licensing SQL server (Is it MSSQL?) or configuring MySQL or whatever you're using as a cluster that looks for his laptop, and how to resolve split brain errors in a cluster so it periodically resyncs with his laptop. But this will be asking, in my opinion, for possible errors and management headaches, as I've not heard of anyone trying something quite like intentionally keeping part of a cluster offline all the time, plus it will give you a lot of errors in the logs as your cluster will be "broken" all the time.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .