Note: There’s an updated version of this tutorial for SSMS 18 available
here.
Introduction
This will be an absolute beginner’s guide on how to get started with creating SQL Server Management Studio 17 (SSMS) extensions. If you don’t know where to begin with creating your own, you’ve come to the right place. It will cover just the basics of setting up your development environment for developing SSMS extensions from scratch.
If you want to skip my sad story and the reason why I created this tutorial, you can go to the Requirements section and jump straight to the project.
Note: This tutorial is created for extending the latest version of SQL Server Management Studio which at the time of this writing is 17.6. The process for extending older versions of SSMS should be similar though.
Background
All I wanted was to create a simple SQL Server Management Studio (SSMS) extension to simplify what was already a simple action that I repeat a couple of times a week. I thought, how hard could it be… it’s just a simple action. You click a button on the menu somewhere and something simple happens. It would be easy and straight forward I thought. It would be a fun project. I know that SSMS extensions exist. Not a lot of them, not as nearly as many as there are available for Visual Studio, but they do exist. There must be some good beginners tutorials out there… there must be a decent documentation somewhere explaining all the bits and pieces, providing some sample code. I would just need to Google around for a bit and I would be on my way in a couple of hours.
Boy, was I wrong! There isn’t anything out there. Documentation about creating SSMS extensions doesn’t exist. The available tutorials are few and far between and unfortunately most of them are too old, explaining the older Add-in system that is no longer supported so unfortunately they are mostly useless. All that exists is just incomplete pieces of knowledge and documented experiences scattered all over the internet.
OK, I thought, back to square one. I know there are a lot of extensions for Visual Studio and I know that SSMS 17 is based on the Visual Studio 2015 Isolated shell. So, maybe the tutorials for Visual Studio extensions would help me to get started. Well, not really. They help once you are up and running, but there is not a single tutorial out there that would explain to me how to get an SSMS extension project going.
Hopefully, for everyone else, this is that tutorial…
As you will see below, the process is far from easy or intuitive and can get really tricky really fast. So let’s start.
Requirements
All you need to get started is to have Visual Studio with the Visual Studio Extensibility Tools installed. This will enable you to use the VSIX Project template when creating new Visual Studio projects. If you have Visual Studio installed but don’t have the extensibility tools, you can add them to your current installation through Programs and Features in Control Panel.
I recommend using Visual Studio 2015 for this because I had issues with references and dependencies using Visual Studio 2017. Because SSMS 17.X is based on the Visual Studio 2015 Isolated shell, I figured this is probably a more compatible version for this type of project and decided to stick to it. If you are an experienced user though, you can probably get away with Visual Studio 2017.
File > New Project
To start with our extension, we’ll first start with creating a simple Visual Studio extension and then port it to SSMS. To create a new Visual Studio Extension, first go to File > New > Project… and then select Installed > Templates > Visual C# > Extensibility > VSIX Project and name it HelloWorldSsmsExtension.
To give some functionality to our extension, we’re going to right-click our project in the Solution Explorer, select Add > New Item… and then select Visual C# Items > Extensibility > VSPackage > Custom Command and name it HelloWorldCommand.
As soon as Visual Studio creates the command, we’re pretty much good to go as far as Visual Studio extensions are concerned. Build the project, put a breakpoint somewhere in the MenuItemCallback method in the HelloWorldCommand class (HelloWorldCommand.cs) and hit Start.
A new Visual Studio instance should start (the Experimental Instance) in which we can find our command named “Invoke HelloWorldCommand” in the Tools menu (if this is your first time doing this, you may need to go through the Visual Studio’s Experimental Instance initial setup wizard). If we try to click the command now, we should see that our breakpoint in the original instance is hit and that a message will appear as a result of our command’s code.
We can close the message box and the Experimental Instance now, change the message to show “Hello World from the SSMS extension!” and try again to see if the changes take effect. Start the debugging session and click the command again. It should now display the new message.
Pretty easy, right? Well, this is where the easy part ends and the problems begin.
Moving to SSMS
OK, so let’s now try to move our extension to SSMS. The first thing we want to do is to start SSMS instead of Visual Studio when we start debugging. To do this, we first need to close Visual Studio and start it again but this time, run it as an administrator (we need to do this because Visual Studio will need to move some files around and to do that, we need administrative privileges). Once we’re in Visual Studio, open up the solution again, right-click the project in the Solution Explorer and select Properties.
In the Debug tab, change the external program to be SQL Server Management Studio instead of Visual Studio by changing the property “Start external program” to:
C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe
Also, a good idea (but not required) is to set the /log argument in the “Command line arguments” field to make SSMS log its activity to a file which can help us with troubleshooting down the line. The log file (ActivityLog.xml) can be found at:
C:\Users\Username\AppData\Roaming\Microsoft\AppEnv\14.0
So, now that we have all that set up, we can try to start the extension again. If we start debugging in Visual Studio, we should now see that SQL Server Management Studio starts up when we hit the Start button. Wait for it to fully start up and when it does just close the Connect to Server window. OK, so SQL Server Management Studio did start, but our command is nowhere to be found in the tools menu.
Also, if we go back to our Visual Studio, we can see that our breakpoint isn’t active.
Something is wrong here. Well, SSMS looks for extensions when starting up in:
C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Extensions\
If we go to that location, we can see that our extension is not there. Our extension needs to be here if we want SSMS to load it. So, let’s fix that. Open up the project’s properties window again and this time open the VSIX tab. In it, check the “Copy VSIX content to the following location” and in the text box enter the SSMS Extensions folder. At the end of the location we’ll add a folder with the name of our extension so the full location should look like this:
C:\Program Files
(x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Extensions\HelloWorldSsmsExtension
Start debugging again and wait for SSMS to open. It looks that our extension still isn’t working but if we go to the extensions folder, we can see the newly created folder for it. So why isn’t our extension loading? If you look into the activity log, you can see that SSMS is importing the .pkgdef file and not much else. Everything should be OK, but it isn’t.
Workaround #1
You see, the SSMS team at Microsoft keeps a list of all the extensions it allows for SSMS to load. It’s just a list of package GUIDs that are white listed and the packages (extensions) identified by those GUIDs are loaded into SSMS with no problem. Anything else won’t be loaded by SSMS. I’ve read somewhere that the reason for this is that the SSMS team doesn’t want SSMS to be flooded with irrelevant extensions. Another place said that Microsoft should eventually include official support for SSMS extensions and this shouldn’t be a problem in the future. But until then we have to use a workaround for this problem if we want our extension to load (alternatively you could just contact Microsoft’s SSMS team and ask them to whitelist your extension). So, what is this package GUID and how do we work around this problem?
The package GUID is nothing more than a unique identifier for your extension and it can be found in two places in our project. The first one is in the HelloWorldCommandPackage class:
public const string PackageGuidString = "c626db04-880e-41bf-b780-ab5804d1ea2f";
The other is in the HelloWorldCommandPackage.vsct file:
<GuidSymbol name="guidHelloWorldCommandPackage" value="{c626db04-880e-41bf-b780-ab5804d1ea2f}" />
To work around the validation problem we need to explicitly tell SSMS to skip the validation for this package when trying to start up. To do this, unfortunately we need to edit the registry a bit. To open up the registry editor, first we need to open the “Run” window by hitting Windows + R on the keyboard. In it, type regedit and hit OK.
When the registry editor opens, go to:
HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\14.0
In it, check if there’s a “Packages” key and if there isn’t, create it by right-clicking 14.0 and then selecting New > Key.
In the (newly created) Packages key create a new key with the extension’s package GUID wrapped in curly braces as its name. The key name should look like this:
{c626db04-880e-41bf-b780-ab5804d1ea2f}
In the new key (the one with the package GUID as its name) add a new DWORD (32-bit) Value by right-clicking the key and selecting New > DWORD (32-bit) Value.
Once the value is created, right-click on it and rename it to SkipLoading. After this, our final step is to set the value to 1 by right-clicking the new (SkipLoading) value and selecting Modify… from the context menu.
Just put 1 in the "Value data" field and hit OK.
You can close the registry editor now. If we got this step right, our extension should finally load into SSMS when we start debugging. Go back to Visual Studio and try it out. Great, our command is here and is working just fine. Also, when we try to run it we can see that our Visual Studio breakpoint hits and finally all is good.
Well, not really!
Workaround #2
The thing is… if SSMS loads the extension correctly and we initialize it by running it, then SSMS will remove the SkipLoading value from the registry and our extension won’t work the next time we start SSMS. This is easy enough to check. Go to the registry editor again and see for yourself that the SkipLoading value is missing in your package key.
This only happens if SSMS tries to initialize the extension which by default happens when you first run it. If you add SkipLoading again, everything will work again and your extension will load when you start debugging. If you close SSMS without running your command, then SSMS won’t initialize your extension and the value won’t be removed from the registry. But not running is not an option when we try to develop the command and re-setting the value in the registry editor between each run is too time consuming to do. For this, we need another solution and another workaround. We’ll open up the HelloWorldCommandPackage class and add the following method in it:
private void AddSkipLoading()
{
var timer = new Timer(2000);
timer.Elapsed += (sender, args) =>
{
timer.Stop();
var myPackage = UserRegistryRoot.CreateSubKey(@"Packages\{" + PackageGuidString + "}");
myPackage?.SetValue("SkipLoading", 1);
};
timer.Start();
}
Note: To use it, we also need to import the System.Timers namespace:
using System.Timers;
We call the method before the end of the Initialize() method.
protected override void Initialize()
{
HelloWorldCommand.Initialize(this);
base.Initialize();
AddSkipLoading();
}
The final result should look like this:
The method that we added restores the SkipLoading key for our package 2 seconds after the Initialize method finishes and SSMS removes the value from the registry. We add the 2 second delay so that we’re sure that we don’t restore the value before SSMS has time to remove it. This would enable us to finally start developing our extension. If we start SSMS but don’t run the command, all is good because the extension didn’t initialize and SSMS didn’t remove the SkipLoading value. If we run the command and the extension initializes, then the code will just re-set the value after SSMS has removed it. So the next time we start SSMS our extension should load just fine. We can try this out by hitting Start in Visual Studio and finally start developing our brand new extension.
Sharing Your Extension
Let’s say that you finished developing your extension and you want to send it to a friend or a colleague. That’s easy. Just zip-up the HelloWorldSsmsExtension folder located in your SSMS Extensions folder, send it, and tell him to place it in the same location. But what if you don’t want to embarrass yourself by telling him how to do the registry editor hack just to get your extension going for the first time (remember that the extension itself will set the SkipLoading value once it loads up).
Workaround #3
Well it turns out that we can make SSMS itself set this value for us. Just to recap… if your extension doesn’t pass the SSMS validation, then it won’t be loaded when SSMS starts. But, for SSMS to know if your extension is white listed or not, it at least has to read the .pkgdef file (which contains the package GUID along with other configuration information) that is generated when you build your extension. In it, we can place an instruction for SSMS to auto load the extension when it starts up. With this instruction in place, SSMS won’t just silently fail when loading up your extension but instead it will throw an error which we can use to set the SkipLoading value. In the error message, SSMS will complain that something is wrong with the extension and ask you if it should continue to display the error. If you select No, then SSMS will add the SkipLoading value itself but unfortunately won’t load the extension when it starts up. But, the next time you do start SSMS, the value will be there and from then on, the extension itself will take care of its presence in the registry. So in other words, you just need to restart SSMS after selecting No and all will be well from then on.
To add the instruction to the .pkgdef file, all you need to do is add the ProvideAutoLoad attribute with the NoSolution context GUID to your HelloWorldCommandPackage class.
[ProvideAutoLoad(UIContextGuids80.NoSolution)]
The final code should look like this:
This will force SSMS to load your extension on startup, it will fail and it will ask if it should continue to show the error. You select No, restart SSMS and you should see your extension loaded and working fine. It’s only this first startup of SSMS that is problematic. But hey, it’s the best thing we got for now. So when you send your extension to a friend, just tell them to select No and restart SSMS the first time and everything will be OK from then on.
Final Words
This is all you need to know to get started with developing SQL Server Management Studio 17 extensions. The process should be similar for older versions of SSMS. If anyone has any other helpful tips, please feel free to contact me and I will try to get them in this tutorial. The full code of the HelloWorldSsmsExtension is also available for download from github at the following address:
Note: If you want to try the attached project (or the one at github), you need to set the “Start external program” property in the project’s properties first.
That is all...
References and further reading
These are the resources I used to help me get started that are worth looking into for more information:
Also some github projects that you can use to get more ideas:
