Post reply

Cannot BULK INSERT on SQL Server Authentication

  • 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?

  • January 12, 2010 at 2:38 pm

    #1102770

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

  • January 12, 2010 at 2:50 pm

    #1102778

    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.

  • January 14, 2010 at 8:47 am

    #1103661

    Anyone?

  • January 15, 2010 at 8:23 am

    #1104243

    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.

  • January 15, 2010 at 8:37 am

    #1104252

    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?

  • January 15, 2010 at 10:06 am

    #1104329

    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.

  • January 15, 2010 at 11:47 am

    #1104375

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

  • January 15, 2010 at 12:31 pm

    #1104403

    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.

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

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