SQL Server 2005 Debugging Requirements

There have been some questions about the sysadmin requirement of SQL Server 2005 Debugging, and I’d like to explain it in some details. When you debug T-SQL or CLR code in SQL Server 2005, there are two users involved: user running the debugger and user making the connection that is being debugged. User running the debugger (Visual Studio 2005) has to be in the sysadmin fixed role, and there’s no requirement on the user making the connection. Also because Visual Studio communicates with SQL Server through debugging interfaces in DCOM, the debugger user has to use Windows Authentication rather than SQL Server Authentication.

For CLR code debugging, sysadmin is required because CLR debugger user has total access to the memory of SQL Server process, and we don’t want anyone other than sysadmin to have it.

For T-SQL debugging some alternatives have been considered. One alternative is to allow anyone to debug T-SQL procedure/function that s/he has certain permission (e.g. alter permission or ownership). This would be much more convenient for developers, but it has some security complications, especially in cases like procedure with EXECUTE AS and signed procedures. Also filtering of T-SQL stack frames based on permissions make implementation more complex. We gave up on this for SQL Server 2005, and will reconsider it for future versions. Another alternative is to make execute permission on sp_enable_sql_debug grantable and allow anyone with this permission to debug T-SQL. After security review we found that without solving security problems that prevented us from the first alternative, it’s possible for a malicious debugging user to elevate to sysadmin privilege. Thus debugging permission is equivalent to sysadmin privilege and we chose to signify this by only allowing sysadmin to execute sp_enable_sql_debug.

Remote Debugging Monitor (msvsmon.exe) is another requirement that people often get confused on. Remote Debugging Monitor is required for SQL-CLR debugging, whether remotely or locally (here are the steps to set up the Remote Debugging Monitor); and it is not required for T-SQL debugging, whether remotely or locally. Here “local” means Visual Studio 2005 and SQL Server 2005 run on the same machine.

For T-SQL debugging Visual Studio doesn’t actually attach to the SQL Server process. It communicates with SQL Server through a set of debugging interfaces in DCOM, so msvsmon.exe is not required. For SQL-CLR debugging msvsmon.exe is required even for local debugging for robustness reasons. In this case msvsmon.exe attaches to the SQL Server process, and Visual Studio talks to msvsmon.exe through some private channels. In this way even if Visual Studio crashes or freezes, msvsmon.exe can detect it and detach safely from SQL Server process. If Visual Studio attaches to the SQL Server process directly, and something bad happens to Visual Studio, then SQL Server process can be terminated, which is what we try to avoid. Msvsmon.exe is relatively small and we can make it pretty robust; whereas Visual Studio is much more complex and has open plug-in architecture, and thus is much more susceptible to problems.

Comments

• Anonymous
  May 14, 2007
  Hi, I was just following your posts as regards Debugging using Sql server 2005.And it solved some issues for me.Thanks for the same I have a question regarding something which is hampering my work: Supposing in a sql procedure I have done following steps 1.Created a temp table @t 2.Taken data from 2-3 tables,did some calculations and inserted that data into the table @t -Now I want the SQL debugger to tell me the value in table @t. Can this be done? IF so can you point me to the article on this. Thanks, Ketan Kalia PS:I don't have a url as yet.If I am not hassling you, can you just write comment on your url or mail me at ketankalia@hotmail.com,that would be really nice

• Anonymous
  May 15, 2007
  The comment has been removed

• Anonymous
  June 27, 2007
  I am currently writing a SQL 2005 debugger to include in a SQL IDE product I develop and was wondering if there are any plans to make it possible to do some of what Ketan mentioned, specifically in regards to getting the contents of table variables. I am able to query the contents of temp tables (ie. #table or ##table) during the debugging session and show contents to the user but not for table variables.  Just curious if Katmai will add this feature? Also, just wondering if you know a little about the DCOM interface for the SQL 2005 debugger.  Specifically, how to set/get a NULL value to a parameter.  I have been unable to determine how to specifically set NULL to a variable during a break.  I am also unable to determine the difference between NULL and empty string in getting the values.  Just curious if you had any knowledge.

• Anonymous
  June 27, 2007
  The comment has been removed

• Anonymous
  July 18, 2007
  Sorry for late response.  Table variable inspection support is still being considered but probably won't make into Katmai. As to NULL vs. empty string.  When you call IHostDebug::SetSym, you can specify HOST_DEBUG_SYMBOL_PROPERTIES_NULL in HOST_DEBUG_SYMBOL.m_ulProperties to tell SQL Server you are setting a NULL value (also works for other types such as int).  Unfortunately in values returned from IHostDebug::GetSyms, HOST_DEBUG_SYMBOL_PROPERTIES_NULL is not set for a NULL value, but you can still use HOST_DEBUG_SYMBOL_PROPERTIES_TEXT to distinguish between NULL and empty string (empty string will have HOST_DEBUG_SYMBOL_PROPERTIES_TEXT but NULL string will not).  How to present these to debugger user is up to you. Hope this helps, -Haitao

• Anonymous
  April 29, 2008
  PingBack from http://ezinefrontpageblog.info/sql-programmability-api-development-team-blog-sql-server-2005/