K.Watterson, B.Shadish, G.Wells
10 Projects You Can Do With Microsoft SQL Server 7
page 470.

"... Before you can load the SQL Debugger Add In, you need to make sure the following prerequisites are meet:
1. The MSSQLServer Service run under the Domain Account.
2. The Remote Procedure Call (RPC) Service is running.
3. A Data Source Name (DSN) has been created for the database in which stored procedure is located.
4. The Sdi.dll file MUST be located in C:\MSSQL7\BINN subdirectory. If not, you can obtain a copy from SQL Server7 CD in the x86\Other\SDI subdirectory."

I also recommend look at the article
http://msdn.microsoft.com/library/devprods/vs6/vinterdev/vidref/vihowdebuggingstoredprocedurestriggers.htm

and another small article
www.winntmag.com/Articles/Content/7780_01.html

I'm using Visual InterDev for debugging both SP and triggers. It works good.