SQL Shell Commands

SQL Server’s Transact-SQL provides a function to execute SQL shell scripts directly from SQL. This function is called SQL Server xp_cmdshell. The function works the same way as a prompt command.

This tutorial will walk you through the process of configuring the SQL Server to allow SQL to execute SQL shell scripts and SQL prompt commands directly from SQL. In addition, the returned results can be stored in a table and can be combined with other SQL script functions and commands like any other SQL script.


How To Execute SQL Prompt Commands

Before you can execute the xp_cmdshell function in SQL Server, you will need to enable it on the SQL Server. To enable the xp_cmdshell you will need to execute the sp_Configure SQL system command while providing the proper parameters. The general syntax for the sp_Configure command is:

?
sp_Configure OptionName, ConfigValue
Reconfigure
To execute the sp_Configure command to enable the xp_cmdshell, open a new query in Sql Server Management Studio and enter the following command to enable the xp_cmdshell followed by the Reconfigure statement to install the new configuration:

?
Exec sp_configure 'xp_cmdshell', 1
Reconfigure
Note

You will need to run the xp_cmdshell using the credential that has access to the Windows server processes, like an administrator, otherwise the store procedure will not run or will issue an error.

The sp_Configure creates a new SQL Server configuration and displays the results in the SQL output. The first option is the name of the stored procedure that must be enabled on the SQL Server. The second option either enables or disables the stored procedure on the server. To enable, pass the value of '1' as a char value. To load the new configure, execute the Reconfigure SQL command.

This command alters server settings for all databases on that particular SQL Server. To alter database level settings, use the Alter Database command instead.

If you get the following message: “The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.” it is because the Advanced Options aren’t configured and you will to configure these first. To do so, issue the Advanced Options Command followed by the xp_cmdshell command as follows:



?
EXEC sp_configure 'show advanced options', 1;

GO

Reconfigure;

GO

EXEC sp_configure 'xp_cmdshell',1

GO

Reconfigure

GO
General xp_cmdshell Syntax

return codes
xp_cmdshell can return an error code of either success or failure. To capture this code to be used for further query processing such as a condition to either exit the query or to continue, define a integer variable such as:

?
DECLARE @returnCode int

Then assign the variable to the xp_cmdshell function as follows

EXEC @returnCode = xp_cmdshell âSC Start SomeWindowsServiceâ
xp_cmdshell Return Codes

Code
Message
1
Success
0
Failure


If you don’t want any output to the SSMS query screen, just append the NO_OUTPUT directive to the end of the command as the following code snippet demonstrates:

?
EXEC @returnCode = xp_cmdshell 'SC Stop SomeWindowsService', NO_OUTPUT
Security Considerations

The xp_cmdshell stored procedure runs with the same credentials as the SQL Server services account. However these credentials may not be sufficient to access the far reaches of the network and individual computers or file resources on local or network accounts. To override this constraint, you can use the variant stored function, sp_xp_cmdshell_proxy_account, which can be used to provide a valid Windows Administrator account and password with the proper accesses. This function can be executed prior to xp_cmdshell to create the proxy account settings. To create a proxy account execute the function as follows:

?
EXEC sp_xp_cmdshell_proxy_account 'WINDOWS_DOMAIN\username','password'

To remove the proxy account, execute the same function using the NULL keyword like this:

EXEC sp_xp_cmdshell_proxy_account NULL 'WINDOWS_DOMAIN\username','password'
Command Runs Synchronously

Like any SQL script or query, the xp_cmdshell runs synchronously. Meaning that the other query statements, processes or yourself cannot interact with the query while it is running. Of course you can stop the execution if the stored procedure is running in SSMS (SQL Server Management Studio) using the stop command in the toolbar. Additionally, you can use the output as any other SELECT statement and the output can be stored in tables and variables.


view quiz statistics
Store Returned Results in Tables

Like any other SELECT output, the returned result from the xp_cmdshell can be stored in temporary tables, table variables or physical tables in a SQL database. Here are the general syntax of the three types of tables and some code snippets to illustrate.

Temporary Table

In the following temporary table example, the xp_cmdshell executes the Net Config Server DOS Network command. This command returns information on the configuration of the current server. The other options would be to gather information on a workstation if the query was running on a workstation (a computer running on a network).

Temporary Table

?
--Create the table
create table #tmpTable(outputText varchar(3000))

/*
insert the the current server configuration into the #tmpTable by issuing the Net Config DOS command and passing it the Server parameter. The results will be inserted the outputText column. You don't need to specify the columns in the insert or select (in this case the EXEC) if the source and target match.
*/

insert into #tmpTable
exec xp_cmdshell 'NET CONFIG Server'

--To view the results in the #tmpTable, perform a simple select
select * from #cmdTable

--Always drop (delete) the table after use, to free memory.
drop table #cmdTable
table #cmdTable
Do you plan using the xp_cmdshell stored procedure in the future

Yes
No
Vote
See results without voting
Variable Tables

To use a table variable is very similar to the previous table example, except for the syntax of course. A table variable is only created during the the execution of the query and it is dropped once the query is complete.

To create a table variable for the xp_cmdshell output, first declare the table variable and any columns that are required as the following example demonstrates:

Variable Tables

?
--Create the table variable
DECLARE @servercfg TABLE(serverdetails VARCHAR(3000))

--Populate the table variable using an INSERT
INSERT INTO @servercfg
EXEC xp_cmdshell 'c:\java\java.exe -jar javaprogram.jar'
Naturally for this query to work, the java program would have to output the results using the System.out.println(output); statement. The example above is only a fictitious java app, but it demonstrates the syntax and the strength of the xp_cmdshell function. Virtually any executable that can be launched from the command line can also be executed from the xp_cmdshell function.

Of course Windows Applications mustn’t present an UI (user interface) since these scripts run on the server, away from prying eyes, so you cannot, say launch Microsoft Excel, unless it be for a background processing job like to refresh its contains from a web service or database without having to present an UI to the user.

The following screenshot demonstrates how to use a DOS NET command to query the server where the SQL Server is installed to return information on its configuration.

Storing xp_cmdshell output in a table variable
Storing xp_cmdshell output in a table variable | Source
Physical Tables

?
CREATE TABLE cmdtable(cmd_output varchar(4000))
INSERT INTO cmdtable
exec xp_cmdshell 'wmic MEMLOGICAL get /all'

SELECT * FROM dbo.cmdtable
Physical Table

Another form of queries that can be executed using the xp_cmdshell is to store the returned output to a physical table in a database that resides on the servers HDD. As before the table needs to be created beforehand. You cannot do a direct INSERT INTO from another table. So here is the syntax and example

SQL queries aren't case sensitive, you can mix and match UPPER and lower or ProperCase and it all means the same thing to the compiler.

The following query will extract information on the machine's memory and store the information in a physical table. Notice the output is divided into several columns for display but is stored in one physical column. To store each piece of information in its own table column would require extra query processing.

BIOS memory output using Microsoft WMI and xp_cmdshell
BIOS memory output using Microsoft WMI and xp_cmdshell | Source
Running Windows Processes

Virtually any Microsoft Windows process can be run with the xp_cmdshell function if you have the right credentials. For best results, it is best to run processes with no user interface or that can run minimize or hidden.

I have found it very useful to run Microsoft WMI (Windows Machine Instrumentation) scripts from the command line (CLI). The WMI can query every aspect of a local machine or any other machine on a local area network or wide area network. WMI is used to obtain information on every aspect of Windows based machines and to be able to act upon that information.

WMI is a great API for doing audits on machines on the network which then can be stored in tables and used for reporting purposes, like knowing how many Microsoft Word licenses the company has versus the number of copies installed in the computers.

Here are some examples of running WMI queries from the xp_cmdshell SQL function using the wmic.exe WMI Windows process.

?
1
2
3
WMI queries on the machines system for the NIC

exec xp_cmdshell 'wmic /namespace:\\root\cimv2 path Win32_NetworkAdapterConfiguration get Caption, DNSDomain, DNSHostName'
Caption

DNSDomain
DNSHostName
[00000001]
VMware Accelerated AMD PCNet Adapter

PCSYS32
[00000002]
RAS Async Adapter


[00000003]
WAN Miniport (L2TP)


[00000004]
WAN Miniport (PPTP)


[00000005]
WAN Miniport (PPPOE)


[00000006]
Direct Parallel


[00000007]
WAN Miniport (IP)


[00000008]
Teefer2 Miniport


[00000009]
Teefer2 Miniport

NULL

(12 row(s)
affected)


Here is the output of the previous WMI query.
Capture information of disk drives

?
1
exec xp_cmdshell 'wmic /namespace:\\root\cimv2 path Win32_LogicalDisk get FileSystem, FreeSpace, Size, VolumeSerialNumber, VolumeName, caption, description'
Caption
Description
FileSystem
FreeSpace
Size
VolumeName
VolumeSerialNumber
A:
3 1/2 Inch Floppy Drive

C:
Local Fixed Disk
NTFS
8022052864
42935926784

50E721D5653
D:
CD-ROM Disc


E:
Local Fixed Disk
NTFS
6049144832
42943377408
Data
3ZSD#ADC493
NULL

(7 row(s)
affected)

This WMI query will gather information on the physical drives on a machine where it is executed.
In Conclusion

xp_cmdshell is a very powerful tool in Microsoft BI - SQL Server Tooling.