B# .NET Blog

Preparing a little demo on SQL Server 2005 CLR showing the benifits of the CLR compared too the dangers of using extended procedures to extend the database's functionality. I decided to start Visual C++ 6.0 (:o), create a new project, choose the Extended Stored Procedure Wizard and call the project xpdemo. In the first step (which is the last step too) of the wizard, I'm giving the procedure the name xp_crash. Next, add two lines of code in the xp_crash method, as shown below:

#include <stdafx.h>

#define XP_NOERROR              0
#define XP_ERROR                1
#define MAXCOLNAME    25
#define MAXNAME     25
#define MAXTEXT     255

#ifdef __cplusplus
extern "C" {
#endif

RETCODE __declspec(dllexport) xp_crash(SRV_PROC *srvproc);

#ifdef __cplusplus
}
#endif

RETCODE __declspec(dllexport) xp_crash(SRV_PROC *srvproc)
{
 //crash me
 int *p = NULL;
 *p = 0;

 return XP_NOERROR ;
}

Ready to build. Next, the xpcopy.dll file is copied to the Binn folder of the SQL Server. Finally, go to Query Analyzer to register the extended procedure:

exec sp_addextendedproc 'xp_crash','xpdemo.dll'

Now when running the xp_crash extended procedure, SQL Server goes down, showing the danger of extending the database functionality with unmanaged code:

ODBC: Msg 0, Level 19, State 1
FCallRpcDll: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
ODBC: Msg 0, Level 20, State 1
Stored function 'xp_crash' in the library 'xpdemo.dll' generated an access violation. SQL Server is terminating process 51.

Connection Broken

Now, when running SQL Server not as a service but as a process (just by starting sqlservr.exe in the binn folder when the service is not started), you'll get detailed information in the process window of SQL Server:

2005-07-24 01:55:31.64 spid51    Using 'xpdemo.dll' version 'UNKNOWN' to execute
 extended stored procedure 'xp_crash'.
2005-07-24 01:55:31.78 spid51    Using 'dbghelp.dll' version '4.0.5'
*Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQLDum
p0001.txt
2005-07-24 01:55:31.82 spid51    Error: 0, Severity: 19, State: 0
2005-07-24 01:55:31.82 spid51    FCallRpcDll: Process 51 generated fatal excepti
on c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..

* ******************************************************************************
*
*
* BEGIN STACK DUMP:
*   07/24/05 01:55:31 spid 51
*
*   Exception Address = 04231072 (xp_crash + 00000022 Line 23+00000003)
*   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
*   Access Violation occurred writing address 00000000
* Input Buffer 30 bytes -
*  exec xp_crash
*
*
*  MODULE                          BASE      END       SIZE
* sqlservr                       00400000  00CBAFFF  008bb000
* ntdll                          7C800000  7C8BFFFF  000c0000
* kernel32                       77E40000  77F41FFF  00102000
* ADVAPI32                       77F50000  77FEBFFF  0009c000
* RPCRT4                         77C50000  77CEEFFF  0009f000
* MSVCP71                        7C3A0000  7C41AFFF  0007b000
* MSVCR71                        7C340000  7C395FFF  00056000
* opends60                       41060000  41065FFF  00006000
* SHELL32                        7C8D0000  7D0D2FFF  00803000
* GDI32                          77C00000  77C47FFF  00048000
* USER32                         77380000  77411FFF  00092000
* msvcrt                         77BA0000  77BF9FFF  0005a000
* SHLWAPI                        77DA0000  77DF1FFF  00052000
* sqlsort                        42AE0000  42B6FFFF  00090000
* ums                            41070000  4107DFFF  0000e000
* comctl32                       77420000  77522FFF  00103000
* sqlevn70                       41080000  4108AFFF  0000b000
* NETAPI32                       02100000  02157FFF  00058000
* AUTHZ                          02160000  02173FFF  00014000
* COMRES                         02410000  024D5FFF  000c6000
* ole32                          77670000  777A3FFF  00134000
* XOLEHLP                        024E0000  024E5FFF  00006000
* MSDTCPRX                       024F0000  02567FFF  00078000
* msvcp60                        780C0000  78120FFF  00061000
* MTXCLU                         02570000  02588FFF  00019000
* VERSION                        77B90000  77B97FFF  00008000
* WSOCK32                        02590000  02598FFF  00009000
* WS2_32                         025A0000  025B6FFF  00017000
* WS2HELP                        025C0000  025C7FFF  00008000
* OLEAUT32                       77D00000  77D8BFFF  0008c000
* CLUSAPI                        025D0000  025E1FFF  00012000
* RESUTILS                       025F0000  02602FFF  00013000
* USERENV                        02610000  026D3FFF  000c4000
* secur32                        026E0000  026F2FFF  00013000
* mswsock                        02710000  02750FFF  00041000
* DNSAPI                         02760000  02788FFF  00029000
* winrnr                         027D0000  027D6FFF  00007000
* WLDAP32                        027E0000  0280DFFF  0002e000
* rasadhlp                       02830000  02834FFF  00005000
* SSNETLIB                       03000000  03015FFF  00016000
* NTMARTA                        77E00000  77E21FFF  00022000
* SAMLIB                         00030000  0003EFFF  0000f000
* security                       034F0000  034F3FFF  00004000
* crypt32                        03500000  03592FFF  00093000
* MSASN1                         03640000  03651FFF  00012000
* schannel                       03660000  03686FFF  00027000
* rsaenh                         036A0000  036CEFFF  0002f000
* PSAPI                          03750000  0375AFFF  0000b000
* dssenh                         03760000  03783FFF  00024000
* hnetcfg                        037B0000  03808FFF  00059000
* wshtcpip                       03810000  03817FFF  00008000
* wship6                         03820000  03826FFF  00007000
* SSmsLPCn                       038B0000  038B7FFF  00008000
* SSnmPN70                       410D0000  410D6FFF  00007000
* ntdsapi                        03970000  03984FFF  00015000
* msv1_0                         03990000  039B6FFF  00027000
* iphlpapi                       039E0000  039F9FFF  0001a000
* SQLFTQRY                       41020000  41045FFF  00026000
* xpsp2res                       10000000  102C4FFF  002c5000
* CLBCatQ                        777B0000  77832FFF  00083000
* SQLOLEDB                       03440000  034C0FFF  00081000
* MSDART                         034D0000  034E9FFF  0001a000
* MSDATL3                        03D70000  03D84FFF  00015000
* oledb32                        03F90000  04008FFF  00079000
* OLEDB32R                       04010000  04020FFF  00011000
* xpdemo                         04230000  04267FFF  00038000
* dbghelp                        04280000  0437FFFF  00100000
*
*        Edi: 0412DD20: 0412EAC8  00792893  42C020C0  635F7078  68736172  0412DD
00
*        Esi: 00000001:
*        Eax: 00000000:
*        Ebx: 42C020C0: 42C025B8  00000000  00000002  00040308  42C02038  000000
00
*        Ecx: 00000000:
*        Edx: 00000000:
*        Eip: 04231072: 000000C7  C0330000  8B5B5E5F  CCC35DE5  CCCCCCCC  CCCCCC
CC
*        Ebp: 0412DD20: 0412EAC8  00792893  42C020C0  635F7078  68736172  0412DD
00
*      SegCs: 0000001B:
*     EFlags: 00010212: 00720065  0063005C  0075006C  00740073  00720065  006C00
2E
*        Esp: 0412DCD0: 0423100A  00000001  42C020C0  CCCCCCCC  CCCCCCCC  CCCCCC
CC
*      SegSs: 00000023:
* ******************************************************************************
*
* ------------------------------------------------------------------------------
-
* Short Stack Dump
* 04231072 Module(xpdemo+00001072) (xp_crash+00000022 Line 23+00000003)
* 00792893 Module(sqlservr+00392893) (SQLExit+0022AD9C)
* 00770DB8 Module(sqlservr+00370DB8) (SQLExit+002092C1)
* 0062EC6C Module(sqlservr+0022EC6C) (SQLExit+000C7175)
* 0050BCEF Module(sqlservr+0010BCEF)
* 0050BB13 Module(sqlservr+0010BB13)
* 00415D04 Module(sqlservr+00015D04)
* 00416214 Module(sqlservr+00016214)
* 00415F28 Module(sqlservr+00015F28)
* 0049C32E Module(sqlservr+0009C32E)
* 0049C46A Module(sqlservr+0009C46A)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line 456+00000000)

* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line 263+00000007)

* 7C34940F Module(MSVCR71+0000940F) (threadstart+0000006C Line 196+00000006)
* 77E66063 Module(kernel32+00026063) (GetModuleFileNameA+000000EB)
* ------------------------------------------------------------------------------
-
2005-07-24 01:55:36.00 spid51    Stack Signature for the dump is 0x9A8489B4
2005-07-24 01:55:36.02 spid51    Error: 0, Severity: 20, State: 0
2005-07-24 01:55:36.02 spid51    Stored function 'xp_crash' in the library 'xpde
mo.dll' generated an access violation. SQL Server is terminating process 51..

The short stack dump shows me where the error occurred:

* Short Stack Dump
* 04231072 Module(xpdemo+00001072) (xp_crash+00000022 Line 23+00000003)

which points to line 23:

 *p = 0;

That's right indeed. If you do want more information however, you can use WinDbg to do further debugging:

1. Attach sqlservr.exe to the debugger using F6
2. Dump the thread stacks using ~*kv
3. Dump the TEB of the current thread (typically the last worker thread of SQL Server) using !teb; here you'll find information about thread local storage, fiber data, the last error value (which can be very handy; use net helpmsg #nr to find a textual description of the error)
4. Dump the process execution block of the process (the PEB) using !peb

Nice (:s) and easy (h) demo of the extended procedure hell. Don't try this on your production box please :-)

Security tip:

If you can (and most people can), disable xp_cmdshell by running the following command: sp_dropextendedproc 'xp_cmdshell'. xp_cmdshell is a big target for hackers and attackers, which opens a big door to the whole server if SQL Server is running as a high-priviliged account. Typical situation: SQL injection vulnerability in web application, web application running as sa, SQL Server running as SYSTEM, ... You can complete the rest of the story yourself ;-).