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.
Using VPN and RUNAS to Connect to Remote SQL Server: http://wp.me/pvLke-48j
@sqlagentman nice but doesn’t the Windows auth user in the connection dialog show as the local user, even though you’re connected as remote?
It does show the vpn login’s credentials in 2008 at least.
@sqlagentman Oo, thank you or reminding me I need to write a somewhat similar post for a different audience.
When I do this, it also shows me the local user, not the runas user.
I’ve taken to using the sysinternals tool ShellRunAs for this purpose — individual shortcuts can get cumbersome if you have multiple apps (VS 2008, 2010, etc) and multiple domains (we have three).
I like your article (and dmv book btw), but there’s simpler solution – hold shift, right click on icon of ssms/shortcut/any app and there is ‘Run as different user’
Agreed. That is indeed an option as well.
I usually use the “/netonly” option instead of “/noprofile”. When I used noprofile, it caused all sorts of havoc with tools I had installed under my local profile. The “/netonly” option tells the runas command to just use those creds for permissions, but lets me access all of my local settings in my current profile. That saved me a lot of grief trying to connect to a variety of boxes.
Much simpler to use the Windows Credential manager. (On Win7 it’s Control Panel, User Accounts, Credential Manager, but on any version of Windows you can use cmd.exe control keymgr.dll).
Enter the server name or IP address and your remote domain credentials. Run SMSS as normal and connect to the target using Windows Authentication. The stored credentials will be used instead of your local ones. Basically the same as use RUNAS /NETONLY.
If SQL Server is running on nonstandard ports, you need to put the port number on the end of the host name/ip using this format : (not comma like SSMS uses).
I usually just Login to my Laptop using my “private credentials” and VPN myself into our work Network using my “work credentials”. For me there is no Need to use “runas” when starting SSMS, I just start SSMS and our Servers recognize me as my “work self” (my work credentials). Are you sure the “runas” is really needed as soon as you’re vpn’ed into work Location?
Ah, but think of the contractor or vendor support person who is logged into their workstation under their company credentials but are issued a different login for the client they’re working with. That domain would have no understanding of the domain of the contractor’s company. This is what the article addressed.
To be honest, I don’t understand it. I am logged in to my Notebook using a local user account. Thus I am not logged into our work Domain. As soon as I create a VPN Connection to work, the SQL Servers at work recognize my under my work credentials. Isn’t that an analogy to the vendor Support Person you are talking about?
My Laptop is part of the Windows Domain at work though – is this part of the reason why I am recognized when using the VPN Connection?
Wilfried,
Some VPN’s automatically forward your remote domain credentials when you access a server over the VPN. This seems to be what is happening in this case. I assume they create a temporary entry in the windows credential manager. This only works with VPN’s which use your remote domain credentials to authenticate the connection. If the VPN uses some other source of authentication (RSA tokens or a RADIUS server not connected to Active Directory) then the VPN can’t do this for you. This tip is aimed at users of those VPN’s.
Thanks for the explanation!