Go to Top

Running Access locally on a different domain using VPN

We develop solutions using Access on the desktop and SQL Server on the network for clients through out the US. Our standard method is to login to the client’s network via VPN and program on their server, since we couldn’t program on our development machines using the credentials provided by our client. They would clash with our own domain and it was not feasible.  Until now.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

VPN Tunnel has it’s limitations

In the past, when we use VPN to our client’s network, we’ve also asked them to allow Remote Desktop Connections to one of their servers. We would then use Access and SQL Server Management Studio, (SSMS), on their machine to make changes to their software and roll out updates. For the most part it works, but we hate not developing on our souped up machines that are also running our specialized tools, such as the ones provided from FMS Software. Recently we’ve found a better way.

Passing your windows credentials using RunAs

To open both SSMS, (SQL Server Management Studio), and Access, use RunAs, a command line utility bundled with Window, with the flag /netonly. It allows you to remotely access a domain and thus grant you the needed credentials to login to the SQL database as a member of the remote domain rather than your own computer’s login.

Here’s the syntax for SSMS:

… for Microsoft Access:

(adjust the paths as needed depending on your environment & configuration)

When you use RunAs command, it’ll prompt you for the password, use the domain password of the user you’re going to log in as. When you succeed, you’ll be opening the program as this user, rather than as yourself.

A red herring

When you open the SSMS, it usually shows a log in dialog and when Windows authentication is selected, it’ll show you the current username. Instead of showing you the logged-in user, it’ll show as original user which _looks_ incorrect but you can safely ignore that inconsistency, click “Connect”.

For those paranoid about the identity, you can verify after connecting by querying “SELECT SYSTEM_USER;” which will then return the logged in user you passed in the RunAs.

Being able to run Access locally is a major improvement for us and we hope others find this useful for their needs. We love comments, so please let us know if this worked for you.

2 Responses to "Running Access locally on a different domain using VPN"

  • 10 things every Access developer who works with SQL should know | accessexperts.com
    May 14, 2013 - 9:34 pm Reply

    […] a pure Access solution, (no SQL Server), is almost impossible over VPN, but not a problem at all with SQL Server as your […]

  • Luke Chung
    November 24, 2011 - 2:06 am Reply

    Nice article on a common need for those of us serving organizations remotely. Thanks also for mentioning our products!

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.