Is it safe to set xp_cmdshell value 1?

Question

I need to use xp_cmdshell in one of SQL job.So is it safe to reconfigure xp_cmdshell to 1 or can i set value 1 in the starting of SQL statements and set it back to 0 in the end of SQL Statement ?

Answer 1

No, it is not very safe if you get untrusted input into the database (i.e. if it is on a web server or such). You basically hand the user a really dangerous tool if you have any SQL injection vulnerability. I would not run that risk on public-facing servers.

Internal servers may be another thing, but internal attacks are often overlooked, so probably "we" should protect against internal attack also.

Also, if setup correctly (see https://msdn.microsoft.com/en-us//library/ms175046.aspx ) you need a proxy account and all kinds of SQL Server pereparations to use it.

I would always suggest using SSIS or SQL Server Agent to perform OS level tasks.

Answer 2

Without discounting anything stated in til_b's & unclefredo's answers.

I would like to share some facts about xp_cmdshell you should know the before enabling it.

On SQL Server 2005 or newer, including SQL Server 2016:

1. xp_cmdshell is disabled by default on install.

2. Only users with sysadmin (sa) permissions can use it.

3. Only users with sysadmin permissions can enable it for use, or disable it.

4. Users with sysadmin permissions can do so much other damage (if they wanted to) like DROP DATABASE, even if xp_cmdshell is not enabled.

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.

NB: 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.

How you turn xp_cmdshell on.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

How you turn xp_cmdshell off.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;

For Example: how you use xp_cmdshell:

EXEC xp_cmdshell 'dir c:\';
RECONFIGURE;

There are some pros and cons to using xp_cmdshell:

Pros

1. If xp_cmdshell is being called by a member of the sysadmin role, it is going to execute in the context of the SQL Server service account and with the same privileges. So if DBAs don't normally have administrative rights on a server, but SQL Server is running under a service account that does, then through the use of xp_cmdshell they have effectively escalated their rights to be at that level. By the way, this also means any processes which run under an account that is a member of the sysadmin role, such as a SQL Server Agent job owned by sa, runs at this level, too.

2. On the surface being able to run this extended stored procedure does not seem like much, but if the MSSQLSERVER service account has local administration rights then you can use this extended stored procedure to perform any Windows operating system command. Therefore, under these circumstances xp_cmdshell can create quite a security risk.

Cons

1. When you use the extended stored procedure xp_cmdshell it runs commands in the background. Because of this, it MUST NOT be used to run programs that require user input. If you try to execute a program that requires user input, the xp_cmdshell process will hang. The process hangs because the program is waiting for user input, but xp_cmdshell will never display the user interface to allow the user to enter data.

2. At the very least, an attacker could use xp_cmdshell to shut down key services and processes on the server (especially if coupled with something like pskill), rendering the operating system in an unstable state where it forces itself to reboot.

For Your Further Ref: - Here And Here

Answer 3

Agree with til_b on this. However, if you have to do make sure you minimize the attack area by only having it switched on when you need it and as highlighted, you'll need to set up a proxy.

I would say this though - if you're having to use xp_cmdshell, it may be that whatever you're trying to do isn't best placed to be in the database tier.