Export (0) Print
Expand All

Configuring the Transact-SQL Debugger

You must configure Windows Firewall exceptions to enable Transact-SQL debugging when connected to an instance of the Database Engine that is running on a different computer than the Database Engine Query Editor.

The Transact-SQL debugger includes both server-side and client-side components. The server-side debugger components are installed with each instance of the SQL Server 2008 Database Engine. The client-side components are installed when you install the SQL Server 2008 client-side tools.

There are no configuration requirements to run the Transact-SQL debugger when SQL Server Management Studio is running on the same computer as the instance of the SQL Server Database Engine. However, to run the Transact-SQL debugger when SQL Server Management Studio is running on a different computer from the instance of the Database Engine, you must enable program and port exceptions on both computers.

Caution noteCaution

Enabling exceptions in the Windows Firewall may expose your computer to security threats that the firewall is designed to block. Enabling exceptions for remote debugging unblocks DCOM (TCP port 135) and IPSEC (UDP port 4500 and UDP port 500). It also allows the debugger to open additional ports.

On the computer that is running the instance of the Database Engine, use the Windows Firewall Control Panel application to specify the following information:

  • Add TCP port 135 to the exceptions list.

  • Add the program sqlservr.exe to the exceptions list. 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.

  • If the domain policy requires network communications to be done through IPsec, you must also add UDP port 4500 and UDP port 500 to the exception list.

On the computer that is running SQL Server Management Studio, the first time you open a debugging session, SQL Server Management Studio opens a dialog box with three options for configuring the remote debugging access.

Cancel remote debugging

Cancels the attempt to start debugging. The security settings of your computer remain unchanged.

Unblock remote debugging from computers on the local network (subnet)

Enables remote debugging of computers on your local subnet. This setting may open vulnerabilities to computers on your local subnet, but the firewall continues to block information coming from outside the subnet.

Unblock remote debugging from any computer

Enables remote debugging of computers anywhere on the network. This setting is the least secure.

Optionally, you can manually configure the program and port exceptions by using Windows Firewall to specify the following information:

  • Add TCP port 135 to the exceptions list.

  • Add program ssms.exe (SQL Server Management Studio) to the exceptions list. By default, ssms.exe is installed in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE.

The requirements to start the Transact-SQL debugger are as follows:

  • SQL Server Management Studio must be running under a Windows account that is a member of the sysadmin fixed server roll.

  • The Database Engine Query Editor window must be connected by using either a Windows Authentication or SQL Server Authentication login that is a member of the sysadmin fixed server role.

  • The Database Engine Query Editor window must be connected to an instance of the SQL Server 2008 Database Engine or later. You cannot run the debugger when the Query Editor window is connected to an instance that is in single-user mode.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft