Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

BCP Utility to output contents into CSV Expand / Collapse
Author
Message
Posted Tuesday, May 21, 2013 9:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:21 PM
Points: 135, Visits: 101
Hi Guys,

Thanks for all your suggestions.

I have spotted the problem - it is nothing to do with the permission, bcp, cmdshell, sybase (or anything as thought about ......)

What it turns out to be is @sql contains LF \ CR and xp_cmdshell doesn't want to troop along with this.....

However surprisingly when passed the same query directly into xp_cmdshell it worked fine.

So for the purpose of the topic is not resolved. But I will carry on in this voyage of discovery as to why it is so fussy about the specials chars in @sql unless you guys already know it!
Post #1455285
Posted Wednesday, May 22, 2013 10:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
Thanks for the update. I'm not sure I would have thought of that, but a great catch.

Did you use some type of hex editor to find this?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1455595
Posted Wednesday, May 22, 2013 2:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:21 PM
Points: 135, Visits: 101
Cheers! I just wanted to format the ugly looking SQL in @sql while someone was watching over my shoulder and then I had a light bulb moment - that it could be a possibility that BCP / cmdshell is having trouble having those LFs...... and there was the result.
Post #1455711
Posted Thursday, May 23, 2013 9:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 388, Visits: 1,531
Wouldn't you have to do something like:

EXECUTE @RESULT = 'MASTER..xp_cmdshell '+ @sql +'_1, NO_OUTPUT'

or

SET @RESULT = EXECUTE MASTER..xp_cmdshell [sql]_1, NO_OUTPUT
set @result = replace(@result, '[sql]', '@sql')
exec @result

Post #1456044
Posted Thursday, May 23, 2013 11:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:56 AM
Points: 243, Visits: 799
This is what has worked for me. This code is embedded in a S/P and runs on a schedule and gives me a CSV like clockwork every night. Needs no tweaking of permissions or access, other than turning xp_cmdshell ON and OFF. In addition, bcp is itself used to fire a S/P, not just a static query. Note that the stored procedure name can also be parameterized and works fine. I just leave in plain text for maintainability.

--turn xp_cmdshell ON as I leave them off by default:
EXECUTE master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

SET @SQL = 'bcp "exec [WorkflowV3].[dbo].aStatusExtractDataCSV" queryout "' + @destFolder + @fileName + '" -t \, -T -c'

EXECUTE master.dbo.xp_cmdshell @SQL

--and then turn xp_cmdshell back OFF for security's sake
EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXECUTE master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE

Good luck!


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1456127
Posted Thursday, May 23, 2013 6:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1456264
Posted Friday, May 24, 2013 6:54 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:56 AM
Points: 243, Visits: 799
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
Post #1456466
Posted Friday, May 24, 2013 7:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:56 AM
Points: 243, Visits: 799
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
Post #1456469
Posted Saturday, June 1, 2013 7:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
@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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1458950
Posted Monday, June 3, 2013 7:04 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:56 AM
Points: 243, Visits: 799
@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
Post #1459249
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse