When you try to debug a stored procedure on the server from the client machine, the debugger just runs through the code and does not stop at any executable statement or break-point. The debugger however works locally both at the client and server.
No errors are logged in any of the machines in SQL Server error logs and no error messages are thrown at GUI.
Application log from the server may have messages like below
10/11/2006 5:15:36 PM SQLDebugging98 Error None 1 N/A COBRA2 Unable to connect to debugger on MAKO. A firewall may be preventing communication via DCOM to the debugger computer. See the 'Setting Up SQL Debugging' help topic in Visual Studio. Debugging disabled for connection 55.
7/16/2006 5:22:33 AM .NET Runtime Optimization Service Error None 1101 N/A COBRA2 ".NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Failed to compile: Microsoft.ReportingServices.QueryDesigners, Version=126.96.36.199, Culture=neutral, PublicKeyToken=89845dcd8080cc91 . Error code = 0x80070002
7/16/2006 5:22:09 AM .NET Runtime Optimization Service Information None 1100 N/A COBRA2 ".NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Began compiling: Microsoft.ReportingServices.QueryDesigners, Version=188.8.131.52, Culture=neutral, PublicKeyToken=89845dcd8080cc91
When we checked there was no firewall between the two servers
Ensure that there is no Anti Virus running and if there is one configure it not to scan the transmissions on port 135 of the machine
In my case the Norton Antivirus was scanning the port 135 and was interrupting with the TCP/IP communications at that port
Norton Internet worm had the following message logged:
Details Rule: "Default Block EPMAP" stealthed (COBRA2.riskvectors.com (192.168.1.210), epmap (135))
Inbound TCP connection.
Local address, service is (0.0.0.0, epmap(135))
Remote address, service is (COBRA2.riskvectors.com (192.168.1.210),1830).
Process name is "C:\WINDOWS\system32\svchost.exe"
Configuring the Norton not to scan port 135 got rid of the problem.
Norton was acting as a firewall at port 135.
When you use any type of firewall or a software which acts as a Firewall, you must configure it not to interrupt with the transmission on TCP port 135 (RPC port) in case you want the T-SQL debugger to work remotely.
An example on how to achieve the above for Visual Studio 2003:
1. In Control Panel, open Security Center.
2. Under Manage security settings for, click Windows Firewall.
3. Click the Exceptions tab, click Add Program.
4. In the Programs list, click Microsoft Visual Studio .NET 2003, and then click OK.
5. In the Windows Firewall dialog box, click Add Port.
6. In the Name box, type any name that you want.
7. In the Port number box, type 135 , click TCP, and then click OK.
8. Click OK to close the Windows Firewall dialog box.
Support Engineer, Microsoft SQL Server
Technical Lead, Microsoft SQL Server