Remote debugging of Stored Procedures

Source host environment: Window XP SP2 vs2005 team

Target host environment: Window 2003 sp2 sqlserver2005

I configured the remote debugging of sqlserver2005 stored procedure in vs2008 in my previous company. Today I suddenly remembered this, So I configured this environment in my current company and the configuration was successful. The procedure is as follows:

(1): Create a new user on the target host and associate it with the Administrators group. If it is a 2003 operating system, the user may encounter problems such as non-conforming password policies after entering the password after saving the password. The solution to this problem is: control Panel -- local security policies -- password security: Disable some policies. Create the same user in the data and make it belong to the SysAdmin group. Then open the Database Service, modify it to run the user, and restart the service.

(2) create an identical user in the source host with the same username and password. Switch the user, start vs2005, add the server, select windwos authentication, and select debug data. Right-click the link and select options such as application debugging and CLR debugging, and then modify the properties of the project -- debug sqlserver.

(3): Open the stored procedure to be debugged, set the breakpoint, and set the breakpoint for the stored procedure in the program. Then, run the program to see the effect.

　　

Conclusion 1): The Platform supports debugging; 2) window users with permissions; 3) run database services with special users. In fact, the simplest way is to change the administrator password, because each computer has this user. Set the password of the target host to the same as that of the source host, and assign proper permissions to administartor in the data.

Later, I found some other knowledge on the Internet:

Visual Studio professional and team system allows you to set breakpoints for stored procedures in SQL Server and debug stored procedures.

　　

Debug T-SQL database objects in a remote instance

If the SQL Server database instance is on the same machine as Visual Studio, it is quite simple to debug the database object through Visual Studio. However, if SQL Server and Visual Studio are on different machines, you need to configure them carefully to make all aspects work normally. To this end, we have two important tasks:

• Make sure that the login account used to connect to the database through ADO. Net has the system administrator (SysAdmin) identity.
• Make sure that the Windows User Account used by Visual Studio on the development machine is a valid and valid SQL Server Logon account with SysAdmin identity.

The first step is relatively simple. First, determine which user account to use to connect to the database from the ASP. NET application, and then add the Logon account to the SysAdmin role through SQL Server Management studio.

The second task requires that the Windows User Account you use when debugging the application is a valid logon account on the remote database. However, sometimes the Windows account you log on to the workstation is not a valid logon account on SQL Server. A better way is to designate a Windows User Account as an SQL Server debugging account than to add your specific Logon account to SQL Server. Then, you can run Visual Studio using the certificate of the Windows Logon account to debug the database objects of the remote SQL server instance.

The following uses an example to illustrate the above situation. Assume that there is a Windows account named sqldebug in the Windows domain. You need to add this account to a remote SQL server instance as a member of a valid logon account and SysAdmin role. Then, we need to run Visual Studio as the sqldebug user to debug a remote SQL server instance through Visual Studio. Follow these steps: log out from the workstation, log on again with the sqldebug account, and then start Visual Studio. However, a simpler example is to use our certificate website, and then use runas.exe as the sqldebug user to start Visual Studio. By using runas.exe, you can use the identity of other user accounts to execute specific applications. To start Visual Studio as a sqldebug user, run the following command on the command line:

runas.exe /user:SQLDebug "%PROGRAMFILES%\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"

For more information about this process, see William R. Vaughn's Hitchhiker's Guide to Visual Studio and SQL Server, seventh edition, and how to set SQL Server permissions for debugging.

Note:If your development machine runs Windows XP Service Pack 2, you need to configure Internet Connection Firewall to allow remote debugging. How to enable SQL Server 2005 debugging involves two steps: "(a) on the host machine of Visual Studio, you must add devenv.exe to the exceptions list and open TCP port 135; (B) on a remote (SQL) machine, you must open TCP port 135 and add sqlservr.exe to the exceptions list. If your domain policy requires network communication through IPSec, ports UDP 4500 and UDP 500 must be enabled.