| I spent quite some time trying to get T-SQL debugging to (finally) work against a remote SQL Server instance. I was initially distracted by the MSDN documentation that shows how to set up "remote" debugging. It turns out the solution was really pretty easy, but it's like saying it's easy to get into the Pentagon if you're the Secretary of Defense. As a "best practice," I create a special local system account specifically dedicated to SQL Server and the related services it starts. This account is designated when SQL Server is initially installed. However, if SQL Server is running under this account, you won't be able to debug stored procedures remotelyunless you execute the Visual Studio IDE "as" this or an identical account. If you set up SQL Server to log on as the same account you use to develop, you'll be able to debug stored procedures just fine without having to attach processes or start "remote" debugging. To set up the Visual Studio 2005 session that can perform T-SQL debugging against a remote procedure, you need to make sure that: - You have a stable connection to the server. I suggest using the Server Explorer to open a connection to the target server and drill down to the target stored procedure to be debugged. You'll want to do this anyway to make sure the needed breakpoints are set.
- You need to ensure that the account you're using to run Visual Studio and the account used to connect to SQL Server are members of the sysadmin role (group) on the SQL Server hosting the stored procedure.
- The account used to connect to SQL Server (from your ADO.NET code) can be either your Windows user account (if you are using Windows authentication) or a SQL Server account that specifies appropriate user ID and password. When using Windows Authentication, this account may be a local system account or a Windows domain account. In either case, it must match the account used to run the SQL Server service.
Let's walk through a configuration that works. It might save you some time when you're on a deadline and try to get T-SQL debugging to work. You might need to cooperate with your DBA to configure the server, but then again, you might just need to determine the Windows account used to run the SQL Server service. 1. | Starting with the target SQL Server, open the SQL Server Configuration Manager. While addressing the target SQL Server machine, drill into the SQL Server 2005 Services, as shown in Figure 5.19. Note the name of the service running the instance of SQL Server hosting the stored procedure to be debugged.
Figure 5.19. Using the SQL Server Configuration Manager to inspect the SQL Server instance service. 
| 2. | Note that the SQL Server instance (SS2K5) is logged on as ".\SQLE". This means it's logged on as a local machine account (not a Windows domain account). Your instance of Visual Studio 2005 will need to run as this account, or you'll need to change the account used by the SQL Server instance to match what you use to run Visual Studio.
I need to make sure this account (SQLE, in this case) has sufficient rights to debug stored procedures. That's the next step.
| | | 3. | Using SQL Server Management Studio, open a connection to the target server and drill into the Server Roles tab (as shown in Figure 5.20).
Figure 5.20. Drilling down to the Server Roles using SQL Server Management Studio. 
| 4. | Click on "sysadmin" and inspect the list of role members. Make sure the account used to run the SQL Server instance is listed (as shown in Figure 5.21). In this case, the BETAV9\SQLE account is listed as a member of the sysadmins role, so I can use this account to start Visual Studio 2005 when debugging stored procedures.
Figure 5.21. Listing the members of the sysadmin role. 
| | | 5. | Next, I need to start Visual Studio 2005 using the same credentials used to start the target instance of SQL Server hosting the stored procedure. This might be a bit tricky if you don't know the password of the account, so you still might need to actually speak to your DBA. Given a few days, you should be able to crack this passwordunless the DBA knows how to create and use strong passwords. In this case, it would be easier to take the DBA out to a nice lunch and beg for the password. Of course, if you're also the DBA, you should take yourself out to lunch.
Starting Visual Studio (or any application) using alternative Windows credentials is not that hardyou simply have to use the "run as" feature of the Windows desktop. Let's step through the process of setting up a separate shortcut for Visual Studio to run as using different credentials.
| 6. | Right-click on the Windows desktop and choose "New shortcut".
| 7. | Enter the path to the executable application to start or browse to its location. Since I want to start the Visual Studio 2005 development IDE, I used "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe".
| 8. | Click Next and enter the name for the shortcut. I used "Visual Studio 2005Run as". This creates the new shortcut on your desktop.
| | | 9. | I'm not done yet unless you want to enter the logon credentials each time you click the new Visual Studio "run as" shortcut. Right-click the new shortcut and choose "Properties", as shown in Figure 5.22. Change the Target to include "runas.exe /user:SQLE /savecred". Yes, you should change the "SQLE" username to match the name used to start the target SQL Server.
Figure 5.22. Creating a custom shortcut to launch Visual Studio using alternative credentials. 
The "savecred" option is available only on Windows XP Professional and Windows 2003 Server. For Windows XP Home, you'll have to enter the credentials manually.
| 10. | I also changed the comment and chose an icon for the shortcut (the runas defaults to a simple icon). To use the Visual Studio 2005 icon, navigate to the executable and choose one of the icons from the list exposed. You can drag the new shortcut to the quick launch bar, if desired.
| | | 11. | Test your icon by clicking on the new shortcut. Yes, you'll be prompted for the credentials, but only the first time you use it. After that, Visual Studio is launched with the alternative credentials.
|
|
