Debugging SQL Server 200 Stored Procedures with ADO .NET

With SQL Server 2000 with SP3a we used to be able to debug our Stored
Procedures by stepping into them via our ADO .NET code, but we have recently
upgraded to SP4 and can no longer do this unless we connect as 'sa'.

We have made sue that the Extended Stored Procedure sp_sdidebug can be
accessed by the user we normally connect as and we always execute the
following line on startup to turn on legacy debugging.

exec [master].[dbo].[sp_sdidebug] 'legacy_on'

Furthermore we have verified in our DCOM configuration properties that under
'Default Protocols' there is a 'Connection-orientated TCP/IP' entry and this
is the first entry in the list.

Any help on why we are unable to debug our stored procedures would be
greatfully appreciated.

v-kevy
Sun Nov 27 20:40:54 CST 2005

Hi,

Do you mean that in SP4 you can debug the sue user, but cannot debug with
the sa user? If so, I suggest you reset the sa password. If that still
doesn't, what's the error message?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

el_sid
Mon Nov 28 10:40:10 CST 2005

No, what I mean is that we can only debug as the sa user and not as any other
despite the fact that we have given our user admin rights in SQL Server.

"Kevin Yu [MSFT]" wrote:

> Hi,
>
> Do you mean that in SP4 you can debug the sue user, but cannot debug with
> the sa user? If so, I suggest you reset the sa password. If that still
> doesn't, what's the error message?
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
>

v-kevy
Mon Nov 28 21:29:05 CST 2005

Hi,

It seems to be a security issue. Are you getting message box which says
security has not been set up correctly on a server? If so, you can check
the following link for more information.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/htm
l/vxtbstroubleshootingsqldebuggingsetupsecurityissues.asp

You can also try to post in the sql newsgroups for this issue.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

el_sid
Tue Nov 29 03:43:09 CST 2005

No messages are being displayed nor are any exceptions being thrown.
Debugging is turned on and unless we connect as the sa user we just can't
debug our Stored Procedures at runtime when they are called from our code.

"Kevin Yu [MSFT]" wrote:

> Hi,
>
> It seems to be a security issue. Are you getting message box which says
> security has not been set up correctly on a server? If so, you can check
> the following link for more information.
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/htm
> l/vxtbstroubleshootingsqldebuggingsetupsecurityissues.asp
>
> You can also try to post in the sql newsgroups for this issue.
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
>

v-kevy
Tue Nov 29 23:34:03 CST 2005

Hi,

Please check the permission of the user sue. Does this user has the
permission to exec [master].[dbo].[sp_sdidebug] 'legacy_on'?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

el_sid
Wed Nov 30 03:15:02 CST 2005

Yes, as stated we have given the user full admin access to SQL Server, have
connected via Query Analyzer to the SQL Server database and run the command.
It doesn't seem to make any difference except for the user sa.

"Kevin Yu [MSFT]" wrote:

> Hi,
>
> Please check the permission of the user sue. Does this user has the
> permission to exec [master].[dbo].[sp_sdidebug] 'legacy_on'?
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
>

v-kevy
Fri Dec 02 03:41:02 CST 2005

Hi,

In this case, I suggest you try to use SQL Profiler to trace what is
actually happenning when using both sa and sue to do the debugging. Then
logon as sue in QA to execute these SQL statements to see if it will go
wrong.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

el_sid
Mon Dec 05 07:11:05 CST 2005

Everything works fine with Query Analyzer, it is simply via the .NET IDE
where we don't seem to be able to debug from unless we connect as the sa
user.

"Kevin Yu [MSFT]" wrote:

> Hi,
>
> In this case, I suggest you try to use SQL Profiler to trace what is
> actually happenning when using both sa and sue to do the debugging. Then
> logon as sue in QA to execute these SQL statements to see if it will go
> wrong.
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
>

v-kevy
Tue Dec 06 03:37:01 CST 2005

Hi,

Since it's hard for me to reproduce this issue, I suggest you try to
contact Microsoft PSS for this issue. You can get their contact information
from the following link:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

el_sid
Tue Dec 06 04:05:02 CST 2005

Thanks.

We have also just reverted our SQL Server instances back to SP3 and they
work fine with the debugging, seems like yet another undocumented feature has
been added as part of a service pack.

"Kevin Yu [MSFT]" wrote:

> Hi,
>
> Since it's hard for me to reproduce this issue, I suggest you try to
> contact Microsoft PSS for this issue. You can get their contact information
> from the following link:
>
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
>

v-kevy
Tue Dec 06 22:03:04 CST 2005

Sorry for the inconvenience!

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."