Connecting SQL Sentry to a Different Domain
Published On: April 1, 2015
Categories: Plan Explorer, Fragmentation, Performance Analysis 0
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:
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:
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"
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):
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:
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
Shiftwhile right-clicking an executable, you'll now see three distinct "Run as different user" 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
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):
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).
Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com. Aaron's blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.