SQL Debugger not stopping on remote machine

[Steve Meier]

Environment:

SQLServer Developer Edition on Machine A

Enterprise Manager running on remote machine B

Both machines are in the same subnet

I open Enterprise Mgr on Machine A, right click on the Stored Proc, enter
the parameters and the debugger stops on the first line and I can single
step through it just fine.

I open Enterprise Mgr on Machine B and connect to the same database on
Machine B, right click on the same Stored Proc, enter the same parameters
and the debugger doesn't stop, but it runs the proc al the way through.
Even if I set a break point on one of the lines from this machine it doesn't
stop. I don't get any errors, it just runs right thought the proc.

I've been through all the DCOM settings, Logins with Admin rights and not a
SYSTEM account. Checked all the debug files and have them all registered.

Anyone else expericened this? Anyone have any ideas on how to fix it?

-Steve

[Erland Sommarskog]

Re: SQL Debugger not stopping on remote machine

Steve Meier (snaggs34@hotma il.com) writes:[color=blue]
> SQLServer Developer Edition on Machine A
>
> Enterprise Manager running on remote machine B
>
> Both machines are in the same subnet
>
> I open Enterprise Mgr on Machine A, right click on the Stored Proc, enter
> the parameters and the debugger stops on the first line and I can single
> step through it just fine.
>
> I open Enterprise Mgr on Machine B and connect to the same database on
> Machine B, right click on the same Stored Proc, enter the same
> parameters and the debugger doesn't stop, but it runs the proc al the
> way through. Even if I set a break point on one of the lines from this
> machine it doesn't stop. I don't get any errors, it just runs right
> thought the proc.
>
> I've been through all the DCOM settings, Logins with Admin rights and
> not a SYSTEM account. Checked all the debug files and have them all
> registered.[/color]

Which versions of the tools and server do you have on the machines? I
believe there is a problem if you run Query Analyzer SP2 or earlier
against a server that runs SP3. Microsoft did some security fixes in SP3
which resulted in this effect.

Also, make sure that SQL Server is running on a domain account and not
as Local System.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Steve Meier]

Re: SQL Debugger not stopping on remote machine

Erland,

Thanks for the reply. SQL Server is logging in under the adminsitrator
account. The SQL Server machine is not part of a domain. Actually none of
the machines in the network are part of a domain. They are all part of a
workgroup. SQL Server 2000 is running on Windows 2000 Server. The Client
and the Server are both at Service Pack 3. Remote debugging was supposedly
disabled by default in SP3. I have tried the command:

exec sp_sdidebug 'legacy_on'

but with both client and server on SP3, this causes probelms. If I don't do
the above command, it will run the proc on the client (XP Pro) but won't
stop. If I do the above command, then try to debug on the client, I get the
following error message:

Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on WEBSERVER (Error = 0x800706ba). Ensure that client-side components, such
as SQLDBREG.EXE, are installed and registered on THINKPAD. Debugging
disabled for connection 55.

If I turn it off with:

exec sp_sdidebug 'legacy_off'

it will run the proc on the client, without errors, but it won't stop on any
break points.

Could it be a password issue? Do the SQLDebugger accounts on both machines
have to have the same password or be in the same groups? I have added the
Everyone group to the Access and Launch security on the server but it still
won't stop on breakpoints or let me single step.

Any additional ideas would be greatly appreciated. When I get it fixed,
I'll put together a document on how to fix it.

Thanks again,

-Steve

"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns945F815 DACF21Yazorman@ 127.0.0.1...[color=blue]
> Steve Meier (snaggs34@hotma il.com) writes:[color=green]
> > SQLServer Developer Edition on Machine A
> >
> > Enterprise Manager running on remote machine B
> >
> > Both machines are in the same subnet
> >
> > I open Enterprise Mgr on Machine A, right click on the Stored Proc,[/color][/color]
enter[color=blue][color=green]
> > the parameters and the debugger stops on the first line and I can single
> > step through it just fine.
> >
> > I open Enterprise Mgr on Machine B and connect to the same database on
> > Machine A, right click on the same Stored Proc, enter the same
> > parameters and the debugger doesn't stop, but it runs the proc al the
> > way through. Even if I set a break point on one of the lines from this
> > machine it doesn't stop. I don't get any errors, it just runs right
> > thought the proc.
> >
> > I've been through all the DCOM settings, Logins with Admin rights and
> > not a SYSTEM account. Checked all the debug files and have them all
> > registered.[/color]
>
> Which versions of the tools and server do you have on the machines? I
> believe there is a problem if you run Query Analyzer SP2 or earlier
> against a server that runs SP3. Microsoft did some security fixes in SP3
> which resulted in this effect.
>
> Also, make sure that SQL Server is running on a domain account and not
> as Local System.
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Steve Meier]

Re: SQL Debugger not stopping on remote machine

I also noticed that in the database properties...

Right click on the database name on the server, select properties, click on
the Security Tab. At the bottom is shows startup information. It was set
to "System Account", although in the services section under the logon tab it
showed "This Account" and had the administrator account.

Why were these two not in sync?

Set to both to use "This Account" and gave the same account information for
both and it still won't stop on break points.

I have a book on SQL Server and it makes mention of this:

"...if the server is using the local system account and you are running a
Windows 2000 or Windows NT client, you will wee the folliwng message box
when you run the debugger. --SP Debugging may not work properly if you log
on as 'Local System Account' while SQL Server is configured to run as a
service. You can open Even Viewer to see details. Do you wish to
continue?"

it goes on to say...

"If you continue, the debugger will load, but you will not be able to set
breakpoints or step through the procedure. If you look in the system
application log using Event Viewer, you will see this error rmessage logged,
from source SQLDebugging98: SQL Server shen started as a service must not
log on as System Account. Rest to logn as user account using Control
Panel."

However I don't see any of these messages in the Event Log and I have the
Security Audit level set to ALL. I have stopped and started SQL Server
after making a change.

Any ideas?

-Steve

"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns945F815 DACF21Yazorman@ 127.0.0.1...[color=blue]
> Steve Meier (snaggs34@hotma il.com) writes:[color=green]
> > SQLServer Developer Edition on Machine A
> >
> > Enterprise Manager running on remote machine B
> >
> > Both machines are in the same subnet
> >
> > I open Enterprise Mgr on Machine A, right click on the Stored Proc,[/color][/color]
enter[color=blue][color=green]
> > the parameters and the debugger stops on the first line and I can single
> > step through it just fine.
> >
> > I open Enterprise Mgr on Machine B and connect to the same database on
> > Machine B, right click on the same Stored Proc, enter the same
> > parameters and the debugger doesn't stop, but it runs the proc al the
> > way through. Even if I set a break point on one of the lines from this
> > machine it doesn't stop. I don't get any errors, it just runs right
> > thought the proc.
> >
> > I've been through all the DCOM settings, Logins with Admin rights and
> > not a SYSTEM account. Checked all the debug files and have them all
> > registered.[/color]
>
> Which versions of the tools and server do you have on the machines? I
> believe there is a problem if you run Query Analyzer SP2 or earlier
> against a server that runs SP3. Microsoft did some security fixes in SP3
> which resulted in this effect.
>
> Also, make sure that SQL Server is running on a domain account and not
> as Local System.
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: SQL Debugger not stopping on remote machine

Steve Meier (snaggs34@hotma il.com) writes:[color=blue]
> I also noticed that in the database properties...
>
> Right click on the database name on the server, select properties, click
> on the Security Tab. At the bottom is shows startup information. It
> was set to "System Account", although in the services section under the
> logon tab it showed "This Account" and had the administrator account.
>
> Why were these two not in sync?
>
> Set to both to use "This Account" and gave the same account information
> for both and it still won't stop on break points.[/color]

And you stop and start the server? And to be really sure check in Task
Manager that it was running as Administrator.

You mentioned that you only had a workgroup. This is beyond my field of
expertise, but it is possible that it could have something to do with it.
I have had some problem getting Windows Authentication to work in my little
workgroup. (I can't test the debugger, because the workgroup consists
of my physical machine and three VMware machines running either SQL Yukon
or SQL 6.5.)

Then again, if you can log in with Windows Authentication from one server
to another, I don't really see why the debugger would fail...

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp