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 ?
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.
Without discounting anything stated in
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.
xp_cmdshell is disabled by default on install.
Only those users with sysadmin (sa) permissions can use it.
Only those users with sysadmin (sa) permissions can enable it for use, or disable it.
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'.
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.
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.
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.