Configure the Debugger

Last modified: October 1, 2024

Ensure that the server-side and client-side components of the T-SQL Debugger are installed

Server-side tools are a part of the SQL Server installation. Client-side tools are not delivered with SSMS and you need install them separately. You can install the client-side tools with SSDT, a new version of which is provided by the Visual Studio Installer.

If you don’t use Visual Studio and want to have the minimal installation package, you can download Build Tools for Visual Studio 2022 in order to install Data storage and processing build tools:

Enable debugging Firewall exceptions on the client and server sides

To enable debugging Firewall exceptions on the server side:

Open PowerShell by pressing Win+R and run the following commands, but replace C:\Program Files\Microsoft SQL Server\MSSQL13.InstanceName\MSSQL\Binn\sqlservr.exe with your path to sqlservr.exe and 1.1.1.1 with your relevant IP:

New-NetFirewallRule -DisplayName "Allow SQLDebugger" -Direction Inbound -Action Allow -Program "C:\Program Files\Microsoft SQL Server\MSSQL13.InstanceName\MSSQL\Binn\sqlservr.exe" -Profile Any -Protocol TCP -LocalPort RPC

New-NetFirewallRule -DisplayName "Allow SQLDebugger SVCHost" -Direction Inbound -Action Allow -Program "%systemroot%\System32\svchost.exe" -Profile Any -Protocol TCP -LocalPort RPCEPMap

New-NetFirewallRule -DisplayName "Allow Client IP" -Direction Inbound -Action Allow -RemoteAddress 1.1.1.1 -Profile Any -Protocol TCP

To enable debugging Firewall exceptions on the client side:

Open PowerShell. For this, press Win+R and execute:

New-NetFirewallRule -DisplayName "Allow SQLDebugger SVCHost" -Direction Inbound -Action Allow -Program "%systemroot%\System32\svchost.exe" -Profile Any -Protocol TCP -LocalPort RPCEPMap

If you use SSMS, it’s required to run the following command, but replace C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\Management Studio\ssms.exe with your path to ssms.exe:

New-NetFirewallRule -DisplayName "Allow SQLDebugger SSMS" -Direction Inbound -Action Allow -Program "C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\Management Studio\ssms.exe" -Profile Any -Protocol TCP -LocalPort RPC

In case you use SQL Server Data Tools, execute the following command, but replace C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe with your path to devenv.exe:

New-NetFirewallRule -DisplayName "Allow SQLDebugger SSMS" -Direction Inbound -Action Allow -Program "C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe" -Profile Any -Protocol TCP -LocalPort RPC

Check that the login is a member of the sysadmin fixed server role

In SSMS, click New Query, enter the following statement, and click Execute:

SELECT name, type_desc, is_disabled 
FROM master.sys.server_principals 
WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name

If the login is not a member of the sysadmin fixed server role, you can execute the following statement in SSMS, but replace UserName with your username:

EXEC sp_addsrvrolemember 'UserName', 'sysadmin';
GO

Check that a database is not in the single-user mode

In SSMS, click New Query, run the following command, but replace YourDb with your database:

if (SELECT user_access_desc FROM sys.databases WHERE name = 'YourDb') = 'SINGLE_USER'
    begin
       print 'It is in single user mode!'
    END

If the database is in the single-user mode, you need to switch it to the multi-user mode. For this, proceed with the following steps:

1. Check all active users in SSMS. Click New Query, enter the following statement, and click Execute:

sp_who2

This statement returns all session IDs of the users that are required for the next command.

2. Disconnect all sessions of the connected users, but replace session_id with the session ID of the process you want to end:

KILL <session_id>

3. Change the mode for the database, but replace YourDB with the required database in the command:

USE [master];
GO
ALTER DATABASE [YourDB] SET MULTI_USER;
GO