Bulk Insert from Mapped Network Drive

Bulk Insert from Mapped Network Drive

• Alex Moura

  Still, the original problem is permissions, though I agree that the best practice is to specify remote paths by UNC instead of relying on mapping drives.

  To get rid of the access denied message, you must make sure that the account performing the bulk insert has the rights to read your file on the remote share. This account is the account that the SQL Server service is using - not the account of the user issuing the query.

  =;o)
  /Kenneth

  
  

• Arrgh

  I am encountering the same issue...

  I am running a BI query on my desktop to a SQL 2005 server trying to load a file from a 3rd server.

  bulk insert OST_DataComm_Log_Data_Load

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

  with

  (

  datafiletype = 'char',

  rowterminator = '\n',

  TABLOCK

  )

  My login account has all the rights it needs as does the Network login account that I am running the SQL Service under. I can browse to the file using either login.

  I am using the UNC naming convention.

  If I login to the SQL 2005 server as myself, they query runs correctly and loads the data. If i login to my workstation and use SQL Server Management Studio, the query fails with:

  Msg 4861, Level 16, State 1, Line 7

  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.).

  
  

• spgomathi

  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 '\\Computer3\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.

  Note that another thread is present in the MSDN Forums on this issue, though the posts there were not helpful for me; see http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=107193&SiteID=1

  
  

• H a t e m

  I don't think so. In SQL 2005 the SQL service account is only used when the connection made to the SQL Server uses SQL Authentication. Otherwise it will use the user logged in which means that if your not in a Kerberos environment it won't work.
  
  

• EjayBoy

  Though this only applies if Kerberos is used, if I remember correctly
  (ie it's not entirely enough to use SQL 2005, the windows environment also needs to support delegation)

  But, do we know what platform the poster is on
  If he's on anything else than SQL 2005 and a windows environment supporting Kerberos, isn't it then the case that the account in question is the SQL Server service account that needs the remote permissions...

  Until we know more details, it's hard to say where exactly the problem lies.
  What we do know it that it's permissions related.

  =;o)
  /Kenneth

  
  

• MrYanda0

  One key to validating permissions for this (and getting rid of the "Anonymous Login" attempt in the security event log of the fileshare server) is to make sure you are using KERBEROS authentication. One easy way in SQL 2005 to do this is executing:
  
  select auth_scheme from sys.dm_exec_connections
  where session_id=@@spid
  
  It should say KERBEROS, and NOT NTLM, ... thats what worked for us anyway. :)
  BULK INSERT from a file share would not work for us until we were using Kerberos authentication, as delegation does not work using NTLM.
  
  
  

• Jørn Andre

  You should as has been said use a UNC path, mapped drives are specific to a user profile and not the whole server. a UNC path resolves this.

  In addition if you are fortunate enough to be running in a NT4 domain then you may encounter a problem if you are connecting from computer A to SQL on computer B which is trying to BULK insert from computer C. This is due to a tightening of security in SQL 2005 and the issue with 2 stage impersonation which can't be done in NT4.

  
  
  

• Mike00000001

  This snippet is from BOL.

  When executing the BULK INSERT statement using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

  To resolve this use SQL Server Authentication and specify a SQL Server login, which uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about enabling a user account to be trusted for delegation, see Windows Help.

  It does seem similar to your problem.

  /Kenneth

  
  

• briguy9872

  In SQL Server 2005, with Windows authentication the server will always impersonate the user's credentials. This means when you connect from say desktop1 to server1 accessing files on server2 you need to have security delegation setup. In your case, the security delegation should be setup for the service user account/port combination and registered. In older versions, once the user is authenticated in SQL Server the service credentials will be used to access external resources. This is still the case in SQL Server 2005 for users authenticated via SQL logins.
  
  

• Pradnya

  I guess we're back at square one then

  We don't know if the poster is on SQL Server 2005, or if his environment is able to use Kerberos for delegation. All we know so far is that he is getting an 'access denied' message. =:o/

  /Kenneth

  
  

• Maddy_9

  If file is a remote file (in your case a mapped network drive), specify the Universal Naming Convention (UNC) name.

  
  

• somebodynobody

  All three machines are members of an active directory domain, the username running the Sql Server service is a domain user, and I'm connecting to the Sql Server as a domain user. You are correct that if I connect with a Sql Server login, then the Bulk Insert is successfull. Can you explain why the Bulk Insert works with a Sql Server login but not as a domain user What was changed between Sql Server 2000 and Sql Server 2005 Or, is this a Windows 2003 Server issue Any suggestions on configuration changes would be helpful. Note, too, that several people have experienced this problem and thus it would be helpful if the Sql Server development team could look into fixing this problem (or making it easier to update the permissions to fix this problem) in a future service pack for Sql Server 2005.

  
  

• Acumen

  Your machines have to reside in an active directory domain or change your login to the sql server to be sql authentication
  
  

• Jeff1

  That was the case with SQL 2000 but not the case with SQL 2005 if using a trusted connection. This is the section from BOL.

  Security Account Delegation

  If a SQL Server user is logged in using Windows authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process. For more information, see Security Considerations for Using Transact-SQL to Bulk Import Data.

  When executing the BULK INSERT statement using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

  To resolve this use SQL Server Authentication and specify a SQL Server login, which uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about enabling a user account to be trusted for delegation, see Windows Help

  
  
  
• Bulk Insert from Mapped Network Drive