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.
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:
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 original user which _looks_ incorrect but you can safely ignore that inconsistency, click «Connect».
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.