SQL Server 2005 Express - BULK INSERT Error Code 5 (Access is denied) - Win Vista - sa Authentication

  • I am having some issues on SQL Server 2005 Express on Windows Vista. I installed SQL Server using SQL Authentication. I'm logged in as the sa account via a c# program. I attempt to run a BULK INSERT using a file that I have generated via code in the user's temp folder. When it attempts to run I receive the following error code:

    SQLException: Cannot bulk load because the file ... could not be opened. Operating system error code 5(Access is denied).

    I run the exact same code on SQL Server 2008 installed with Windows Authentication and it runs just fine.

    Is there something special I need to do to "map" the sa SQL account to use the current user's temp folder for BULK INSERT?

    Sorry if this is a silly question. I've been searching the net for hours to find a solution. Yet I have not found anything up to this point.

    Thanks,

    Michael mcDonaldM

  • Michael,

    I can't be sure, but I think this is a permission issue on the folder that contains the file you are trying to bulk insert. As far as I understand it, when you attempt to access the file, you are using the security credentials of 'sa' which is not a regular domain account (its a SQL Server account) and as such you do not have the privileges required to open the file.

    The easiest way to bypass this is to add the Machine/Users group to the folders permissions. For example, if the machine you are using has a machine name of MySQLServerMachine, you would add the MySQLServerMachine/Users to the folder permissions.

    The reason it works with Windows Authentication is because the account you use to login to Windows is part of the Authenticated Users group which will therefore have the permission already set.

    Hope this helps

    Mark

  • Thanks Mark.

    I think I'm going back to Windows Authentication for my installation routine. My users are not going to want to be hassled with assigning permissions to folders while installing my software. So maybe that is the best route for me.

    Michael 🙂

  • Hello,

    I am having a different problem, where I am getting error (Cannot bulk load because the file "\\APPSERVER\TEMP\FileName.csv" could not be opened. Operating system error code 5(Access is denied.)) with my SQL Server DB with windows Authentication. Here is the scenario

    DOMAIN\DBserver

    --> SQL Server 2008.

    --> SQL Services running under DOMAIN\SQLSERVICEACCOUNT

    --> Database with Windows Authentication for DOMAIN\APPSERVICEACCOUNT with Bulk admin rights

    DOMAIN\AppServer

    --> Our Application services running under DOMAIN\APPSERVICEACCOUNT with full rights on the folder from there the files have to be picked up. This service puts a file to be uploaded into the SQL DB on a temp folder which is shared and full rights to the DOMAIN\SQLSERVICEACCOUNT are given under both Sharing and Security tabs.

    --> When our application sends a command to SQL server to pick the file up from \\APPSERVER\TEMP location we are getting Access Denied error.

    --> When we put the same folder on the SQL Server itself, everything seems to work fine.

    We did create a trust between the 2 machines so that they can see each other and work together.

    We are not able to figure out what is it that we are doing wrong. Any help/comments are appreciated.

    Thanks,

    Nitin

  • Hello,

    Forgot to mention that both the App server and the DB server are running on Windows Server 2008 R2 64 Bit.

    Thanks,

    Nitin

  • nitin 69378 (7/21/2011)


    Hello,

    I am having a different problem, where I am getting error (Cannot bulk load because the file "\\APPSERVER\TEMP\FileName.csv" could not be opened. Operating system error code 5(Access is denied.)) with my SQL Server DB with windows Authentication. Here is the scenario

    DOMAIN\DBserver

    --> SQL Server 2008.

    --> SQL Services running under DOMAIN\SQLSERVICEACCOUNT

    --> Database with Windows Authentication for DOMAIN\APPSERVICEACCOUNT with Bulk admin rights

    DOMAIN\AppServer

    --> Our Application services running under DOMAIN\APPSERVICEACCOUNT with full rights on the folder from there the files have to be picked up. This service puts a file to be uploaded into the SQL DB on a temp folder which is shared and full rights to the DOMAIN\SQLSERVICEACCOUNT are given under both Sharing and Security tabs.

    --> When our application sends a command to SQL server to pick the file up from \\APPSERVER\TEMP location we are getting Access Denied error.

    --> When we put the same folder on the SQL Server itself, everything seems to work fine.

    We did create a trust between the 2 machines so that they can see each other and work together.

    We are not able to figure out what is it that we are doing wrong. Any help/comments are appreciated.

    Thanks,

    Nitin

    You do not have the proper permissions as was suggested.:-)

    Please check.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 6 (of 6 total)

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