1

I want to know how can i keep XP_cmdshell enabled forever. Its a local system, and I am using xp_cmdshell to generate a txt file with query results.

I cant enable xp_cmdshell in the code itself because it gave me an error saying I cant activate it then transactions are running.

I searched a lot for this but unfortunately no answer.

ps: I know how to enable xp_cmdshell, I want to keep it on forever only.

mohabbati
  • 1,162
  • 1
  • 13
  • 31
Alexandre Calvario
  • 143
  • 1
  • 1
  • 11
  • Using `xp_cmdshell` is not safe and it could be a **security issue**. When you want to ***access*** to ***resources out of DBMS*** for example, `xp_cmdshell`, DBMS does not have any control over any issues occurred. So I strongly suggest you change the solution and create another service or app to do your task, instead, it is **the only solution** to do your task. – mohabbati Dec 28 '18 at 12:40
  • @MohammadMohabbati This is not true. It ***can*** be unsafe and insecure if security is not managed correctly. However if sysadmin rights are properly managed it is usually fine. That said, there are usually better ways to accomplish the same thing. In particular Sql Agent CmdExec is usually easier to manage correctly. – RBarryYoung Dec 28 '18 at 16:30

1 Answers1

1

Probably not. You might be able to catch and dismiss the command with a server-level DML trigger, but I really wouldn’t recommend that.

There are really only two good options for you, IMHO. First, enable and configure audit logging to capture who/what is resetting it and then deal with that. Since this is probably some company/corporate security procedures, that’s probably the preferred way to go.

XP_CmdShell is very convenient but it is hard to secure properly. Essentially you have to restrict it to SysAdmin use only, and then aggressively lock down SysAdmin rights. This is hard because the SQL Server world is rife with sloppy applications (internal and third-Party) that either require or assume that they have SysAdmin.

The other possibility is to use some other, more easily secured, method of doing the same thing. Both SSIS and SqlAgent have such facilities. I’ve used SqlAgent’s CmdExec successfully in the past, it’s a bit harder to configure, but much easier to secure and I’d recommend trying to use that.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137