locked
Enable remote debug t-sql in sqlserver RRS feed

  • Question

  • Hi

    I need to run the debug to a store procedure in sqlserver, but from remote server (database server), if the database is local not problem, but if the database is in other server throw an error of debug.

     What can i do?

    thank you

    Saturday, April 4, 2015 12:56 AM

Answers

  • 2 things 

    only members of the sysadmin fixed server role are allowed to debug in SQL Server

    Firewall port 135 should be open on both machines

    https://msdn.microsoft.com/en-us/library/vstudio/s0fk6z6e%28v=vs.100%29.aspx?f=255&MSPPError=-2147217396

    https://msdn.microsoft.com/en-us/library/cc646024.aspx
    • Edited by dave_gona Saturday, April 4, 2015 3:35 AM
    • Proposed as answer by pituachMVP Saturday, April 4, 2015 5:33 AM
    • Marked as answer by Michelle Li Monday, April 13, 2015 2:12 AM
    Saturday, April 4, 2015 2:41 AM
  • Have you Configure the Transact-SQL Debugger ?

    If not then :

    https://msdn.microsoft.com/en-us/library/cc646024.aspx

    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


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by Michelle Li Monday, April 13, 2015 2:13 AM
    Tuesday, April 7, 2015 8:36 AM

All replies

  • 2 things 

    only members of the sysadmin fixed server role are allowed to debug in SQL Server

    Firewall port 135 should be open on both machines

    https://msdn.microsoft.com/en-us/library/vstudio/s0fk6z6e%28v=vs.100%29.aspx?f=255&MSPPError=-2147217396

    https://msdn.microsoft.com/en-us/library/cc646024.aspx
    • Edited by dave_gona Saturday, April 4, 2015 3:35 AM
    • Proposed as answer by pituachMVP Saturday, April 4, 2015 5:33 AM
    • Marked as answer by Michelle Li Monday, April 13, 2015 2:12 AM
    Saturday, April 4, 2015 2:41 AM
  • Have you Configure the Transact-SQL Debugger ?

    If not then :

    https://msdn.microsoft.com/en-us/library/cc646024.aspx

    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


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by Michelle Li Monday, April 13, 2015 2:13 AM
    Tuesday, April 7, 2015 8:36 AM