Solved

SQL Server 2005 Permissions Question - I think

Posted on 2010-09-08
5
212 Views
Last Modified: 2012-09-16
Hi,

I have the following SQL statement that returns the error shown.

BULK INSERT dbo.tablename FROM '\\server\xxx\filename' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '');

Error message...
msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file '\\server\xxx\filename' could not be opened. Operating system error code 5(Access is denied.).

I have added the SQL Service account as a user on the share and can see the file using that account.

Any ideas would be greatly appreciated!

Thanks,

Bill  
0
Comment
Question by:Bill Ross
5 Comments
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 250 total points
ID: 33631882
Aside from share permissions did you also give the SQL service account NTFS permissions?
0
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
ID: 33631903
0
 
LVL 5

Expert Comment

by:dbidba
ID: 33633342
To narrow down where the error is occurring, I recommend that you copy the file to the machine hosting the SQL Server instance and then test the bulk insert. As described above, it must be a folder to which which your account or the service account has access. If it works on a local file, but not a remote file, you may be hitting a double hop issue.
0
 
LVL 14

Author Comment

by:Bill Ross
ID: 33658351
Hi,

Just to follow up - still waiting on SQL admin to make the changes to see if that corrects the issue.

Thanks,

Bill
0
 
LVL 14

Author Closing Comment

by:Bill Ross
ID: 38403040
Still not resolved.  Worked around using MS Access to load into SQL.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now