1 / 10

Presentation on xp_cmdshell

Presentation on xp_cmdshell. -By Hussain & Nikhil. Using XP_CMDSHELL on SQL Server 2008.

abra-clements
Download Presentation

Presentation on xp_cmdshell

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Presentation on xp_cmdshell -By Hussain & Nikhil

  2. Using XP_CMDSHELL on SQL Server 2008 • So in order to use xp_cmdshell whether you are a sysadmin or a regular user you need to first enable the use of xp_cmdshell.  Doing this is similar to what was done when enabling the dedicated administrator connection.  This can be done by using sp_configure: • sp_configure ‘xp_cmdshell’, ’1′

  3. Next Steps • Check whether or not you are using xp_cmdshell for previous versions of SQL Server and enable it if it is still needed when you make the upgrade to SQL 2008. • With a new installation of SQL Server 2008 check to see if you really need this option enabled. • From a security perspective it is best to minimize any risks, so keep this option disabled makes the most sense unless there is a real need.

  4. What is XP_CMDSHELL • XP_CMDSHELL – extended stored procedure. • With this extended stored procedure you have the ability to run any command line process within your SQL script, so you can embed this within your stored procedures, jobs or batch processing. • With SQL Server 2008 the use of xp_cmdshell is turned off by default.

  5. Examples of XP_SMDSHELL Commands 1) Let’s start out with a simple directory search example. Say you do not have access to the physical SQL Server machine, but you want to see all the files in the “C:\temp” directory that have a “.sql” extension. You can do this by issuing the following command: • exec master.dbo.xp_cmdshell ‘dir c:\temp\*.sql’

  6. Execute SQL script via SQLCMD (OSQL) using XP_CMDSHELL • exec master.dbo.xp_cmdshell ‘osql -E -Sserver1 -i c:\temp\nightly.sql’ • Here I have used xp_cmdshell to execute the SQL Server osql utility to process the T-SQL commands contained in the script file “c:\temp\nightly.sql”. See how easy it is to execute a T-SQL batch script via a stored procedure using the xp_cmdshell extended stored procedure!

  7. XP_CMDSHELL – Extended Stored Procedure • “xp_cmdshell” is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code. If needed the output of these commands will be returned to the calling routine.

  8. Now not just anyone can run this extended stored procedure. If you want to execute this extended stored procedure, you will either need to be a member of the sysadmin role, or have the xp_sqlagent_proxy_account set up on your SQL Server

  9. Enabling and Disabling Features Enabling and disabling SQL Server features can be configured using facets in SQL Server Management Studio. To configure surface area using facets • In Management Studio connect to a component of SQL Server. • In Object Explorer, right-click the server, and then click Facets. • In the View Facets dialog box, expand the Facet list, and select the appropriate Surface Area Configuration facet (Surface Area Configuration, Surface Area Configuration for Analysis Services, or Surface Area Configuration for Reporting Services).

  10. In the Facet properties area, select the values that you want for each property. • Click OK.

More Related