SQL Management Studio, Trusted Connections, and Remote User accounts

I often work for clients that require me to access their systems remotely, and usually through a VPN. Frequently, I use a Remote Desktop (RDP) session to access their resources after I have connected in via their VPN. I don't mind this, but often it is easier to use SQL Server Management Studio (SSMS) on my own desktop rather than remotely. 

This week I am working for a new client and they have created a domain account for me and granted me some permissions in their SQL server instances using a Windows login rather than a SQL standard login (with username and password). 

I tend to prefer Windows logins when connecting to SQL Server, but when you are a remote user, logging in with another domain account, through a VPN, it's kinda tricky to use SSMS locally instead of remoting into another machine on the client network.

When you run SSMS on your own computer, and attempt to connect using a trusted connection to your customer's server, it uses your local account credentials. 

That's not going to work against the remote server (prod-sql.jimmy.local). 

However, Windows allows you to run programs as a different user, and it's through a command called RUNAS

All you need to do is create a new shortcut to launch SSMS using the RUNAS command. 

  1. Find the shortcut in the menu or task bar that you normally use to launch SQL Management Studio. (I put mine on the Taskbar.)
  2. Right-click on the shortcut and choose Properties.
  3. Select the text in the Target textbox and copy. (note: the keyboard shortcut Ctrl-C does not work here; rt-click and choose Copy)
  4. Rt-click on your desktop and choose New-->Shortcut
  5. Paste in the text you selected (again, the keyboard shortcut Ctrl-V doesn't work) and click Next
  6. Give the shortcut a name like "SQL Remote" or something recognizable. I typically use my client's name here. 
  7. Click through to create the shortcut, then rt-click on the shortcut and open its properties page again.
  8. Add the following to the beginning of the Target line:

    RUNAS /netonly /user:<remote domain name>\<remote user name> "C:\Program....

    Replace the <remote domain name> and <remote user name> with your remote network credentials (and don't include the <>).
    Make sure there is a space after the username and that you keep the rest of the target line after that.

When you start SSMS using this shortcut, you will get a prompt for your remote account's password:

After you enter the password, SSMS will start up, and you can connect to your remote server.

I should point out, though, that the connection dialog that SSMS puts up still shows your local Windows account in the connection dialog, which is disconcerting. However, assuming you put in your password correctly, you should be able to connect to the remote SQL instance and it will use your remote account to do so. 

The proof is in what the remote server returns for your user name:

Now you can use your local installation of SQL Server Management Studio to connect to remote servers with trusted connections using a different Windows account. It's pretty handy for me, I hope you find it useful too.

No Comments