We develop solutions using Access on the desktop and SQL Server on the network for clients throughout the US. Our standard method is to log in 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.
VPN Tunnel has its 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 log in to the SQL database as a member of the remote domain rather than your own computer’s login.
Here’s the syntax for SSMS:
runas /user:REMOTEDOMAINuser /netonly ssms
… for Microsoft Access:
runas /user:REMOTEDOMAINuser /netonly "C:Program FilesMicrosoft OfficeOFFICE14MSACCESS.EXE"
(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 an 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.
[…] a pure Access solution, (no SQL Server), is almost impossible over VPN, but not a problem at all with SQL Server as your […]
Nice article on a common need for those of us serving organizations remotely. Thanks also for mentioning our products!