xp_cmdshell question

Question

Post reply

xp_cmdshell question

blondie

Old Hand

Points: 386
More actions
July 12, 2010 at 3:11 pm

#235099

hi, I have a client that needs to run log parser on my sql box...he wants execute privs to xp_cmdshell for his sql login account. Is there a better way to do this?
thx....

Viewing 11 posts - 1 through 10 (of 10 total)

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

JohnnyDBA SSCertifiable Points: 6355 More actions · Answer 1

Is he parsing the error log?

If so can't he get everything from a xp_readerrorlog command?

blondie Old Hand Points: 386 More actions · Answer 2

actually, he wants to exectue log parser 2.2 (separate app)...here is a desc:

He's taking the info and importing it into his app...thus..he want to leave the code for the app as is.

Log Parser 2.2

--------------

Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®.

http://www.microsoft.com/downloads/details.aspx?familyid=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 3

He's trying to scrape logs from other servers most likely. I did that with this app using my SA privs at my last job to make a lightweight error tracker for a series of non-SQL servers. The program can get all of the event logs for any NT server if it has enough privs. But it does require either xp_cmdshell or a SQL Agent job that runs an OS command because it's an external program. See if running it as an agent job is an option, since that way you don't have to grant the user any rights to xp_cmdshell which as you know is a nasty idea to have to do...

JohnnyDBA SSCertifiable Points: 6355 More actions · Answer 4

If his source code is setup to use xp_cmdshell and he's not willing to change the code then it is probably the only way you will be able to grant him access.

The only other thing I can suggest is that I've read that xp_readerrorlog is actually a generic file reader. You can pass in any file and it will display the content. He may be able to convert his code to use that procedures so xp_cmdshell is no longer needed but the extra parameters for xp_readerrorlog are undocumented which means it may break in the future.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 5

Johnny -- does that XP support getting event logs from live servers? That's what he probably is doing. Log Parser lets you run a query against, say, an AD event log so you can see failed logins to AD and store them in a SQL database.

JohnnyDBA SSCertifiable Points: 6355 More actions · Answer 6

I believe it's just flat files.

Here's a link to the documentation on it:

http://vyaskn.tripod.com/sp_readerrorlog_undocumented.htm

blondie Old Hand Points: 386 More actions · Answer 7

blondie

Old Hand

Points: 386

July 13, 2010 at 7:22 am

#1192016

Thanks for all the replies...very helpful.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

I probably wouldn't give a vendor such access. I'd ask them to tell me what they wanted and how to get it. Then, I'd email the results.

--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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

I would not permit a client or a vendor to have such permissions on a production server of mine.

I would find an alternative method such as described by Jeff or maybe enable an automated solution to upload only the necessary information to an ftp server or such.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

EdVassie SSC Guru Points: 60476 More actions · Answer 10

I agree with Jeff. Allowing a customer or vendor to use xp_cmdshell on your system is opening a security hole you can never completely close. If what they do causes your organisation to breach legislation then your organisation pays the penalty and gets the reputation damage.

If you have an external customer or vendor who wants data from your system, then get them to say what they want and how to get it. Then create your own process to do this work and put the results in a place (such as a FTP location) where the customer can access it.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara