BCP

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  November 28, 2007 at 12:40 pm

  #122528

  Hello All

  I have a situation where a database app is using BCP. But we do not allow the cmd_shell to be configured for use. Is there any way to use BCP without having the cmd_shell configured? And also, what kind of security issues are involved with keeping the cmd_shell configuration set to allow?

  Thanks in advance

  Andrew

• Norman Kelm

  SSCommitted

  Points: 1922

  More actions

  November 28, 2007 at 1:28 pm

  #755120

  Andrew,

  BCP is a command line app, so it can be run from the command line. What does database app mean? sql code, vb program...?

  Alternatives are the bulk insert command or creating DTS or SSIS objects programatically to handle the data.

  xp_cmdshell is trouble. Even if it is sa only other parts of the system need to be configured correctly. For instance I did not have access to the production systems, but was sa on dev. The problem is that the dev and production sql servers were running under the same domain account.

  Run this from query analyzer connected to the dev server and I had an sa rights account on the production server

  master..xp_cmdshell 'isql -SPRODSERVER -E -Q"EXECUTE master.dbo.sp_grantlogin ''Domainorman''"'

  master..xp_cmdshell 'isql -SPRODSERVER -E -Q"EXECUTE sp_addsrvrolemember ''Domainorman'', ''sysadmin''"'

  The dev and production sql servers are now have their own domain accounts.

  Norman

  DTS Package Search

  http://www.dtspackagesearch.com/

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  November 28, 2007 at 2:17 pm

  #755142

  Thanks Norman

  That is just what I thought. The app, which is very poorly written, uses BCP to pump some data out of a table. I found out that the SQL code was written by a front-end developer. Nice........

  I am in the process now of creating an SSIS package to handle these tasks.

  Thanks again, have a good one

  Andrew