Extended Stored Procedures

Open Data Services (ODS) is a server-based application programming interface (API) that you can use to create an application that is accessed just like SQL Server. Another useful application of the ODS library is creating extended stored procedures. You have probably come across stored procedures with names that begin with xp_ . These are extended stored procedures and are not built with T-SQL commands; instead, they map to a function stored in a DLL. Extended stored procedures provide a way to extend SQL Server functionality through external functions written in C or C++ that can return resultsets and output parameters from a variety of external datasources.

Extended stored procedures are invoked and managed similarly to regular stored procedures. You can grant and revoke permissions on extended stored procedures as you do for normal stored procedures. The main difference is that extended stored procedures are created only in the master database. Unlike system procedures, however, when extended procedures are invoked from a database other than master, the procedure name has to be fully qualified with the master database name , as in the following example:

 use pubs  go exec master..xp_fixeddrives 

Creating Extended Stored Procedures

Extended stored procedures are typically written in Microsoft C or Visual C++. When compiling your extended stored procedure DLLs, you need to include the srv.h header file and the Opends60.lib library file.

To create an extended stored procedure DLL in Visual C++, follow these steps:

1. Create a new Win32 Dynamic Link Library project.

2. Select the Options item on the Tools menu. In the Options dialog box, click the Directories tab and set the directory for include files and library files. Set the include files directory to C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Include and the library directory to C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib.

3. From the Project menu, choose the Settings option and in the Project Settings dialog box, click the Link tab. Choose the General category, and type opends60.lib in the Object/Library Modules text box.

4. Add your source files containing your custom extended procedure code to your project.

5. Compile and link your project.

NOTE

There's not enough room here to go into detail on writing C++ code. For more information on creating DLLs, refer to your development environment documentation and the Microsoft Win32 SDK documentation. The "Extended Stored Procedure Programming" section in SQL Server Books Online provides helpful examples of extended stored procedure code, as well as a reference to the extended stored procedure API calls.

CAUTION

Because the extended stored procedure DLL and SQL Server share the same address space, poorly written extended procedure code can adversely affect SQL Server functioning. Any memory access violations or exceptions thrown by an extended stored procedure could possibly damage SQL Server data areas. Extended procedures should be thoroughly tested and verified before they are installed.

Adding Extended Stored Procedures to SQL Server

To add an extended stored procedure to SQL Server for which you have created a DLL, use the sp_addextendedproc system stored procedure. Only SQL Server system administrators can add extended stored procedures to SQL Server. The syntax is as follows :

 sp_addextendedproc [ @functname = ] '  procedure  ' ,  [ @dllname = ] '  dll  ' 

Extended stored procedures are added only in the master database. sp_addextended procedure will add an entry for the extended stored procedure to the sysobjects and syscomments tables in the master database and register the DLL with SQL Server. To remove an extended procedure from SQL Server, use sp_dropextendedproc :

  sp_dropextendedproc  [  @functname =  ] '  procedure  ' 

Obtaining Information on Extended Stored Procedures

To obtain information on the extended stored procedures in SQL Server, use sp_helpextendedproc as follows:

  sp_helpextendedproc  [ [  @funcname =  ] '  procedure  ' ] 

If the procedure name is specified, sp_helpextendedproc lists the procedure name along with the DLL that is invoked when the extended stored procedure is executed. If no procedure name is passed in, sp_helpextendedproc lists all extended stored procedures that are defined in SQL Server and their associated DLLs.

Extended Stored Procedures Provided with SQL Server

Most of the extended stored procedures that ship with SQL Server are undocumented. All extended stored procedures (or rather, the references to them) are stored in the master database. A folder in SQL Enterprise Manager under the master database lists the extended stored procedures.

If you plan to use an undocumented extended stored procedure, be careful. First, you have to find out what it does and what parameters it takes. You should also be aware that Microsoft does not support the use of undocumented extended stored procedures. Moreover, the procedure might not be included in a later version of SQL Server, or if it is included, it might behave differently.

Table 28.8 lists the categories of extended stored procedures.

Table 28.8. Extended Stored Procedures Categories

Category	Description
General extended procedures	General functionality. Perhaps the most useful is xp_cmdshell , which executes external programs and returns the output from them as a resultset.
SQL Mail extended procedures	Used to perform e-mail operations from within SQL Server.
SQL Server Profiler extended procedures	Used by SQL Server Profiler. These can also be used directly, for instance, to create a trace queue and start the trace from within a stored procedure.
OLE automation procedures	Allows SQL Server to create and use OLE automation objects.
API system stored procedures	Undocumented extended stored procedures used by the API libraries. The server cursor functionality, for instance, is implemented as a set of extended stored procedures.

Using xp_cmdshell

One of the most useful extended stored procedures is xp_cmdshell . xp_cmdshell can execute any operating system command or program (as long as it is a console program that doesn't require user input). The following example uses xp_cmdshell to list the files in a directory on the SQL Server computer's hard disk:

 EXEC xp_cmdshell 'DIR c:\*.*' 

xp_cmdshell returns a resultset of one nvarchar(255) column. A common use of xp_cmdshell is to dynamically execute a series of BCP commands to export data from a database rather than having to create a script file. (For an example, see the BCP_out_AllTables stored procedure in Chapter 20, "Importing and Exporting SQL Server Data Using BCP and DTS.") xp_cmdshell runs synchronously. Control is not returned to the SQL Server user session until the shell command completes. This is why you have to ensure that the shell command does not prompt for user input (for example, running the bcp command and not providing a password on the command line, requiring a prompt for it).

CAUTION

After SQL Server passes off the xp_cmdshell command to the operating system, SQL Server cannot interact with the command. The process will wait indefinitely and it usually doesn't go away without a fight. Killing the process in SQL Server usually just leaves it in a KILLED/ROLLBACK state. Closing the session that invoked the xp_cmdshell statement won't help either. Usually, you need to stop and restart SQL Server for it to finally go away.

If xp_cmdshell is invoked from another database, it has to be fully qualified as master..xp_cmdshell . Unlike system procedures, SQL Server doesn't automatically look for extended stored procedures in the master database.

By default, permission to execute xp_cmdshell is granted only to users with the sysadmin server role. For these users, xp_cmdshell runs under the account that the SQL Server service is running under, with all of the rights granted to this account. At a minimum, this account has administrative rights on the machine on which SQL Server is running. If you need to access shared resources on the network via xp_cmdshell , the account that SQL Server is running under will also need to be a domain user account afforded the appropriate rights necessary to access those shared resources.

Permission to execute xp_cmdshell can be granted to other users who are not members of the sysadmin role. However, for these users, xp_cmdshell does not run under the SQL Server service account, but under a special account: the SQL Server Agent proxy account.

In SQL Server 6.5, if non-sysadmin users were granted permission to execute xp_cmdshell , by default xp_cmdshell commands ran under the account the SQL Server service ran under. Because this was typically an administrative account, that meant that any users with permission to run xp_cmdshell could invoke administrative level commands that could be potentially dangerous to the system. SQL Server administrators could close this gaping hole by setting a SQL Server option to restrict non-SA execution of xp_cmdshell commands to run under a special account, SQLExecutiveCmdExec . This was a local account whose password was known only internally to SQL Server. As a local, and not a domain account, it had no rights to access shared resources on other servers in the domain.

Microsoft recognized this potential security hole and patched it in version 7.0. Unfortunately, for many shops , the patch was too big. Non-sysadmin users could only run xp_cmdshell under the local account SQLAgentCmdExec . This account could be granted rights on the local SQL Server machine, but because it was only a local account, it could not be granted access rights in the NT Domain. Therefore, non-sysadmin users couldn't access shared directories on other servers via xp_cmdshell . Although this fixed a potential security hole, the net effect was that it often ended up resulting in a bigger one. For those applications that needed access to shared network resources via xp_cmdshell , some shops would change their user applications to run under a SQL Server sysadmin account!

Fortunately in SQL Server 2000, they fixed the "fix" by allowing a proxy account to be associated with SQL Server Agent instead of using a fixed account. A proxy account is a Windows account that a system administrator defines and sets a security context for within the Windows environment. When xp_cmdshell is run by a member of the sysadmin group, it still runs within the security context of the account under which the SQL Server service is running. However, when a user who is not a member of the sysadmin group runs xp_cmdshell , the commands are run within the security context of the SQL Server Agent proxy account. If no proxy account has been defined, xp_cmdshell will fail.

A proxy account is configured using the xp_sqlagent_proxy account extended stored procedure:

 xp_sqlagent_proxy_account {'GET'  'SET'}, '  agent_domain_name  ',                            '  agent_username  ', '  agent_password  ' 

A Windows or network administrator needs to first set up a Windows or a domain account and configure it with the appropriate permissions necessary to access the resources needed by xp_cmdshell when xp_cmdshell is executed by a non-sysadmin user. Assume the network administrator sets up an account called SQLProxy with a password of "unleashed" in the SMALLWORLD domain. This account is then granted rights to a shared directory on the network that xp_cmdshell needs to access for BCP export files. To set the SQL Agent proxy account to this account, execute the following:

 xp_sqlagent_proxy_account 'SET', 'SMALLWORLD', 'SQLProxy', 'unleashed' 

NOTE

Setting a SQL Agent proxy account will only work in Windows NT or Windows 2000. Under Windows 9.x and Windows Me, xp_cmdshell always runs under the security context of the user account that started SQL Server. No proxy account can be set for Windows 9.x or Windows Me.

To configure the SQLAgent proxy account in SQL Enterprise Manager, right-click on SQL Server Agent and choose the Properties option. Click on the Job Systems tab. Unchecking the Only Users with SysAdmin Privileges Can Execute CmdExec and ActiveScripting Job Steps check box will bring up the SQL Agent Proxy Account dialog box, as shown in Figure 28.14. Enter the username, password, and domain name and click OK to save the changes. This same dialog box is presented if you need to modify the SQLAgent proxy account information. You can bring it up by clicking the Rest Proxy Account button in the Job Systems tab of the SQL Server Agent Properties dialog box.

Figure 28.14. Setting the SQLAgent proxy account in SQL Enterprise Manager.