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=9.0.242.0, 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=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

When we checked there was no firewall between the two servers

Resolution:

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's Log:

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.

Reason:

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.

Abhishek Soni
Support Engineer, Microsoft SQL Server

Reviewed By
Sourabh Agarwal
Technical Lead, Microsoft SQL Server