BCP Utility to output contents into CSV

Question

BCP Utility to output contents into CSV

Viewing 8 posts - 16 through 22 (of 22 total)

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

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

Sigerson (5/23/2013)
Needs no tweaking of permissions or access, other than turning xp_cmdshell ON and OFF.

I'm just curious... why do you turn xp_CmdShell OFF? Since anyone (including an attacker) with "SA" privs can turn it on, there's no non-trivial benefit to turning it off. Since only "SA"s can use it to begin with (unless you've made the horrible mistake of granting someone a proxy), there's also no non-trivial benefit to turning it off.

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

Sigerson Hall of Fame Points: 3697 More actions · Answer 2

I turn it off just because I've read that since it's OFF by default at installation, it's best to leave it that way. Also (I read) having it on is a security risk. So keeping it off just seems like basic housekeeping (?) No one ever logs in as 'sa'.

I'm a sole developer not a DBA. But I'd like to understand this idea of proxies more b/c of another problem I'm having with bcp. You posted to my thread a few days ago, right after Lowell did. Link: http://www.sqlservercentral.com/Forums/FindPost1454773.aspx

We have a client-server system, front-end written in javascript. All the corporate users have user records in SQL Server under their own names, which I believe the app inserts automatically. But the app itself connects to the d/b and does all its work under it's own login, let's call it 'GroverSQL'. So when the CSV file is created by my s/p, the Windows owner shows up as "[myDomain]\GroverSQL." Is this what you mean by proxy?

My problem in the other thread is that while I can get my bcp s/p to fire from SQL Agent, when I try and run the same s/p from withing a javascript ASP call from the front end, it fails with a permissions error. All members of Domain Users have inherited Write permissions to the CSV's destination folder. So I'm getting a permissions problem having a user unable to run a s/p from the front end, but GroverSQL has no trouble executing the s/p at all.

I'm hoping the concept of a proxy might be useful here. Or am I way off track?

Sigerson

"No pressure, no diamonds." - Thomas Carlyle

Sigerson Hall of Fame Points: 3697 More actions · Answer 3

Forgot my other question regarding the 'horrible mistake' of granting a proxy. I don't know that I did that and wouldn't want to, but I just don't know. I log into the SQL Server instance with a user with Domain Admin privileges. Is that bad?

Sigerson

"No pressure, no diamonds." - Thomas Carlyle

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

@Sigerson,

My apologies for the long overdue response. I lost track of this thread.

The type of "proxy" that I was talking about isn't like the fact that multiple people login to an application and the application talks to the database with its own login (although I do have an appreciation for Developers that actually pass user information through the app especially when the app writes to the database). The type of proxy I'm talking about is where an individual user is granted privs to run xp_CmdShell directly even though that user does not have "SA" privs.

The reason why such a proxy is a terrible mistake is because the user isn't using xp_CmdShell within the confines of their own login privs. Rather, xp_CmdShell will operate as the login for the SQL Service itself, which frequently has extraordinary privs across the domain (another mistake... the SQL Server Service login should also be limited). That means that the unprivileged user can use xp_CmdShell to (for example) see files and other databases that they're not supposed to. It also means that they can modify or even delete data/files and the only trace that may (provided there is any auditing) be left behind is that the SQL Server Service login did it.

My take on the use of xp_CmdShell is that only those people with "SA" privs should be allowed to use it directly and only DBAs should have "SA" privs and then only the most trusted ones. NO application should ever have "SA" or even "DBO" privs. Everyone else (including apps) should only be allowed to do what a stored procedure will allow them to do (which can include xp_CmdShell functionality if done properly). I also believe that well protected processes that do things like calling BCP using CmdExec tasks in jobs or using xp_CmdShell in properly protected stored procedures is just fine.

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

Sigerson Hall of Fame Points: 3697 More actions · Answer 5

@jeff:

Thanks for the information, it answered a lot of my questions. I'm an accidental DBA at best. I never grant individual users any special privs so I don't think I'm in any jeopardy here. No users can execute xp_cmdshell except through an application s/p, and I always disable it in the s/p code as soon as I can.

Anyway, thanks again for the confirmation.

Sigerson

"No pressure, no diamonds." - Thomas Carlyle

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Sigerson (6/3/2013)
@Jeff:
Thanks for the information, it answered a lot of my questions. I'm an accidental DBA at best. I never grant individual users any special privs so I don't think I'm in any jeopardy here. No users can execute xp_cmdshell except through an application s/p, and I always disable it in the s/p code as soon as I can.
Anyway, thanks again for the confirmation.

Disabling xp_CmdShell when you're done using it is fine but it really does nothing for security. Only the people (have "SA" privs) that can actually use it can turn it on.

I am concerned a bit about what one of the application SPs might look like for running xp_CmdShell because of the "public facing" nature of such SPs and the fact that there is such a thing as "DOS Injection". I'm also concerned with how the privs are setup since the application SPs turn it on and off. I'd be happy to check for you if you'd like to post them. If they're "sensitive", you could PM me instead.

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

Sigerson Hall of Fame Points: 3697 More actions · Answer 7

@jeff:

Sure, what do you need to see? I told you I'm very new to the whole topic of users/privs/roles/security in general.

Also, how do I send a pm?

Sigerson

"No pressure, no diamonds." - Thomas Carlyle

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

Apologies for the delayed reply...

I guess I'd need to see what one of the SP's that takes user input to be used with xp_cmdShell looks like. I'd also need to know (VERY important) what the privs of the application login are and whether or not you've setup an xp_CmdShell proxy.

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