How is remote debugging allowed on Microsoft SQL Management studio?

Xarzu · August 29, 2021, 5:24pm

This is working with Sql Server version 2008 R2

Here are the other versions of software that might be helpful:

This question might be misunderstood. I am not sitting at the computer where Microsoft SQL Managment studio resides and I am attempting to debug a remote database. That is not the issue I am having at all. Instead, I have remoted into a client's server by their request and also I am trying to debug an issue they are having. You see, this is how things are evolving in our world. More and more there are situations where a developer is working from home and has to remote into a client's system do get some work done.

When I open up their studio software on their side, if I try to debug or execute a command, I get this error message:

I want to be able to instruct them, on their side, what they need to do to make this problem go away. Please advise.

yosiasz · August 29, 2021, 10:15pm

Is this a production server? And what are you trying to debug?

Xarzu · August 29, 2021, 10:48pm

This is not a production server. And I am running SQL Server Management Studio to debug a stored procedure.

This link is of interest:

Have you Configure the Transact-SQL Debugger ?

If not then :

Configure firewall rules before running the T-SQL debugger - SQL Server Management Studio (SSMS) | Microsoft Learn

After that test the following steps :

Test 1: When you locally connect to the same instance the issue is not reproduced

Test 2: Check if TCP 139, TCP 445, UDP 137 and UDP 138 are open from both side (server and client) (e.g.: Configuring the Transact-SQL Debugger )

Test 3: Check if SQL management Studio is lauched with "Run an administrator..."

Test 4: Check if SQL service account can connect to the client machine. When you are using T-SQL debugging, SQL service account will communicate with the machine host Management Studio.It's an additional reason to set up SQL service with domain account

Test 5: Check if SSMS and SQL server service are in the same domain. if not, set up the same account and password that SQL server is using.

Test 6: Check if your domain account is sysadmin.

Test 7: Check if Kerberos is enable correctly and SPN are ok for your instance. - The SQL Network Interface library was unable to register SPN - SQL Server 2008 connectivity issue : cannot generate SSPI context

Test 8: Check from server side that the service below are started - TCP/IP NetBIOS Helper - Remote Registry - RPC Service .Details

This link:

shows this:

To prepare Windows Firewall for remote debugging (when SQL Server is running on a different computer)
On the computer running Visual Studio you must add the following to the Windows Firewall exception list:

	The Visual Studio program, Devenv.exe.

	TCP port 135

On the computer running the instance of SQL Server, you must add the following to the Windows Firewall exception list:

	The SQL Server program, sqlservr.exe. By default, sqlservr.exe is installed in C:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Binn, where InstanceName is MSSQLSERVER for the default instance, and the instance name for any named instance.

	TCP port 135

	If your domain policy requires network communication to be done through IPSec, you must also open the UDP 4500 and UDP 500 ports.

So, I gather from this is that perhaps it is up to me to add to the Windows Firewall exception list:
"The Visual Studio program, Devenv.exe" and "TCP port 135" albeit I am not running Visual Studio. But I am assuming it can't hurt if I do this.

So, the question is: How do I do this?

I am not experienced with working with firewalls. I do not know how to add firewall exceptions. When I look for applicable system programs, a number of thems seem available. Can anyone guide me?

yosiasz · August 30, 2021, 1:16am

For Firewall settings and changes this forum might not be the appropriate forum and would not recommend at all poking holes in the firewall. What exactly are you trying to debug and why exactly are you trying to use such approach? What are you trying to solve?

Xarzu · September 1, 2021, 2:32pm

Are there some sort of special settings that set a production server apart from a development server?

robert_volk · September 1, 2021, 2:37pm

Generally speaking, production systems have stricter network security rules and dedicated support teams to ensure those rules are followed. Such that you may not be able to make the firewall changes by yourself, or you will at least have to notify someone that you're making them.