0

We recently moved all databases from MyServerA to MyServerB. Everything worked fine on MyServerA, but when I try to call the bcp command using xp_cmdshell, I get the following error: 

Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file

Some Info:

  1. I'm pretty sure the user I'm logged in as under MyServerB has rights assigned to the remote server location I'm trying to post to. I ran a "whoami" at the command prompt and successfully added them under security with full control on the remote folder (which btw is shared). Plus, I can map to it or access it fine.

  2. Interestingly when trying to send an already existing file, that is from the same location I'm trying to send to, as an attachment using sp_send_dbmail in, I got an Access denied message. So it seems a security issue. But see #1 above.

  3. I also tried saving it in a folder on the local MyServerB, but I cannot do that either.

Sample Code:

DECLARE @bcp varchar(2000);

SET @bcp = 'bcp "SELECT 1" queryout "\\MyServerA\Reports\MyFile.txt" -c -T -S MyServerB';

EXEC master..xp_cmdshell @bcp;
arghtype
  • 4,376
  • 11
  • 45
  • 60
ptownbro
  • 1,240
  • 3
  • 26
  • 44
  • `bcp` is *not* a text export tools. Why don't you use SSIS? The very fact that you had to elevate privileges to run `xp_cmdshell` should be a warning. – Panagiotis Kanavos Feb 16 '17 at 08:26
  • Anyway, why run `bcp` from inside SQL Server? You could run it from a command line or schedule it to run periodically in Scheduled Tasks. You could also schedule the command to run from SQL Agent. Running `xp_cmdshell` requires weakening security. – Panagiotis Kanavos Feb 16 '17 at 08:27
  • This is something I inherited. I'm authorized at this point to update to SSIS or change the script or method. This was working before as-is and they want it fixed as is. Thanks for the suggestions though. – ptownbro Feb 16 '17 at 23:04

2 Answers2

2

Went to a friend who is an expert. The issue was I need to provide permissions to the serivce account from which SQL server was trying to execute xp_cmdshell.

The challenge was the "Service" account was named something different when looking at the "Log On" account that was associated with the SQL Server Service running. That had a NT account associated with the default instance name. What you wanted to select was the account named "SERVICE".

Also, the old machine seemed to work without the need to add this account to that permission.

Anyway, it works now.

Thanks for your help and interest.

ptownbro
  • 1,240
  • 3
  • 26
  • 44
1

Run whoami from xp_cmdshell, I am sure it will give you a clue what needs to be done next.

Laeeq
  • 66
  • 4