If you could use xp_CmdShell securely, would you?

Question

If you could use xp_CmdShell securely, would you?

Viewing 4 posts - 91 through 93 (of 93 total)

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

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

TomThomson (8/4/2015)
Jeff Moden (8/3/2015)
Heh... ironically, I absolutely agree with the idea of writing SQLCLR for file handling, etc. Equally ironic is the amount of FUD concerning such CLR endeavors and I've actually found (read that as "feels like") more people would accept the use of xp_CmdShell than those who would accept the use of SQLCLR. Considering that I had some pretty idiotic SQLCLR code submitted to me for review in the past, I can't say I blame people much. The most idiotic CLR I ever had to turn down was one that calculated MODULUS because the "developer" didn't know (or even try to find out) that SQL Server has a modulus operator built in.
Rather strangely (because I think it would be better to extend SQL to do some of the things I currently would have to use SQLCLR for) I think SQLCLR is an extremely good thing; but, in the hands of brain-dead idiots (or which there are far too many screwing up databases, whether in CLR or in SQL) it rather obviously could be a bit of a pain, just as hopelessly bad as incompetently written XPs used to be.

Thanks for the feedback, Tom. I remember when 2005 first came out. There were some pretty useful things that I could see it being used for including file handlers, splitters that worked at machine language speeds, auto-magic complex spreadsheet readers, etc, etc. You know... do all the things that one might wish that you could do with T-SQL. Unfortunately, everyone started using SQLCLR to do everything from backups to what have you, kinda like the new-shiny-object syndrome with PoSh and I soured on it a bit because of that. When you have that many people trying to get punches on their "man card" for using the latest cool thing no matter how idiotic, there's bound to be many brain-dead idiots involved.

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Jeff Moden (8/6/2015)
stevenb 14609 (8/6/2015)
I use xp_CmdShell for 2 different things.
One is to export csv reports for end users.
The other one is to export and then FTP files to a third party vendor that uses the data for sending invoices for us.
Heh. I went for years without finding anyone else other than myself that would do such things. I thought it was because I was in the telephony business. In the last year or so, I've found several people that are using it to do FTP and SFTP sessions in both directions. So, thanks for the feedback. It's nice to not actually be the only one doing this type of stuff. It's part of the reason why I held of on an xp_CmdShell article and that obstacle is rapidly disappearing thanks to these types of comments.

Piling on, using WMIC, SMARTCTRL, TASKLIST, NETSTAT, NET etc. are just few of the things one normally does with xp_cmdshell. For instance on a physical box, I normally gather the hard drives S.M.A.R.T. stats and tie those in with whatever monitoring system being used. WMIC in combination with TASKLIST and NET allows me to detect RDP sessions etc.

😎

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

Spot on a similar here. I use it for calls to WMIC to pick up the disk stats and warnings for the entire enterprise and put it all in a morning report. It even has a "media locator" that I've built into it to help the folks in NetOps find stray DVDs and thumb drives. It works really well and auditors love it.

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

JohnG69 Hall of Fame Points: 3114 More actions · Answer 4

Hi Jeff,

I'm sorry to bother you but I'm still trying to get this running properly and had to turn to you for help since you seem to be the best guy out there for this type of setup ;-):

This is what I've done until now:

1. created a standard windows user (SQLServiceUser)

2. With SQL Server configuration Manager, I changed the SQL server Service Account to use this new user.

3. I created a windows group "OperatorGroup" that has limited access to some directories and put them in MSSQL as DB Owners of a specific DB, let's call it MYDB

4. I created a new standard user called CmdShellProxyUser

5. I ran SSMS as Administrator and logged in using an Admin Account

6. I ran

use Master

create login [myMachine\CmdShellProxyUser] for Windows with default_database=master

create user [myMachine\CmdShellProxyUser] for login [myMachine\CmdShellProxyUser] with default_schema= dbo

Exec sp_xp_cmdshell_proxy_account 'myMachine\CmdShellProxyUser','MyPassword'

Grant execute on Xp_cmdshell to [myMachine\CmdShellProxyUser]

7. I made the owner of MYDB: sa

8. in the sp that uses xp_cmdshell, I added the 4 magic words "with Execute as Owner"

9. I then created a standard windows user operator1 which I added in the OperatorGroup

10. I logged in to the machine as operator1 and opened SSMS (It logged me in no problem)

11. I gave the operatorGroup execute permission: Grant Execute to [mymachine\OperatorGroup]

Now when I run the SP, with operator1, I get the following error message:

The Server Principal "MyMachine\CmdShellProxyUser" is not able to access the database "myDB" under the current security practice.

It seems obvious that I should give cmdShellProxyUser permissions to MYDB, but is this normal practice or am I missing something?

And If I should give access to cmdshellProxyUser: DB_Owner or DBwriter? If it depends on what the SP does, it creates tables, does selects and drops tables... so I'm leaning towards DBOwner.

Also in what case would I need to give the operatorGroup access to master or msdb? I was under the impression that no one should have access to the system DBs.

Your Help is greatly appreciated.

thank you

JG