Remote Debugging in SQL Server Management Studio …

If you are a developer you know very well that a good programmer is not someone who can write fancy code but someone who knows how/when/where to press F9-F10 smile_tongue no offence. And it is equivalently true for SQL Server as well. Front end used to manage SQL Server instance is called SQL Server Management Studio (aka SSMS, my current project requires to me to be very familiar with many acronyms and aliases so I kind of love acronyms) … and debugging can be very very handy tool when you are developing some sort of Stored Procedure, Function, View or any sort of super complex query. It gives you insight of how your code works at every step and you can easily troubleshoot your code if something is wrong.

If you are have installed instance of SQL Server on local machine then this post is not for you. But if you are a developer at some enterprise where you are usually not allowed to have separate installation of SQL Server on your local machine. And you are usually using SSMS installed on your workstation to connect to Dev. server or something like that. In that case it could be real issue for you if you have not done before. No matter how much developers try, there has been always problem with error messages. I mean beyond some limit error messages can’t be displayed in much friendly text.

To be able to run remote debugger on your workstation, it is two part process. First you need to configure firewall of your workstation and then you need to add exception at firewall of your Database Server. And in general both can be done by yourself if you have sufficient admin rights.

For Part 1: Add Exception to firewall of your workstation.

for that you need to open port TCP 135 and UDP 4500/500.


For Part 2: Add Exception to firewall of Database Server.

for that you need to add exception for sqlservr.exe at firewall of your server and also open ports used for debugging process TCP 135 and UDP 4500. (Actually only adding that sqlservr.exe worked for me.)

And in the end if all worked fine you will be able to simply stat debugging process by pressing F10. Else … you are screwed and there is no hope for you in this whole world ….

Oki, that was more dramatic … in reality, if you ever not be able to make it work there are many ways to get help. Mail me the error, post on MSDN Forum or Google it. But I think if you know what you are doing its really easy to make debugging work. If you somehow can’t find good solution, this post will help you with step by step screenshot instructions.

Anyways, this is end of post.

It’s Just A Thought … fingerscrossed

Gaurang Sign

Leave a Reply

Your email address will not be published. Required fields are marked *