Debugging stored procedure in SQL Server 2005 from Visual Studio?

Question

I see a lot of frustrated questions here and elsewhere with no clear answer. I am trying to get the stored procs to debug, but with no success.

Client: either VS2005 or VS2008, works in neither. When I select 'Step into Stored Procedure' from the sproc context menu, I get "Cancelled by User' in the debug window and that's the end of it.

I did the following to help myself (all to no avail):

1. Right clicked on the connection and selected 'Application Debugging' and 'Allow SQL/CRL debugging'
2. Made sure that all the boxes are on the same domain.
3. Made sure that I am logging in to SQL Server with the same domain account that I log into my workstation with.
4. Made sure that my domain account is both an admin on the SQL box and an admin within SQL Server.
5. I ran msvsmon.exe on the SQL box and successfully attached to a process from my client box (in VS).

What am I missing?

Answer 1

What edition is your SQL Server? SP debugging is only available in Pro and Team edition (not Express and Standard)

Also make sure (again) you're using Windows authentication to login and your account is a sysadmin in SQL server.

As you are able to attach to a process on the SQL box, I assume DCOM to work correctly and there is no blocking firewall in between.

Answer 2

One important issue here is that this won't work if SQL Server process is running as local system, which is the default install. It needs to be running as an account which is in the administrator group on the local machine.

What I have done is set up local user on my box named "sqlserver" and put it into the local administrators group. Then, go into the service control panel, stop the sql server service. Modify it and set it to run as the specified user account rather than as local system. Specify the sqlserver local user account and the password you created. Then restart the service.