Bulk Insert -- Access denied issues

Bulk Insert -- Access denied issues

• J-Man3568

  Ok. So can you describe your configuration:

  1. What is the service account

  2. Where is the file located

  3. What are the ACLS on the file or share or directory for the service account

  4. Who is running the BULK INSERT Is it a user who is authenticated using SQL login or Windows Login

  5. If user is authenticated using Windows login then can be access the network resource So check the ACLS on the file or share or directory for the user's Windows login since the SQL Server service will impersonate him/her.

  6. If user is authenticated using SQL login then the service account credentials will be used,

  7. Is the BULK INSERT being executed as part of SP with an impersonation context specified in SQL Server 2005 This complicates matters slightly so you will have to read more about external access permissions in BOL (start with EXTERNAL_ACCESS database option).

  8. Lastly, if the request is distributed one (making hop between multiple SQL Servers) then you will have to check your security delegation settings. If you are using Windows authentication and the credentials are not being delegated properly then you will get errors.

  
  
  

• shamuthekillercoder

  Thanks!!! I never would have thought to look this up in books online.

  Unfortunately, as with others who have responded to this thread, I have tried everything mentioned in books online and it did not resolve the problem.

  
  

• Alex Weinert

  Could you please check the BOL topics mentioned in one of the posts above The security considerations topic for bulk insert documents the changes made in SQL Server 2005 and it should give you an idea of how to resolve the problem.
  
  

• PBuchetta

  Has anybody resolved this issue

  I am using OS 2003 with SQL 2005 and am using SQL server Management Studio to Bulk Insert. The SQL service is running under a network login that has Admin privliges on the network, as does my login.

  I can browse to this file with both logins.

  My script..

  bulk insert dbo.OST_DataComm_Log_Data_Load

  from '\\myserver\r-drive\DataComm\Logs\Site2Site.Log'

  with

  (

  datafiletype = 'char',

  rowterminator = '\n',

  TABLOCK

  )

  and get the message:

  Msg 4861, Level 16, State 1, Line 2

  Cannot bulk load because the file "\\myserver\r-drive\DataComm\Logs\Site2Site.Log" could not be opened. Operating system error code 5(Access is denied.).

  HELP!

  
  

• coosa

  I did the steps mentioned in the links in my last post. It did not resolve the issue.

  I had some other pressing issue, so moved on for now. Will have to get back to it, at some point of time.

  
  

• Johnny0910

  Vivek,

  I have the same situation like yours.  Did you resolve this issue   Please reply me back at the earliest.

  Thanks.

  
  
  

• baziz

  This is a supported scenario. Have you configured your SQL Server for delegation Please refer to BOL section 'confguring linked servers for delegation'
  
  The BOL entry for your bulkload scenario: 'security consideratons for using transact-sql to bulk import data'
  
  Please let me know how it goes. Unfortunately I can't test it locally as our domain does not allow delegation setup.
  
  

• Mister

  You need to check your security delegation setting for your setup to work. The security delegation should be setup for the service user account/port combination and registered. So it is still a configuration issue and you should contact your network admin to verify settings again. See the KB articles for Windows and search for SETSPN utility for more details.
  
  

• Sam_bham

  Sunil: I am experiencing the same problem on my SQL 2005 / Windows 2003 upgraded server as was detailed in the original post of this thread.

  My batch job is doing a remote Bulk Insert worked fine under SQL 2000 / Windows 2000. Now, however, after the upgrade, it is failing.

  And, yes, my SQL Server Service accounts all have rights to the remote file (referenced using a UNC path) but I still am getting the "access denied" error.

  Help! I have Kerberos enabled and it is working. Is there some new security policy or permission in Windows 2003 that is stopping this from working

  Note: If I copy the file locally to the SQL Server and change my code to reference the local file; no problem. It is only when it tried to access the remote file via the BULK INSERT statement that I get the access denied.

  
  
  

• Stijn Fonck

  Thanks for the reply Sunil.
  Adding the links to the 2 sections you have mentioned.
  
  Setting up SQL Server for delegation
  http://msdn2.microsoft.com/en-us/library/ms248588
  
  Security considerations for using Transact-SQL to bulk import data
  http://msdn2.microsoft.com/en-us/library/ms246113
  
  I will check the things specified with my domain admin and update.
  

• Wops

  Did anyone ever get this issue resolved I'm having the same problems in SQL 2005.
  

• davros-too

  In response to your questions:

  1. The service account is network windows login.

  2. The file is located on a separate server than the database server.

  3. The ACLS for the file/share is set to full control-Everyone.

  4. The Bulk Insert command is being executed by a user logged in to Management studio using windows authentication.

  5. The user that is attempting the Bulk Insert command is an administrator of the network resource (file server)

  6. The service account also has administrator rights on the file server (just in case)

  7. The Bulk Insert command is being used (& failing) from both a Query Window and from a stored procedure with no impersonation context specified.

  8. The request is making hops between multiple machines. From a workstation running Management Studio to a Database Server running SQL 2005 to a file server running Server 2003. Going directly form the Database Server to the File Server works with no problems. My network admins tell me that security delegation is set up correctly. If you know of anything specifically for them to check it would be helpful.

   

  Thanks.

  
  

• Jazz4sale

  I am replying to my own note since I was able to resolve this issue and wanted to share it with others just in case they were having issues too.

  In my case, the code we were running was shelling out to the OS and using ISQL to call the BULK INSERT. After thinkning about it for a while, I changed the ISQL statement to an OSQL statement and my correct credentials got passed tot he remote server! This resolved my problem.

  Apparently, ISQL was trying to log on to the remote server using anonymous access. I was able to see this in the event log. When I changed the code to use OSQL, credentials were passed and authenticated.

  
  
  

• dagilleland

  I too am experiencing an Access is Denied error when trying to bulk insert a file from a network path.  As with previous posters, if I login to the server (Computer1) and execute the bulk insert command (using a query in Sql Server Management Studio):

    BULK INSERT Tmp_Peptide_Import FROM '\\Computer2\LTQ\InputFile.txt'

  the data successfully loads into the table.  However, if I'm using my desktop machine (Computer2, running Windows XP SP2) connect to the server (Computer1, which is running Sql Server 2005 SP1 on Windows 2003 Server) and try to bulk insert the data from the third computer (Computer3), the bulk insert fails (using SSMS or Query Analyzer).  I do not encounter this problem with Sql Server 2000 so it is definitely a result of tightening the security.

  I've read the BOL entries that discuss delegation and authentication and I don't see what to change on the server.  I'm not bulk inserting data from a remote Sql Server instance -- I'm bulk inserting data from a text file that happens to reside on a network share.  If I copy the file to the server (Computer1), then I can bulk insert the data when connected from my desktop computer.

  The reason this is particularly troubling is that I wanted to single step debug through the T-Sql in a stored procedure.  I have Visual Studio 2005 installed on my desktop, but don't have it on the server (and don't want to install it on the server).  Thus, unless I can figure out how to fix the access issues, my only choice is to update the SP to copy the file locally, or shell out to the OS and call OSQL (as suggested by Paul Olivieri on 27 May 2006).

  Trying to answer Umachandar's questions:
   1. --> Sql Server service (on Computer1) is running under a network account with read access to the file (UserA).  If Sql Server Agent runs a SP with the Bulk Insert command (running as UserA) then the insert from Computer3 works fine.
   2. --> File is located on a Network computer (Computer3)
   3. ACLS for the service account are Read/Write access to the file on Computer3
   4. I'm connecting to the server (Computer1) from my desktop (Computer2) using a network account (UserB), connected via Windows Login and TCP/IP (confirmed with cliconfg.exe).
   5. UserB has read access to the file on Computer3 (and is Sql Server admin on Computer1)
   6. ->  n/a
   7. --> The BULK INSERT is being executed in a SP, but not with any impersonation context that I know of; it's simply a T-Sql stored procedure.  Also, simply executing a Bulk Insert command in Query Analyzer fails, so we can't blame the security context of the SP
   8.  --> Nope, not a distributed query

  Matt
  

  
  
• Bulk Insert -- Access denied issues