Debugging SQL Server 200 Stored Procedures with ADO .NET

[Guest]

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.

 

[Kevin Yu [MSFT]]

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."

 

[Guest]

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]]

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."

 

[Guest]

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]]

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."

 

[Guest]

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]]

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."

 

[Guest]

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]]

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."

 

[Guest]

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]]

Sorry for the inconvenience!

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