bcp a pipe delimited file

Question

Post reply

bcp a pipe delimited file

sanjuv999

SSC Enthusiast

Points: 114
More actions
September 25, 2012 at 10:05 am

#282051

hi all,
i want to import a flat file of type
Employee ID|Employee Name;
134543543|asdfasdfdsfdf;
inside a sql proc
As bcp requires the destination table to be already present, i created a table having a single column 'id' as primary key and then executed
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
Next i have used
exec master.dbo.xp_cmdshell 'bcp foo.dbo.employee in c:\test.txt -T -S .\sqlexpress';
but in the output of sql management studio i get
Enter the file storage type of field id [bigint]:
please guide me

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

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

sanjuv999 (9/25/2012)
please guide me

Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.

If you want to do everyting with T-SQL use for this task BULK INSERT.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sanjuv999 SSC Enthusiast Points: 114 More actions · Answer 2

thanks for the reply.

This operation of bulk copy will be taking place once per day in my usecase . i.e. importing 70000 records so that is why i had thought of using bcp in the stored proc. But as you have said that it is better to do bulk insert, i will go forward with the same.

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

opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me
Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.
If you want to do everyting with T-SQL use for this task BULK INSERT.

It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.

I do agree, however, that it's absolutely not required for this scenario.

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me
Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.
If you want to do everyting with T-SQL use for this task BULK INSERT.
It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.

Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

opc.three (9/26/2012)
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me
Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.
If you want to do everyting with T-SQL use for this task BULK INSERT.
It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.
Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.

If you have control over who has "SA" privs, it simply does not. If you don't have that kind of control over your database (and you absolutely should), disabling it is nothing more than a warm fuzzy that won't actually work because anyone with "SA" privs can simply turn it on.

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

Jeff Moden (9/27/2012)
opc.three (9/26/2012)
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me
Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.
If you want to do everyting with T-SQL use for this task BULK INSERT.
It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.
Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.
If you have control over who has "SA" privs, it simply does not. If you don't have that kind of control over your database (and you absolutely should), disabling it is nothing more than a warm fuzzy that won't actually work because anyone with "SA" privs can simply turn it on.

Risk mitigation is all about the "what if" Jeff. I won't argue this point with you anymore. The fact is that enabling it introduces risk.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

opc.three (9/27/2012)
Jeff Moden (9/27/2012)
opc.three (9/26/2012)
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me
Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.
If you want to do everyting with T-SQL use for this task BULK INSERT.
It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.
Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.
If you have control over who has "SA" privs, it simply does not. If you don't have that kind of control over your database (and you absolutely should), disabling it is nothing more than a warm fuzzy that won't actually work because anyone with "SA" privs can simply turn it on.
Risk mitigation is all about the "what if" Jeff. I won't argue this point with you anymore. The fact is that enabling it introduces risk.

You HAVE to continue to talk about it because you keep talking about it in a negative fashion! 😉

What risk does having xp_CmdShell being turned on bring to a system? None because it can only be used by "SA"s (unless you were foolish enough to proxy it to individuals) and ANY SA can turn it on even if it's off. There is absolutely no risk to mitigate here.

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

Jeff Moden (9/27/2012)
opc.three (9/27/2012)
Jeff Moden (9/27/2012)
opc.three (9/26/2012)
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me
Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.
If you want to do everyting with T-SQL use for this task BULK INSERT.
It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.
Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.
If you have control over who has "SA" privs, it simply does not. If you don't have that kind of control over your database (and you absolutely should), disabling it is nothing more than a warm fuzzy that won't actually work because anyone with "SA" privs can simply turn it on.
Risk mitigation is all about the "what if" Jeff. I won't argue this point with you anymore. The fact is that enabling it introduces risk.
You HAVE to continue to talk about it because you keep talking about it in a negative fashion! 😉

And I will continue to steer people towards more-secure and more-auditable (and more robust I might add) solutions as long as I have breath 🙂

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

opc.three (9/27/2012)
Jeff Moden (9/27/2012)
opc.three (9/27/2012)
Jeff Moden (9/27/2012)
opc.three (9/26/2012)
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me
Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.
If you want to do everyting with T-SQL use for this task BULK INSERT.
It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.
Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.
If you have control over who has "SA" privs, it simply does not. If you don't have that kind of control over your database (and you absolutely should), disabling it is nothing more than a warm fuzzy that won't actually work because anyone with "SA" privs can simply turn it on.
Risk mitigation is all about the "what if" Jeff. I won't argue this point with you anymore. The fact is that enabling it introduces risk.
You HAVE to continue to talk about it because you keep talking about it in a negative fashion! 😉
And I will continue to steer people towards more-secure and more-auditable (and more robust I might add) solutions as long as I have breath 🙂

All you have to do now, my old friend, is find something more secure to steer them to. 😀 For starters, how about how to control who gets SA privs and how to determine who already has them. How about how to run any stored procedure with only PUBLIC privs and EXECUTE privs on the stored proc instead of wasting time on something that doesn't actually increase security at all?

Heh... when you and I finally meet up to drink a beer or two together, let's promise each other now that neither of us will bring this particular subject up. It appears to be the only subject that we have a true disagreement on. :w00t:

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 10

Jeff Moden (9/27/2012)
opc.three (9/27/2012)
Jeff Moden (9/27/2012)
opc.three (9/27/2012)
Jeff Moden (9/27/2012)
opc.three (9/26/2012)
Jeff Moden (9/26/2012)
opc.three (9/25/2012)
sanjuv999 (9/25/2012)
please guide me
Do not enable xp_cmdshell! You do not need it for this scenario and it introduces risk in your environment.
If you want to do everyting with T-SQL use for this task BULK INSERT.
It doesn't introduce any risks that aren't already there. Only an attacker who can get in as "SA" would be able to use it and if (s)he did so, they could also turn it on. It wouldn't even slow them down because they're expecting it to be off.
Enabling xp_cmdshell does introduce risk into an environment despite how slight or irrelevant you think those risks may be.
If you have control over who has "SA" privs, it simply does not. If you don't have that kind of control over your database (and you absolutely should), disabling it is nothing more than a warm fuzzy that won't actually work because anyone with "SA" privs can simply turn it on.
Risk mitigation is all about the "what if" Jeff. I won't argue this point with you anymore. The fact is that enabling it introduces risk.
You HAVE to continue to talk about it because you keep talking about it in a negative fashion! 😉
And I will continue to steer people towards more-secure and more-auditable (and more robust I might add) solutions as long as I have breath 🙂
All you have to do now, my old friend, is find something more secure to steer them to. 😀 For starters, how about how to control who gets SA privs and how to determine who already has them. How about how to run any stored procedure with only PUBLIC privs and EXECUTE privs on the stored proc instead of wasting time on something that doesn't actually increase security at all?

It would be a wonderful pleasure to finally meet you! I would also love to see you present if that opportunity ever arose.

We're eye to eye on the 'control who gets sa.' Consider that sometimes the former is not in the DBAs hands. Case in point that just came up today. I actually thought of our dialog when writing my response to that thread because it is part of what makes up my position on xp_cmdshell.

We're also eye to eye on the 'how to execute with only public.' I implemented the technique you and I corresponded about to close a loop where users who connect to a DB instance through a .NET Windows Forms app using Windows Auth were actually granted direct EXEC on xp_cmdshell, and to make things worse the xp_cmdshell proxy was setup with the same account the SQL Server Service was using and that account was a local admin and had network perms :w00t:, i.e. FULL CONTROL over the entire server if they wanted it, as well as lots of network access :angry: This is why I cringe when I see the recommendation tossed out to enable xp_cmdshell. In my opinion there is no disclaimer strong enough or comprehensive enough to cover what to watch out for that can successfully escort a recommendation to enable it, especially not in the context of an online forum to a newbie developer looking for an expedient way to export data from a table to a flat-file.

I concede that xp_cmdshell has a bad reputation due to things careless or incurious DBAs have done in the past, or from poor AD user-management policies, or from lax policies around who should be a sysadmin, or...you get the idea. That is a shame, but it does not relieve us from having to protect against it the best I can. In fact it should compel us. Enabling it does introduce risk in and of itself even if 'implemented properly' in code. A malicious, legitimately anointed sysadmin, still has less roadblocks to overcome to compromise a system. Not just for elevating ones permissions at a cmd prompt but also for adding a level of misdirection that makes it more difficult to audit their actions.

Heh... when you and I finally meet up to drink a beer or two together, let's promise each other now that neither of us will bring this particular subject up. It appears to be the only subject that we have a true disagreement on. :w00t:

We can leave this issue online for sure. There is a lot of common ground for us to meet on. It's true, we agree on every other point I can think of at the moment. xp_cmdshell can just be the elephant in the room 😉

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato