3

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 ?

2

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.

| improve this answer | |
2

Without discounting anything stated in @til_b & @unclefredo answer.

I would like to say something facts about 'xp_cmdshell'. Before enable the 'xp_cmdshell' you must know the some facts about xp_cmdshell.

The facts with xp_cmdshell on SQL Server 2005 or newer, including SQL Server 2016.

  1. xp_cmdshell is disabled by default on install.

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

  3. Only those users with sysadmin (sa) permissions can enable it for use, or disable it.

  4. Those users with sysadmin (sa) 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;

Definitely, There is some Pros and cons to use the '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.
So what ever the service account SQL Server is running under, a member
of the sysadmin role can execute as through the use of xp_cmdshell. 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 this circumstance the xp_cmdshell can create quite a security
hole.

Cons

1. When you use the extended stored procedure xp_cmdshell it runs
    commands in the background. Because of this, xp_cmdshell "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

| improve this answer | |
  • 2.Only those users with sysadmin (sa) permissions can use it. sysadmin can use it without doing enable ?? – Rehan Dec 12 '16 at 13:08
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.