May 7, 2011 at 11:39 pm
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
May 8, 2011 at 12:47 pm
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
May 8, 2011 at 12:50 pm
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
July 21, 2011 at 4:15 pm
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
July 21, 2011 at 4:16 pm
Hello,
Forgot to mention that both the App server and the DB server are running on Windows Server 2008 R2 64 Bit.
Thanks,
Nitin
July 21, 2011 at 4:20 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy