SQL Security - Disable SQL "xp_cmdshell"

Windows server security has been a hot topic for many years now.  Password complexity, periodic password changes, restrictive NTFS rights are all great steps toward a secure Windows installation.   Unfortunately many system administrators leave a big hole in their SQL sever installations by letting the SQL services run under the local system account.   This account has local admin privileges and therefore any process executed by the SQL server or SQL agent are running with those privileges.

Enter the “xp_cmdshell” extended stored procedure.   This procedure will allow you to execute commands as if you were typing them in a command window under the windows account that SQL sever is configured to run.   If that account is the local system account, the process has full access to the user database, file system and registry.  

In a situation where a username and password is compromised in SQL (this can be common on accounts with password that don’t change) a hacker can basic tools to further compromise your system.   A common hacking attempt would be to execute the extended stored procedure to first create an account, then another command to give them local administrative rights to the local server.  It can do this if SQL is running as a local administrator equivalent.  Now they have complete access to the system with a “valid” account.  They can now install new software, turn on RDP, fetch backup files, replace files…  it is pretty much endless path of destruction.  In addition, by being a local administrator, they also now have DBO access to all the databases in your SQL installation making things worse.

Now that I have  spread a lot of doom and gloom about a single SQL command, let me tell you the best way to prevent it.    If you aren’t using it…   Disable it.    Log into Query Analyzer (SQL 2000) or Management Studio (SQL 2005) as SA or SA equivalent and run the following command:

– Allow advanced options to be changed.

EXEC sp_configure ’show advanced options’, 1

GO

– Update the currently configured value for advanced options.

RECONFIGURE

GO

– Disable the feature.

EXEC sp_configure ‘xp_cmdshell’, 0

GO

– Update the currently configured value for this feature.

RECONFIGURE

GO

I am sure that some readers will say that this is a handy command.   I agree it is, but use it wisely and grant permission to it sparingly or change the windows account that the SQL services are running.    Look for more of this in the future.