The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
Debugging on your local box is easy. Like most things however, when you introduce a network between your SSMS install and the database instance you’re attempting to debug, things get a little more complicated. Welcome to day 10. Remote debugging.
I think a conservative estimate is that 99% of people who attempt debugging a query for the first time against a sql server that is not hosted on the same computer as SSMS will get this error. Conditions are rarely such that remote debugging works by default. But who knows, maybe firewall restrictions are really lax around your place and it just works.
Getting past this error is pretty straight-forward. The error message tells you everything your missing. Getting it up and working in fact may rely more on your social skills that your technical savvy.
SQL Server 2012 and 2014
Getting remote debugging up and running on a 2012 or higher SQL Server instance is as easy as this.
– First, create an inbound rule allowing sqlservr.exe across dynamic RPC. You may also have to create a rule for the local svchost.exe. Complete step by step instructions to accomplish this step can be found on msdn.
– Second, make sure you are a member of the sysadmin fixed role on the database instance.
Easy right? Maybe not. I can pretty much guarantee you’ll spend more time convincing your network folks to enable remote RPC rules and potentially the DBA responsible for the instance you are trying to test to grant you sql server sysadmin rights than anything else. Once you’ve successfully navigated those two landmines however you should be good. Remote debugging works just like debugging a local instance.
If you need help with the soft skills part I recommend following the advice I was given by Grant Fritchey one time at TechEd. “When working as a database professional you should always keep a frothy beverage budget for your network / firewall admins”. That’s good advice right there.
If your network folks haven’t worked with dynamic RPC before or your in the type of shop where you wear all the hats, you can point them to this article on MSDN that introduces the topic and gives a good walkthrough of setting it up on a 2008R2 server. It’s basically identical to setting it up on a Win Server 2012. I reference this article more for the justifications presented by Microsoft for moving to RPC over a dedicated port number than its version of windows.
Remote Debugging SQL Server 2008
If you happen to be debugging against a SQL Server 2008 or earlier box you only had to open up port 135 (and potentially UDP 4500 and 500 for IPsec). In my experience this was an easier sell to your network/firewall/security team than the newer dynamic RPC approach.
A Word on Best Practice
Remote debugging is resource intensive, leaves open sessions (and potentially blocks), requires elevated permissions for everything, and opens up ports that you’re not going to want in production. I know this probably seems like it should go without saying – but don’t debug against production. Other than that – all the tools and techniques we covered in days 8 and 9 are available to you remotely as well.
That pretty much wraps up our debugging section. Tomorrow we’ll head back over to dive a little deeper into SSMS options and spend a couple days on customizing your toolbars, shortcuts, and menus.