BCP and remote paths

Question

BCP and remote paths

Jportelas

SSChasing Mays

Points: 625
More actions
April 1, 2008 at 3:50 pm

#120903

Hello there:
Does anyone know if BCP can write to a remote location? I mean in a server different to the current database server, does it need to configure some special permissions on the remote server?
We are trying to do it so but the BCP reports an error when attempting to write to the remote server.
SQLState= HY000, NativeError = 0
Thanks for any hints.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

if what you are talking is if you can bcp a table storing in a text file and save to another location in the network, yes it can be done just replace the path to whatever your network location is

"-=Still Learning=-"

Lester Policarpio

Jportelas SSChasing Mays Points: 625 More actions · Answer 2

Hi Lester:

thanks for the reply.

Yes, we already tried using the network path (i.e: \\Server2\SharedFolder) but the error I mentioned above, showed up when trying this. So what I really need to know is if there are any other settings (permissions to the user BCP runs on, by the way, what user account would it be?...etc) are required to acomplish this task.

Thanks for any further help.

Lowell SSC Guru Points: 323504 More actions · Answer 3

from a real command window, bcp has access to all shares that the caller(you) have access to;'

but if you call bcp from xp_cmdshell, it is almost certainly using a different user contenxt than you expect, and would be prone to issues like not being able to get to a shared drive.

a quick proof of concept:

CREATE TABLE #Results

ID int identity(1,1) NOT NULL,

TheOutput varchar(1000))

insert into #Results (TheOutput)

exec master..xp_cmdshell 'whoami' --nt authority\system for example would be a bad thing

insert into #Results (TheOutput)

exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system is not a user...command fails.

insert into #Results (TheOutput)

exec master..xp_cmdshell 'dir \\serverMaim\SharedFolders\Output\*.*' --NULL because or permission/access denied?

select * from #Results

for access of files or folders, or anything outside of a database context itself, SQLserver uses the context of the startup account of the service to access shares, and not the permissions of the user running the SQL command.

you might need to change that user, or add additional privileges to that user if it is a network user.

SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:

or if the above was blank, the account in services:

That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.

As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.

Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.

you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the external object you were trying to access/use works when SQL is run your credentials, so you'd know you need a domain account to access the resource.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!