VS 2005 and SQL Server 2005 Remote Debugging Issue

[Guru7777]

I am having quite a bit of problems getting this to work properly. I am on a Windows XP Professional SP2 machine trying to connect to Windows Server 2003. I have Sql Server 2005 installed on the Server machine. I also have a local instance on my developer machine.

I can use remote debugging to debug both T-SQL statements and managed C# code on my local machine. However, when I try to work with the server, I cannot just hit the Debug button. When I do, I get the error "T-SQL execution ended without debugging. You may not have sufficient permissions to debug. Click Help for more information." Some times, I get the error, "Exception from HRESULT: 0x89710016".

I am an administrator on my local machine and on the server. When I connect to the server, I can see that it is using my server account (through the executable remote debugger program). The Sql Server is set up to use mixed authentication and my account is a full administrator there, as well.

If I merely attach to process when debugging, I get an error that T-SQL cannot be attached, but Managed code can. Going this route, I can debug a managed C# procedure.

I have tested this with firewalls off on both computers. I can Telnet to the server over the correct ports. I have even added every account and its mother to the Administrator role. Using Sql Server Profiler, I can also see that it is trying to use my correct account when it does act with Sql Server. My code is executed, but no T-SQL breakpoints are hit (obviously because it cannot attach).

Can you offer any suggestions?

----------------------------------------

TWljcm8kb2Z0J3MgIzEgRmFuIQ==

 

[Guru7777]

Well, we broke down and spent the $245.00 to put in a support call to Microsoft. It turns out that it was money well spent. They didn't get an answer for us on the first day (Friday), but called me back first thing Monday morning with what turned out to be the answer. I thought I would check back in and let you all know what solution was needed for us in case anyone else has the same problem.

We had to have Sql Server run under a certain account on the remote machine. An account with the same name needed to be running Visual Studio on the local machine. We created that account on our machine and then did "Run As" to invoke Visual Studio. The user that connected to the database needed to be a sysadmin (either through Windows Authentication or Sql Server Authentication) and the local machine needed to have the Local Security Option of "Network access: Sharing and security model for local accounts" needed set to "Classic – local users authenticate as themselves". I had changed the security setting before originally posting, but I wanted to include it to have a complete picture.

Once this is complete, you may debug. Visual Studio will give you a dialog box that will automatically punch the proper holes in Windows Firewall if you have not already done so.

Basically the key to solving this for us was the user account that Sql Server ran under. I've seen several posts where people have has similar problems and I hope that in the future that this post can be of some help to people experiencing the same issue.


----------------------------------------

TWljcm8kb2Z0J3MgIzEgRmFuIQ==