Solved

SQL Server 2005 Permissions Question - I think

Posted on 2010-09-08
5
214 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

831 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