Configure the Debugger

If you are unable to attach the Transact-SQL Debugger, you need to:

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.

Other toolsets

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:

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;
GO

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;
GO

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

Check that the ssdebugps.dll library file is installed on your PC

The Transact-SQL debugging feature works with SSMS version 17.9.1 and earlier and is also available in SQL Server Data Tools (SSDT) for Visual Studio. For later SSMS versions, Microsoft has discontinued support for the debugger and does not provide a 32-bit version of the ssdebugps.dll library file.

To enable the debugger for newer versions, you need to install and re-register the ssdebugps.dll library file as follows:

1. Check that the ssdebugps.dll file is stored at C:\Program Files (x86)\Common Files\Microsoft Shared\SQL Debugging (for SSMS) or C:\Program Files (x86)\Common Files\Microsoft Shared\SQL Debugging\SSDT version (for example, 120 or 130)* (for SSDT VS).

2. If the ssdebugps.dll file is missing, copy it from another computer with SQL Server or Visual Studio installed to your PC. The file can be found in the following directory - \Program Files (x86)\ Common Files\Microsoft Shared\SQL Debugging.

3. In the search panel of your computer, type %windir%\SysWoW64\cmd.exe and run it as an administrator.

4. In the Command Prompt, execute the following command to navigate to the directory where the ssdebugps.dll file is located.

cd  "C:\Program Files (x86)\Common Files\Microsoft Shared\SQL Debugging"

5. Execute the following command to re-register the file:

regsvr32 ssdebugps.dll

Note

T-SQL Debugger is fully compatible with Virtual Private Networks allowing you to seamlessly debug queries even when connected through VPN.

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge SQL Complete for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the SQL Complete in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the SQL Complete for your business, request a demo to see it in action.
Ready to start using dbForge SQL Complete for SQL Server?
Download a free 30-day trial