Cannot BULK INSERT on SQL Server Authentication

Question

Post reply

Cannot BULK INSERT on SQL Server Authentication

branden.huxtable

SSC Journeyman

Points: 97
More actions
January 12, 2010 at 12:49 pm

#208943

I have two computer, neither are connected. On one computer, I can run a BULK INSERT command with my program and it works fine, but on a different computer (supposely the same settings), it crashes with the message "Cannot bulk load because the file could not be opened. Operating system error code 5(Access is denied)." Also, the error number is 4861. Doing the exact same thing on the other machine, BULK INSERT will not crash. As far as I can tell, both machines are set up the same way, but there must be one little setting I'm missing.
The SQL Server login account has all server roles enabled. Also, if I connect to the database as Windows Authentication, it will BULK INSERT just fine.
Can anyone help?

Viewing 9 posts - 1 through 8 (of 8 total)

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

thasteve SSCrazy Points: 2707 More actions · Answer 1

thats a typical permission problem read the security considerations on this site

branden.huxtable SSC Journeyman Points: 97 More actions · Answer 2

I'm not quite seeing the answer here. I see that the SQL Server account may not have read access to the file on the local machine, but what I don't get is why that would be and how I can change it (account? file?) to have read access.

branden.huxtable SSC Journeyman Points: 97 More actions · Answer 3

branden.huxtable

SSC Journeyman

Points: 97

January 14, 2010 at 8:47 am

#1103661

Anyone?

Henry_Lee SSCrazy Points: 2714 More actions · Answer 4

When using Windows Authentication - that user must have file system permissions to the file. When using SQL Server authentication - the service account that runs the SQL Server services must have file system permissions to the file. This is because the SQL Server login has no context in the file system.

Go to the services console and look at what account in running the SQL Server services. Then go the file and look at the Security tab. That's what you need to reconcile.

branden.huxtable SSC Journeyman Points: 97 More actions · Answer 5

The concept makes sense. When I check the services, my SQL Server is logged on as local system account. When I check the folders/files, SYSTEM has all permissions. What else should there be?

Henry_Lee SSCrazy Points: 2714 More actions · Answer 6

Generally that is all there should be. However, there are certain nuances with the LocalSystem account that I'm not familiar enough to speak to.

thasteve SSCrazy Points: 2707 More actions · Answer 7

local system cant access network shares if the file is located there?

branden.huxtable SSC Journeyman Points: 97 More actions · Answer 8

Oh gee. Now it decides to work, and I didn't do anything since when I first posted this. Frustrating to know that things "fix itself" without knowing what changed. No lessons learned here.