SQL Server Management Studio (SSMS) over VPN
As with most days, today I learned something new. This is a great tip that was passed onto me by one of my clients. Currently, as a contractor, I work for many different companies. Almost always, I have to do some sort of database work involving Microsoft SQL Server over a VPN.
Generally, I am given access to SQL Server using Windows Authentication based on my client’s domain. Until today I always had to log-in into one of their machines using Remote Desktop in order for SQL Server Management Studio (SSMS) to recognize my identity on the client’s domain.
Logging into remote machines is always a pain: getting/setting permission for remote access to the machine, restricted to a single monitor (RDP doesn’t work well on multiple monitors), Alt-Tab doesn’t work like it should… the little annoyances add up.
But no longer… This simple trick will allow you to use your local instance of SSMS:
Solution
-
Find your SQL Server Management Studio EXE. Usually located somewhere like:
C:\Program Files (x86)\Microsoft SQL Server\[version number]\Tools\Binn\ManagementStudio\Ssms.exe
- Create a shortcut to it on the desktop.
- Right click and select properties.
-
Add the following before the Target information:
C:\Windows\System32\runas.exe /user:yourclientsdomainname\yourusername /netonly
- Add
-nosplash
after Ssms.exe after the Target information -
Your Target should look like:
C:\Windows\System32\runas.exe /user:yourclientsdomainname\yourusername /netonly "C:\Program Files (x86)\Microsoft SQL Server\[version number]\Tools\Binn\ManagementStudio\Ssms.exe -nosplash"
- Click OK to save your changes.
- Make sure you are logged into the VPN for the domain your accessing and launch the shortcut.
- You will be prompted for a password at a command prompt.
- Enter you password and hit enter (use the client’s domain password).
- SSMS will launch. Note: Windows authentication might still show as your computers main domain but if you try to connect to a SQL Server instance in your client’s domain you should connect!
The setup is a few steps but once you are up and running it is really simple to start the correct instance.
One Step Further
I love working on the command line, especially since I started using Cmder. So naturally, I turned the shortcut outlined above into a simple batch script that I dropped into Cmder’s bin folder. This gave me access to call it from the command line.
Create a Batch script named remoteSSMS.bat
in the Cmder bin folder and copy/paste the same information into the script as you did for the Shorcut Target information above (changing location of ssms.exe and your domain/username):
C:\Windows\System32\runas.exe /user:yourclientsdomainname\yourusername /netonly "C:\Program Files (x86)\Microsoft SQL Server\[version number]\Tools\Binn\ManagementStudio\Ssms.exe -nosplash"
Now open up Cmder and type remoteSSMS
and you will be prompted for a password and SSMS will open!
Hope you find the trick as helpful as I did.
Comments