Using VPN Connection and RUNAS to Connect to Remote SQL Server
This is Not the Right Way to Bridge the Gap
This is for all those contractors and third-party solution technical support folks out there. Database Administrators really don’t want to install SQL Server Management Studio on a workstation for you to “remote-into” in order to connect to SQL for support reasons. System Administrators don’t want this either because it could mean needing to set up a new workstation just so you can run SSMS. Management definitely doesn’t want this either because it rings the Microsoft Licensing Cash Register with a big Ca-CHING for a new Windows license as well.
So please stop asking for this.
Obviously we’re also not going to give you the ability to “remote-into” a production SQL Server to do likewise just because SSMS is loaded there. It draws resources away from SQL Server and exposes the server and the SQL instance to a host of security threats.
So please stop asking for this.
Don’t take us saying “No” as a sign I don’t love you though. I do. Many folks I know are DBAs by day and Contractors by night. I also can’t think of a single Enterprise DBA that would choose supporting third-party databases they know nothing of over allowing the vendors’ own qualified* and informed* staff to support. DBAs are not saying “No” to access, but rather how you wish to access our environments. There is a better way that you may not know of and odds are you already have the ability to take advantage of it.
I don’t want to faceplant against my firewall. I don’t want to build a new bridge to cross to our domain when a perfectly good one already exists. No, I want to show you how to be nimble and use the tools and access you already have. I want to show you a better way. But first I need you to just listen up and DUCK!
Teach a Person to Fish
I’m going on the assumption that you, the vendor/remote contractor already have a VPN connection in place for the customer. Otherwise setting up a dedicated workstation for launching SSMS from is the least of your problems. VPN is the horse you need to place before this cart of which we talk.
That being said we will move right on to the other items you’ll need in order to get connected without assistance of the customer’s DBA or System Engineer. Those items are:
- SQL Server Management Studio installed on your workstation/laptop. (Yes, the one you are using to read this post or that you play Minecraft on after work or perhaps surf questionable sites on at home once the kids go to bed.) The workstation/laptop you use daily that sits on the desk in front of you and is not on the customer’s domain. The one used to VPN into their network when called upon to serve.
- An entry in this system’s path variable that points to the containing folder for Ssms.exe.
- A valid AD-based login on the target SQL Server instance with an associated user in the database you’re connecting to at the customer’s site.
You’ll get the idea now that I’m targeting this discussion to use of AD/Trusted connections for the security benefits offered over SQL Authentication. While this process is simpler for a SQL login/password I’m not advocating use of weakened security measures. You’re welcome to figure this out on your own if you are sticking to your guns on such a methodology. You’re smart enough to figure it out.**
Here we go…
Install Management Studio on Your Workstation
This is a given. If you already don’t have SSMS take the time to download it. You’re smart and sexy. You can do it. I have faith in you.
The Path Variable
The first thing you’ll need to do, assuming the VPN connection is taken care of and the login you’re using to VPN in as also has an associated login and user on the SQL instance (since that would be a need even if you had a remote workstation to remote-into) is confirming that the path system variable on your workstation/laptop has an entry for the folder that hosts Ssms.exe (the executable for SQL Server Management Studio.) The instructions I’m about to provide are based upon the most-adopted version of Microsoft Windows currently: Windows 7. Alternate methods exist in a slightly different process for other versions of Windows.
- From the Start menu in Windows right click on Computer and select Properties.
- Select the Advanced system settings tab.
- Click the Environment Variables button.
- Select Path in the System variables list and click the Edit button.
- Search for the path for Ssms.exe in the value that is set for the path variable. I find it easiest to do this by dropping the contents into Notepad first and doing a Ctl+F if the path variable value is long, which it can be. If you don’t know the location for Ssms.exe the easiest way to determine it is to locate SQL Server Management Studio in your Start menu, right clicking on the shortcut and selecting Properties and looking at the Target value.
- If the value for the Ssms.exe path is not in the path variable then add it (don’t include the file name) and save your changes. This will allow you to just specify “Ssms.exe” in the runas command we’re going to create next. An example before and after is listed below using the default install path for Ssms.exe for SQL Server 2012.
Example: path variable before:
C:\Program Files\Intel\WiFi\bin\;C:Program Files\Common Files\Intel\WirelessCommon\
Example: path variable after:
C:\Program Files\Intel\WiFi\bin\;C:Program Files\Common Files\Intel\WirelessCommon\;C:Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio
Go For It!
VPN to your customer’s domain as you normally would. At this point you’re ready to leverage your local install of SQL Server Management Studio to connect remotely to the customer’s SQL instance. After authenticating to the customer domain you’ll need to do one of two things: launch cmd and run a command line runas command every time you connect in this fashion or build a shortcut for reuse. Guess which one I advocate.
The Runas Command Shortcut
You’re not going to be logging into your workstation/laptop with the same security context that you VPN into the customer site. Therefore we need to set up the ability for SSMS to run with an AD context that matches your VPN login rather than your local workstation login. I’m going to show you how to do this at a Command Prompt first so that you can easily take that command and convert it to a shortcut you can launch from your desktop or from a shortcut pinned to your Start menu or Task bar.
- Launch Cmd on your workstation/laptop.
- Type the following command, replacing <domain\login> with the valid domain\login you use to VPN into the customer’s network and for which rights have been granted in the target SQL Server Instance.Database:
- runas /noprofile /user:<domain\login> “Ssms.exe”
What will happen when you hit Return, if you’ve followed my instructions so far, is that you’ll be prompted for the password for the domain\login supplied in the runas command. Once authenticated SQL Server Management Studio will launch and you’ll see that you’re authenticating as the domain\login from the VPN session. You’ve now successfully connected to the database you need to support remotely without the need to add a remote workstation or increase licensing in any fashion. Now it’s time to finish this process off for good by building a shortcut so you don’t need to run the command line statement each time you need to launch SSMS for this customer. I’m sure by now, in all your years of using Microsoft Windows, that you’ve created shortcuts but I’ll go through the motions here just in case.
- Copy the command line runas statement you’ve already validated that works above.
- Right click your desktop and select New\Shortcut from the context menu that opens.
- Paste the runas command: runas /noprofile /user:<domain\login> “Ssms.exe” (using the correct domain\login in place of <domain\login> of course).
- Click Next
- Name the Shortcut.
- Save the Shortcut.
You can now leave the shortcut on the desktop or pin it to the Start menu or Task bar for easy access and reuse.
Look at What You Done Did!
You’re able to remote to the customer SQL database for support without any additional effort from the customer and very light work on your part. There are such things as better mousetraps. You’ve just built one.
*Yes, I know that these terms are highly subjective in this context. Your emails and comments are appreciated.
**Perhaps you’re not, if you’re mandating or relying on SQL Authentication over a more secure security construct.