Related to Extended stored procedure, I find some very good article related to it. I think this published resources can help you to build you solid knowledge related to it.
If you've worked with versions of SQL Server prior to SQL Server 2005, you are probably familiar with extended stored procedures. These stored procedures reside in the master database and have names that begin with xp_. 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. Although extended stored procedures reside in the master database name when it is invoked from a database other than master, as in the following example: database like system procedures, the procedure name has to be fully qualified with the master
exec master..xp_fixeddrives
Extended stored procedures are not built with T-SQL commands; instead, they map to a function stored in a DLL. Historically, extended stored procedures were the mechanism available to extend SQL Server functionality. However, the introduction of CLR procedures provides a much easier, safer way to extend the functionality of SQL Server 2008.
Extended stored procedures are typically written in Microsoft C or Visual C++, using the Microsoft Extended Stored Procedure API, and coding them can be quite complex. In addition, extended stored procedures run under the same security context as SQL Server and within the same address space. A poorly written extended stored procedure could bring down the SQL Server service. CLR procedures, in contrast, are written in .NET code that is type safe and runs within the App domain boundary so it cannot access random SQL Server memory locations. In other words, it is much easier and safer to create and deploy CLR procedures than extended stored procedures.
Because of the unsafe nature of extended stored procedures, and the greater security and capabilities of CLR stored procedures, extended stored procedures are a feature that will very likely be removed in some future version of Microsoft SQL Server. For new development efforts, you should use CLR procedures instead of extended stored procedures. In addition, you should make plans to convert any existing applications that currently use extended stored procedures to use CLR procedures instead.
Adding Extended Stored Procedures to SQL Server
If you happen to have a DLL that contains one or more extended stored procedures you need to add to SQL Server, you can 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. The sp_addextended procedure adds an entry for the extended stored procedure to the system catalogs and registers the DLL with SQL Server. You must provide the complete path for the DLL when registering it with SQL Server.
To remove an extended procedure from SQL Server, you use sp_dropextendedproc:
sp_dropextendedproc [ @functname = ] 'procedure'
Because extended stored procedure DLLs 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. For this reason, it is strongly recommended that CLR procedures be considered as an alternative to extended stored procedures. If there is some compelling reason to use extended stored procedures, they should be thoroughly tested and verified before they are installed.
Obtaining Information on Extended Stored Procedures
To obtain information on the extended stored procedures in SQL Server, you use sp_helpextendedproc as follows:
sp_helpextendedproc [ [@funcname = ] 'procedure' ]
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. You can display them in SSMS under the master database. To do so, you open the Programmability folder for the master database and then open the Extended Stored Procedures folder. The provided extended stored procedures are listed in the System Extended Stored Procedures folder.
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, an undocumented procedure might not be included in a later version of SQL Server, or if it is included, it might behave differently than it does now.
Table contains the lists the general categories of extended stored procedures.
Extended Stored Procedures Categories
| |
Category
| |
Description
|
General extended procedures
| |
Provide general functionality. Perhaps the most useful is xp_cmdshell, which executes external programs and returns the output from them as a result set.
|
SQL Mail extended procedures
| |
Enable you to perform email operations from within SQL Server.
|
SQL Server Profiler extended procedures
| |
Are used by SQL Server Profiler. They can also be used directly, for instance, to create a trace queue and start the trace from within a stored procedure.
|
OLE automation procedures
| |
Allow SQL Server to create and use OLE automation objects.
|
API system stored procedures
| |
Are undocumented extended stored procedures used by the API libraries. The server cursor functionality, for instance, is implemented as a set of extended stored procedures.
|
One of the most useful, and potentially dangerous, extended stored procedures provided with SQL Server 2008 is xp_cmdshell. xp_cmdshell can execute any operating system command or program available on the SQL Server system, as long as it is a console program that doesn't require user input. xp_cmdshell accepts a varchar(8000) (or nvarchar(4000)) value as the command string to be executed, and it returns the results of the command as a single nvarchar(255) column. The full syntax of xp_cmdshell is as follows:
xp_cmdshell { 'command_string' } [ , no_output ]
If the no_output option is specified, the results from the command are not displayed. 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 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 invoked via xp_cmdshell does not prompt for user input. Commands invoked via xp_cmdshell do not run interactively, so there is no way to respond to the user input prompt. The SQL Server session waits indefinitely for a command invoked via xp_cmdshell to return.
After SQL Server passes off the xp_cmdshell command to the operating system, SQL Server cannot interact with the command. If the command requires user input, the process waits 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 doesn't help either. Sometimes, you might have to stop and restart SQL Server to make the process finally go away. Alternatively, you may be able to use Task Manager on the system where SQL Server is running to identify the system process that corresponds to the process invoked by xp_cmdshell and end the process.
If xp_cmdshell is invoked from another database, it has to be fully qualified as master..xp_cmdshell. Unlike with system procedures, SQL Server doesn't automatically look for extended stored procedures in the master database.
Because of the potentially dangerous nature of xp_cmdshell (it essentially allows a user to run operating system–level commands on the SQL Server machine), it is disabled by default. To enable xp_cmdshell, you must run the following commands:
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
As an additional security measure in SQL Server 2008, by default, permission to execute xp_cmdshell is limited to users with CONTROL SERVER permission. The Windows process spawned by xp_cmdshell runs within the security context of the account under which the SQL Server service is running. Essentially, it has the same security rights as the SQL Server service account.
When xp_cmdshell is invoked by a user who is not a member of the sysadmin fixed server role, it fails unless a proxy account has been set up. A proxy account is a Windows account that a system administrator defines and sets a security context for within the Windows environment. 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 defined proxy account.
The proxy account for xp_cmdshell can be created by executing sp_xp_cmdshell_proxy_account. The syntax of this command is as follows:
sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password' } ]
For example, the following command creates a proxy credential for the Windows domain user Developer\tom that has the Windows password ss2k5Unl:
sp_xp_cmdshell_proxy_account 'Developer/tom' , 'ss2k5Unl'
If NULL is passed as account_name, the proxy credential is deleted.
Because of the potential havoc that could be wreaked on your database server if xp_cmdshell got into the wrong hands, it is recommended that the capability to run xp_cmdshell be left disabled. If you must use xp_cmdshell, be very careful about who has access to it by limiting it to only those with sysadmin permissions if at all possible. If for some reason xp_cmdshell must be made available to all users, be sure that the permissions granted to the proxy account are restricted to the minimum permissions required to perform the commands that need to be invoked via xp_cmdshell.Bottom of Form
|
No comments:
Post a Comment