VS 2005 and SQL Server 2005 Remote Debugging Issue

[inky...@gmail.com]

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?

[inky...@gmail.com]

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.