Is xp_cmdshell the only option?

Question

Is xp_cmdshell the only option?

Viewing 4 posts - 16 through 18 (of 18 total)

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

Randy Doub SSCertifiable Points: 5322 More actions · Answer 1

I think using an Agent Job is a great idea since the account that runs my Agent already has xp_cmdshell privileges.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Randy Doub (11/4/2010)
I have a vendor system with a SQL2008 back end. We put their database on a large server that houses several other databases because of the large disk array. Now the vendor wants one of their SPs to create a .txt on a fax server to initiate a fax. A share was created on the fax server and a domain account created with rights to the share. The domain account was made an xp_cmdshell proxy, and the vendor's SQL Server login was granted exec to xp_cmdshell. It works as expected and since the proxy account ONLY has access to the fax share on our domain, they can't use xp_cmdshell to write files anywhere else. That's how it appears anyway. But is this the only way? With all I read about xp_cmdshell being a risk, what other option is there? I have a working spOACreate script that will create a file, but SQL2008 BOL says you must be a sysadmin to run sp_OA procedures. So what can one do within an SP, other than xp_cmdshell, to create a .txt without being a sysadmin?
Also, I opened query analyzer with the vendor's sql server account and tried to create files on various servers and shares on our domain and got Access Denied as I expected. Anything else I should be checking to verify this account is truely restricted?
Thanks all.

Create a special user to run jobs with. Create a job with you xp_cmdshell stuff in it. Allow individuals to run the job. The special user would be the only thing where the CMD proxy works and that's used only for running jobs. xp_cmdshell, by itself, isn't a risk that way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Jeff Moden (11/4/2010)
Randy Doub (11/4/2010)
I think using an Agent Job is a great idea since the account that runs my Agent already has xp_cmdshell privileges.[/quote-0]

Correct. Create a job with you xp_cmdshell stuff in it. Allow individuals to run the job. The special user would be the only thing where the CMD proxy works and that's used only for running jobs. xp_cmdshell, by itself, isn't a risk that way.[/quote]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Randy Doub SSCertifiable Points: 5322 More actions · Answer 4

If anyone cares, here's my CLR solution.

http://www.sqlservercentral.com/Forums/FindPost1022487.aspx