Alternative to using xp_cmdshell

Question

Post reply

Alternative to using xp_cmdshell

pawan_kirtikar

SSC Veteran

Points: 268
More actions
December 4, 2009 at 5:19 am

#214547

Hi,
Due to security reasons we cannot use the xp_cmdshell command to execute the system level calls through the stored procedures. We want to execute batch files at a specified time using the SQL Server 2005 stored procedures.
Is there any alternative to xp_cmdshell that we can use to execute the system level calls.
Any inputs will be appreciated.
Thanks & Regards

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

C# Gnu SSCertifiable Points: 5973 More actions · Answer 1

Hi

I am not sure exactly what 'system' operations you wish to perform?

If you mean things like file copy operations, then yes CLR is great for that.

You 'can' use sp_OACreate to copy files, however (in my opinion) this option opens up another security risk.

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Lowell SSC Guru Points: 323518 More actions · Answer 2

xp_cmdshell is the method that opens the door to operating system commands ; once granted you have access to do just about everything.

what you should do is create a CLR for each specific thing you want to allow done from SQL Server. so for example, if you were using xp_cmdshell to list the contents of a directory, or to stop and start specific services, or whatever you were doing previously, you'd make a CLR to do that (plenty of examples out there)

do you have any specific goals you want to do, or was this more of a general question?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pawan_kirtikar SSC Veteran Points: 268 More actions · Answer 3

Thanks for the reply Lowell.

I am specifically looking for calling batch jobs from SQL Server that would be interacting with say external systems.

So for example I will create a bat file do some settings and call the external systems.

C# Gnu SSCertifiable Points: 5973 More actions · Answer 4

Mind you, if you are using SQL Agent to run the stored procedure containing xp_cmdshell, then you may not have an issue as SQL Agent is likely setup to use an accout that sysadmin privelages?

In this way you do not need to grant xp_cmdshell usage to any normal user accounts...

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

C# Gnu SSCertifiable Points: 5973 More actions · Answer 5

What I mean to explain is that by enabling xp_cmdshell:

EXEC sp_configure 'xp_cmdshell', 1

This will only enable xp_cmdshell for sysadmin users.

If your SQLAgent is a sysadmin user, then I do not think you have a security issue..

[Edit: as you mentioned

at a specified time

I am assuming you are creating an SQL Job : hence my references to SQL Agent]

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

I don't know how or if you could do it from a stored procedure, but you might want to look into PowerShell.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

C# Gnu SSCertifiable Points: 5973 More actions · Answer 7

I think the key here is that you wish to schedule a task - and of course a task can have CmdExec step - that could exec a batch job - or use xp_cmdhell in a stored procedure as security should not be an issue if SQLAgent is running under sysadmin account (usally case).

Hope that helps you

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

GSquared SSC Guru Points: 260824 More actions · Answer 8

CLR would be my prefered method for that kind of thing.

If not, then have the script enable xp_cmdshell, run the command, then disable xp_cmdshell. The odds of an attack during that small window are negligible.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

Hold on..

Let me see if I have this right.

1. Can't use xp_cmdshell

2. Want to start a batch file at a specified time

OR

2. Start it from a stored procedure

Ok, SQL Agent can do it with those limitations. SQL Agent doesn't require xp_cmdshell to be active to use CmdExec steps and you can start the job from a sproc by calling sp_start_job in msdb..

As far as using SQLCLR, I have done something similar using the process libraries, SQL Agent would work for what I needed so I built a process that effectively does what xp_cmdshell does but in a more controlled manner.

Does this satisfy your requirement?

CEWII

C# Gnu SSCertifiable Points: 5973 More actions · Answer 10

Be aware sp_start_job permissions within a stored procedure:

A user who can execute sp_start_job and is a member of the sysadmin fixed role

can start any job.

A user who is not a member of the sysadmin role can use

sp_start_job to start only the jobs he/she owns.

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 11

You can build something with service broker to get around that..

CEWII

C# Gnu SSCertifiable Points: 5973 More actions · Answer 12

One way to give an application control of running jobs is to have a BatchWorkToDo table:

For example lets say you have a table like this :

(columns)

JobName_______________IsWorkToDo

(data)

BACS Transfers____________Yes

Send CSV files_____________No

Your application would update the IsWorkToDo column to Yes when the batch job needs to run.

Your scheduled Job say "BACS Transfers" (running every 30 secs perhaps), would firstly look at the BatchWorkToDo table to see if your application has created some work to do, and the job needs to run. The job should immediately set the flag to No, then run whatever tasks are required.

In this way you avoid granting unecesary permissions to your users or to your application, and the batch jobs will run on command of your application.

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

C# Gnu SSCertifiable Points: 5973 More actions · Answer 13

Elliott W (12/4/2009)
You can build something with service broker to get around that..
CEWII

I havn't used that yet, it looks very similar to Microsoft Message Que that I used to use with VB6 (a few years ago mind)

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 14

I started using it by going through the BOL tutorials. I built a queue that could realistically take jobs from any user and the receiver (which is in the same database) runs as a more priviliged user. It checks the user against a table to see if it is allowed to start the job and if so it starts it..

CEWII

Alternative to using xp_cmdshell

Cookies on SQLServerCentral