Unable to do SQL Stored Procedure debugging remotely from VS2010
This post describes an interesting scenario that I came across when working with on Visual Studio Project based issue.
In this situation, customer was unable to debug stored procedures on a remote SQL Server using Visual Studio. The SQL Server was running on a Virtual Machine that was in a different domain than the domain of the local machine (machine on which Visual Studio was running).
Customer was able to create a data connection in the Visual Studio server explorer without any problems. The problem occurred when he performed the following steps:
Selecting a particular stored procedure from the list and tried to step into a stored procedure. That’s when he was noticing a message in the output window: "Canceled by user"
Adding to this, there was another point that was very important to be noted down here: As per the customer there is a one way trust between these domains. (The domain that the VM was residing in trusted the domain that where his local machine was located. However the local machines domain didn’t have the trust to the domain in which the VM was located. )
Before I got started with the log collection to diagnose the problem, I wanted to verify the pre-requisites of the remote debugging in the customer's environment.
On the computer running Visual Studio 2008:
1. Configured firewall to add the following inbound port exceptions: TCP 135, UDP 400, and UDP 500.
2. Added program exception to the firewall for DevEnv.exe.
On the computer running SQL Server :
1. Configured firewall to add the following inbound port exceptions: TCP 135, UDP 400, and UDP 500.
2. Added program exception to the firewall for sqlsrvr.exe.
I tried to reproduce the issue once these setting changes were implemented. But still didn't have any success with remote debugging. Probing in further found that the customer’s logged in user account on the SQL Server machine was not the member of the local administrator group on the SQL Server machine.
This came up when we reviewed the Pre-requisites of the remote debugging once again. This setting is described as a mandatory requirement in our MSDN article: https://msdn.microsoft.com/en-us/library/w1bhybwz(v=VS.90).aspx
Once I added the logged in user on the SQL Server machine to the Local Admin group and tried to step into the stored procedure from the Visual Studio machine, customer was able debug the stored procedure successfully.
Author : Ajay (MSFT), SQL Developer Engineer, Microsoft
Reviewed by : Jason (MSFT), SQL Escalation Services, Microsoft