I hope that all of you had a chance to participate in my session on the “Underappreciated Features of Microsoft SQL Server” during the Exclusive SQL Server Webcast series on Security & Scalability. If not, I trust the recorded sessions would be up soon for you to see. I will share the location of the same once available.

During the session, I demonstrated the T-SQL Debugger (http://beyondrelational.com/blogs/nakul/archive/2011/02/02/t-sql-debugger-underappreciated-features-of-microsoft-sql-server.aspx). Looking at the number of questions after the session during the Q&A and from my colleagues at the office, it looks like I underappreciated the uniqueness of the feature, which always gives a sense of satisfaction to a presenter. I felt that I had brought something new to the audience, and that the audience would be using the features demonstrated. That’s great!

I received a number of questions related to the debugger. Earlier this week, I had mentioned about which SQL Server versions/editions support T-SQL Debugging, and what are the basic permissions that one needs to use the T-SQL debugger (Enabling T-SQL Debugger in SQL Server Management Studio (SSMS)).

Even after you make sure that the user permissions are configured correctly, you may find that you are still not able to use T-SQL Debugging if attempting to debug when the Database engine is running on a computer other than where the Query editor is running. This is because debugging involves both server & client side components and your Windows Firewall is not configured correctly.

WARNING: If you are not comfortable with modification of the Windows Firewall rules, please allow your IT administrator to make these configuration changes. The author is not responsible for any security or other system stability issues that may arise due to improper Firewall configuration.

Here are the configuration changes that need to be done (for SQL 2008):

On the Server

1. Add TCP port 135 to the exceptions list on the Windows Firewall
2. Add the program sqlservr.exe to the exceptions list. By default, sqlservr.exe is installed in C:Program FilesMicrosoft SQL ServerMSSQL10.InstanceNameMSSQLBinn, where InstanceName is MSSQLSERVER for the default instance, and the instance name for any named instance
3. If using IPSec, you will also need to open UDP port 4500 and 500

On the Workstation (where the Query editor is running)

1. Add TCP port 135 to the exceptions list on the Windows Firewall
2. Add program ssms.exe (SQL Server Management Studio) to the exceptions list. By default, ssms.exe is installed in C:Program FilesMicrosoft SQL Server100ToolsBinnVSShellCommon7IDE

That should get your debugger working as expected.

Until we meet next time,

Be courteous. Drive responsibly.