Create SSMS shortcuts with Remote Desktop Manager

THIS ARTICLE WRITTEN BY SEAN O'SULLIVAN, IT MANAGER, IS PART OF OUR GUEST BLOG SERIES. PLEASE CONTACT US IF YOU WOULD LIKE TO BE FEATURED ON OUR BLOG.

Remote Desktop Manager (RDM) is far more capable than its name might first suggest. As well as using it at work to manage all of our endpoints, my department has it managing passwords, online resources, firewalls and switches across multiple domains. This is made possible by RDM’s robust credential management and wide variety of entry types.

Some time ago I wondered if I could consolidate even more into RDM’s consolidated view of my digital world. In particular, I look after a lot of SQL Server instances and need to log in via SQL Server Management Studio every so often. As the credentials were already in RDM, could I have RDM fire off SSMS connections with a straightforward double click?

The answer was, of course, yes.

In this article I will show you how to make use of Remote Desktop Manager’s Command Line entry type and its useful variable functionality for a true single sign on experience. To begin with I’ll show you how to use SSMS with integrated authentication, thereby authenticating to SQL Server with Windows credentials, as it’s the easier method to get going. Next I’ll show you how to authenticate with SQL Server logins using credentials already stored in RDM.

Before we get started though, you will need to have a recent version of Remote Desktop Manager installed as well as a copy of Microsoft’s SQL Server Management Studio. You can use any version of Management Studio that supports the switches in use, which is from version 2008 R2 and above.

SSMS with Integrated authentication

From within Remote Desktop Manager, right click the Navigation pane, search for Command Line (External Application) and press OK.

Let’s enter the connection parameters necessary to make this work. Using the next screenshot as a useful guide, give your entry a name, then provide the full directory path to SQL Server Management Studio inside the Run field. (In my case, that’s

“C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe”).

You should also check the Use Default Working Directory option beneath.

Now we’re going to provide some switches so Management Studio knows what to load and how to log in. First, place your cursor at the end of the SSMS directory path.

Next, either:

1.  Provide the SQL Server instance path in-line, like this:
   

“C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe” -S SQL01\Example,2017 -E

Or:

2.  If you’ve named the entry as the SQL Server instance (like I’ve done in the screenshot), you can use the RDM $NAME$ variable:
   

“C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe” -S $NAME$ -E

Save and double click the new entry in the RDM Navigator. You should see SQL Server Management Studio fire up and log straight into the instance without any further action.

SSMS with SQL Server login authentication

If you do not, or cannot, log in to a SQL Server instance with your Windows credentials, you likely have a SQL Server login instead. This particular method employs the same steps as I showed previously, but adds a credential entry as well.

Go ahead and set up the command line entry as before, but then add a new Username/Password credential. To keep things simpler here, I set its name as the SQL Server login ID, suffixed by the SQL Server instance. Enter the username and password and then select the Advanced tab on the left hand side. It’s crucial that you check the box to Allow password in variable, otherwise this won’t work.

With the credential now in Remote Desktop Manager, return to the SSMS Command Line entry you added earlier and open its Properties.

From the Credentials drop down, select Credential Repository and pick the Username/Password you just set up. Then, in the Run field you need to alter the SSMS switches a little further. My example below will use the name of the entry for the instance, followed by RDM variables for the username and password.

"C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe" -S $NAME$ -U $USERNAME$ -P $PASSWORD$

That’s all there is to it. To add more SQL Server instances you can use RDM’s Duplicate feature and change the name and credential preferences. Don’t forget you can exploit RDM’s security features to restrict who is able to execute, edit or even see these in your database so they can be provided only to those that need to manage SQL Server in your team.

Have fun!