Enable Shell Command in your Microsoft SQL Database Server

Paopatsin/ November 29, 2020/ Database, MS SQL, Technology

Please read our Disclaimer

Works With
-Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64)
-Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64)

As a Database Administrator, there are occasions that you would be needing to interact with the operating system from your SQL Management Studio such as running a dir check or you need to do a file import using the bcp utility command and possibly a lot more.

Luckily, SQL provides a utility command called xp_cmdshell, however, this command does not come ENABLED right off the bat, as the Database Administrator you must first enable it before using it.

Command:

EXEC sp_configure ‘show advanced options’, 1 GO RECONFIGURE GO EXEC sp_configure ‘xp_cmdshell’, 1 GO RECONFIGURE GO

Steps and Sample Usage:

1. Open and log into your SQL Management Studio
2. Open a New Query Windows or (CTRL+N)
3. Run the following commands: (Copy and Paste the code block to your Query Window)

–Show the Advanced Options
EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
–Enable xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE
GO

4. Execute the query by pressing F5

If successful, you would be able to run xp_cmdshell already.

Caveat:

1. Enabling xp_cmdshell does not come without a risk, as a Database Administrator, you must be aware that it allows you to run Operating System specific commands under the level of your SQL authority. So it would be wise to disable after using but on the event that the enabling is permanent then you should only allowed authenticated users to utilize the utility command.
2. Enabling xp_cmdshell is on a per Microsoft SQL Database instance.
3. You must refrain from running OS specific commands under the SQL user context, meaning you must always try to separate your concerns and use proper tools to do the job.