Connecting SQL Sentry to a Different Domain

Access denied

Occasionally, you may need to connect the SQL Sentry Client to a SQL Sentry database in another domain. If you don't have a SQL authentication login for that server, you will need Windows Authentication, using a remote domain account. But without a trust, you won't be able to use the credentials from your local workstation, whether you're in a completely different domain or you're not even on a domain.

Personally, I work out of my home, and my Windows machines are all standalone workstations running as VMs, so they are never joined to the domain and are running under their own local workgroups. But I often need a way to connect to servers over the VPN into our office, our data center, or a customer's environment. It pains me to admit that I've seen the above error dialog more times than I can remember.

One workaround is to RDP into a remote server, and use the client from there, but that is not always possible. And even when it is possible, it is not always optimal. In my career I've gone back and forth about what I like less - waiting for data to appear in my local application or waiting for remote video redraw and click response.

In cases where RDP is impossible or not desired, a better workaround is to use RunAs (see the TechNet documentation here and here). For the SQL Sentry client, you could run the following from the command prompt (note that your path to the SQL Sentry Client may be different, and of course your domain is probably not called INTERCERVE, nor is it likely that your username is abertrand):

runas /netonly /noprofile /user:INTERCERVE\abertrand "C:\Program Files\SQL Sentry\8.0\SQL Sentry Client.exe"

And then you would be prompted for your domain password. Type the password (you won't see any of your keystrokes displayed on the screen), then hit Enter, and it will launch the SQL Sentry Client connection dialog:

Using RunAs from the command prompt

Stick with Windows Authentication here, enter the IP/server and instance name, and click Connect. Now you have a SQL Sentry Client connected to the repository in your remote domain! Note that the title bar will make it look like you're using your local account, but the underlying authentication is really using the remote credentials. Here I am connected from Rhode Island to a SQL Sentry database in our 10.65 network, over our VPN, and using my remote domain account, but the title bar shows my local Windows login:

SQL Sentry Client titlebar

Note that this also works for SQL Sentry Plan Explorer, and that the local Windows account will be reflected in all applications using RunAs (and just as an aside, this is a fault in Windows, not in the applications):

runas /netonly /noprofile /user:INTERCERVE\abertrand "C:\Program Files\SQL Sentry\SQL Sentry Plan Explorer\SQL Sentry Plan Explorer.exe"

The tab for my current session shows my local credential, but I know that it used the domain account I specified on the command line, since my query was successful, that domain and account do not exist remotely:

Plan Explorer indicates the local Windows account, but I know that's not right
Plan Explorer indicates the local Windows account, but I know that's not right

Even Management Studio shows it this way - for example when you use this command line to connect:

runas /netonly /noprofile /user:INTERCERVE\abertrand "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"

The connection dialog will show your local Windows account (and you can't change it):

SSMS connection dialog

But don't worry, we can prove that the underlying connection is actually made with the domain account you entered at the command line, even though the status bar (and the top-level node in Object Explorer) will show your local Windows credentials:

SSMS also shows the local Windows account, but we can prove that's inaccurate
SSMS also shows the local Windows account, but we can prove that's inaccurate.

I've been using this trick for SSMS for years - I've posted several answers on Stack Exchange about RunAs, including this one and this one; I also wrote a tip about it here. That tip demonstrates a different approach as well - using a Windows Credential to launch SSMS with a remote domain account - which should also work for any other application, including ours.

There are other approaches too to help simplify this, so you don't have to constantly be at the command line. For example, SysInternals has had ShellRunAs for a long time. Download the executable, extract it to a folder, and then run the following in a command prompt set to the extracted folder:

shellrunas /reg
shellrunas /regnetonly

After you've registered those context menu handlers, in Windows 10 (and at least as far back as Windows 7 / 2008 R2), if you hold Shift while right-clicking an executable, you'll now see three distinct "Run as different user" options:

ShellRunAs context menu options

(If you don't use Shift, you'll only see the two menu options from ShellRunAs.)

For this specific technique, you want the middle one with (netonly) in the name. (The other two only check the credentials locally, so they won't be useful. The ShellRunAs option - you can distinguish it from the native option because it is the one with the ellipses (...) - is by design only checking locally. I couldn't figure out a way to make the native approach use /netonly.)

This will pop up a relatively familiar Windows Security dialog, which is really the only difference between this approach and the runas approach above (you don't have to type the username/password at the command prompt):

ShellRunAs Windows Security dialog

After that, everything else should work the same - you'll be prompted to enter the SQL Sentry database server name, and leave Windows Authentication checked, because your remote domain credentials will be passed (in spite of anything that makes it look like your local credentials are being used).

THWACK - Symbolize TM, R, and C