CODEWITHSUNDEEP
sqlxp-cmdshell
Alexandre Calvario
Alexandre Calvario

Reputation: 153

XP_CMDSHELL keeps getting disabled from time to time with no reason

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.

Upvotes: 1

Views: 987

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56755

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.

Upvotes: 1

Related Questions