Share via

SQL Server 2005 Debugging Requirements

  • Article

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.

posted by Wang.Haitao on https://blogs.msdn.com/sqlprogrammability/ 

Comments

  • Anonymous
    July 10, 2006
    I think MS is on the fence with debugging and has continuity issues larger than just the SQL product.  Why do similar things differently, develop one way, debug one way, etc.  That is what is touted by the MS propaganda isn't it.  The more years pass, the more it stays the same, but with a different look and is not getting better, just different.  This may be the continuity MS wants, but not developers and users.

    ...And make sure you take away important features like tsql debugging from the sql product and the sql agent job scheduler from express.  I am sure this makes sense to someone that has never used the product.

  • Anonymous
    July 11, 2006
    Firstly thanks for a really useful article and a great blog. I will be adding you to my RSS feed.

    This is a real issue in any decent sized corporate environment. Even dev servers will be standardised and well managed and therefore Sa access will be restricted to the DBA Team. I understood that one of the main benefits of VS2005 is the seamless debug between managed code and SQL and in our environment the Sa requirement prevents us leveraging this.

    Obviously there are workarounds (ie temp grant access, local / SQL Express copy of SQL Server) but these create a manageability headache which again makes them unworkable in a Corporate environment.

    We still see quite a few examples in MS Products of ignorance of corporate realties , so it would be good if these are thought through / discussed with your larger customers more. I'm happy to do this over a Red Rock / Pyramid or two :-)

    thanks again,
    Andy.



  • Anonymous
    July 12, 2006
    The problem with debugging is that you really DO have to have access to the process's memory to be able to do anything useful. And if they added some new "debugging" role to allow people to debug, it'd be the same as the sysadmin role anyway (at least, it would be possible for a "debugging" user to trick the server into thinking they were a "sysadmin"). Essentially, the "debugging" role would be synonymous with the sysadmin role.

    At least requiring the sysadmin role makes it very clear to DBAs the sort of permissions you're giving developers. This'll make it clear that they probably should NOT be giving it to developers on a production server. If they had available some "debugging" role, they might be tempted to think "Oh, it's just debugging, that's not so bad" when in fact it really is.

  • Anonymous
    July 13, 2006
    The comment has been removed

  • Anonymous
    July 13, 2006
    The comment has been removed

  • Anonymous
    July 13, 2006
    Adding a VISIO diagram explaining this would be of great help too.

  • Anonymous
    July 29, 2006
    great help for sysadmin

  • Anonymous
    August 23, 2006
    The comment has been removed

  • Anonymous
    September 25, 2007
    My current task in our project is majorly in MSSQL. I wanted to debug a chain of stored procedures and

  • Anonymous
    June 23, 2008
    <p>Many people have asked where the TSQL debugger is for SQL Server 2005.</p> ...