SQL BULK COPY ERROR (“Operating system error code 5(Access is denied.)”)

 

While there are various forms of bulk copy this blog specifically deals with copying data from a file into SQL Server. It deals about the specific error “Operating system error code 5(Access is denied.)” which might crop up under certain circumstances when doing a bulk copy. For a while now I worked with a lot of DBAs and Developers bewildered with the problem and most of them complaining about the lack of good documentation about it and hours spent diagnosing in the wrong direction. If you are looking for details about bulk copy visit https://msdn.microsoft.com/en-us/library/ms130809(SQL.90).aspx

 

 

You run the following query

 

 

 

 

 

BEGIN

 

 

  BULK INSERT ENVPOT_R

 

 

  FROM '\\advdev64\BulkTest\test_bulk_insert.txt'

 

 

  WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n')

 

 

END

 

 

 And end up getting the following error

 

 

 

 

 

 

 

Msg 4861, Level 16, State 1, Line 1

 

 

Cannot bulk load because the file

 

 

 

"\\advdev64\BulkTest\test_bulk_insert.txt" could not be opened.

 

 

 

Operating system error code 5(Access is denied.).

 

 

 

 

The usual troubleshooting that DBAs do is to chase the “Access Denied” error from a file/folder access perspective. Some of them are as follows.

 

 

 

a) Added “full” access to “everyone” (just temporary to test this) on the BulkTest folder and still getting the same error.

 

 

 

b) Added “full” access to the SQL server service account on the BulkTest folder and still get the same error.

 

 

 

c) The user is made a member of the bulkadmin fixed server role.

 

 

 

 

 

 

 

Many DBAs come with prior experience on SQL Server 2000 where the following was true.

 

 

 

Once a user was authenticated, access to external files was based on the security profile of the SQL Server process. When the SQL Server process had read access to a file, for a user that did not have access to the file but was a member of the bulkadmin fixed server role, the user could import the file by using BULK INSERT and access the contents of the file.

 

 

 

But that had a security issue and the way SQL Server 2005 and later versions handle access to external files is different.

 

 

 

The following are the salient points you need to keep in consideration and are also mentioned in detail here https://msdn.microsoft.com/en-us/library/ms175915(SQL.90).aspx 

 

 

 

a) The data file must be shared between the two computers

 

 

 

b) To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form of \\ Servername \ Sharename \ Path \ Filename.

 

 

 

c) The user account that is used by SQL Server must have been granted the permissions that are required for reading the file on the remote disk.

 

 

 

But there is another important consideration that needs to be taken care of when we have a setting as below and Windows authentication is being used.

 

 

 

 

 

 

 

Client application from client machine à SQL Server (SQL service account impersonating client account) \\File Server (impersonated client credentials)

 

 

 

a) We need to have CIFs SPN for the file server.

 

 

 

Whenever a computer is joined to a domain, it is assigned 2 SPN's by default: HOST/netbiosName, and HOST/FQDN.com. netbiosName being the machine name of the computer being joined to the domain, and FQDN.com being the fully qualified machine name. These two SPN's use the generic "HOST" service type which includes all the various services that *come*be default with Windows. Therefore, if we connect to https://machineName or https://machineName.company.com, we will already have SPN's set that will handle Kerberos when using those names. When trying to connect to \\machineName\SomeShareName  we would be all set for Kerberos (UNC's need a "CIFS" SPN which is included under "HOST" also).

 

 

 

 Complete list of the different service types included in HOST of can be found in this technet article.

 

b) Configuring Kerberos delegation on the SQL Server box.

 

 

 

The requirements are as follows.

 

 

 

 

i) A domain user running the query from management studio must not have the Account is sensitive and cannot be delegated selected option.

 

 

 

 

 

 

 

 

ii) SPNs must be registered for the SQL Server service if the service account is a domain account.

 

iii) The client must be connecting to the SQL using TCP. https://support.microsoft.com/default.aspx?scid=kb;EN-US;811889

 

 

 

 

 

 

 

 

The service’s SPN must be registered by a domain administrator if the service account is a domain user account. If the service account uses the computer’s account, then the process can register by itself or the local administrator can register it by using Setspn.

 

 

 

 

 

At a command prompt, type:

 

 

 

setspn –L Account domain\sqlServiceAccount

 

 

 

 

 

 These two SPNs for SQL service account must come up for delegation to properly function:

 

 

 

 •One for MSSQLSvc/Host:1433, where Host is the name of the host computer.

 

 

 

•One for MSSQLSvc/FQDN:1433 where FQDN is the fully qualified domain name of the computer running SQL Server .

The port number at the end may vary depending on the actual port the SQL Server is listening on. For the sake of brevity I have assumed the default port 1433.

 

 

 

 

 

If there are no MSSQLSvc SPNs listed or there is an SPN missing, then we need to add the appropriate SPN using the setspn –A command for delegation to work properly.

 

 

 

 

 

 

 

At a command prompt, type:

 

 

 

setspn -A MSSQLSvc/< Host >:<Port> <SQL_Service_Account>

 

 

 

setspn -A MSSQLSvc/<FQDN>:<Port> <SQL_Service_Account>

 

A sample is as below

 

 

 

setspn -A MSSQLSvc/MySQLServer:1433 domain\sqlServiceAccount

 

 

 

setspn -A MSSQLSvc/MySQLServer.MyDomain.com:1433 domain\sqlServiceAccount

 

 

 

 

 

 

 

 

 

iv)The SQL Server service account must be trusted for delegation.

 

 

 

 

 

 

Once the delegation is set properly the bulk copy should work fine and we shouldn’t get any errors.

 

 

 

I am not delving deep into Kerberos troubleshooting as the same is well documented in another blog by my colleague here. The same can be referred if we still continue getting Kerberos related errors.

 

 

 

 

Author : Angshuman(MSFT), SQL Developer Engineer, Microsoft 

Reviewed by : SMAT(MSFT), SQL Escalation Services, Microsoft

Comments

• Anonymous
  November 05, 2010
  I have been struggling with this particular problem for about a week. This solution helped get me back on track in no time. I was not aware about the delegation; when I configured the SPN for hostname/port the BULK INSERT started working. This is great for failover clusters, where you can have your application prepare the data file and put it on a cluster share and have SQL Server pick the bulk data up from that share. Thanks to this solution you don't have to ensure that the share is always on the same machine as SQL Server. Thank you!

• Anonymous
  November 08, 2010
  Hello guys, Do you encounter errors while deleting files in Windows? I'm here to provide a solution. I've been reading several threads on this topic on different forums where computer users were asking about this popular error "The filename you specified is not valid or too long". My research helped me to find a tool for you guys. Its LONG PATH TOOL, a very easy to run but highly powerful software. Follow the link to read more about this error. http://www.longpathtool.com

• Anonymous
  November 08, 2010
  Hello guys, Do you encounter errors while deleting files in Windows? I'm here to provide a solution. I've been reading several threads on this topic on different forums where computer users were asking about this popular error "The filename you specified is not valid or too long". My research helped me to find a tool for you guys. Its LONG PATH TOOL, a very easy to run but highly powerful software. Follow the link to read more about this error. http://www.longpathtool.com

• Anonymous
  March 21, 2012
  Before doing any of this, ensure the service account that SQL instance is running as has access to the file (not sql agent account).  That resolved the issue for me.

• Anonymous
  March 21, 2012
  Hi Luke! Thanks for look into the post and provding your suggestions. I had already mentioned it in the initial part of the blog. The usual troubleshooting that DBAs do is to chase the “Access Denied” error from a file/folder access perspective. Some of them are as follows. a)Added “full” access to “everyone” (just temporary to test this) on the BulkTest folder and still getting the same error. b)Added “full” access to the SQL server service account on the BulkTest folder and still get the same error. Please let me know if you feel there is a way we could be more succint so that it helps people faced with the issue. Once again I appreciate your post.

• Anonymous
  March 07, 2013
  Thanks for the interesting post. I, like most DBAs, approach error five by looking at the permissions on the file, and work my way back. I tend to not to be so liberal as to grant all permissions to everyone, but I do tend to grant myself, or the AD user the process is to run as full control over the file and read access to the file.

• Anonymous
  October 08, 2013
  I'm in my first SQL class, and I'm running MS SQL Server in my Acer Aspire One running Windows 7. I've just encountered this error. The proposed solutions don't seem to apply to my case, as I'm not running any kind of rights management.

• Anonymous
  October 08, 2013
  The comment has been removed

• Anonymous
  January 22, 2014
  Good points here .... good to see a collection of suggestions in one place ... I had a prob where I shared a directory and then I got the "error “Operating system error code 5(Access is denied.)”" I reset the directory permissions and this fixed this ... nothing worse then getting issues like this out of the blue !!!

• Anonymous
  March 12, 2014
  According to Microsoft's own documentation here msdn.microsoft.com/.../ms175915(v=sql.90).aspx, the folder/file access rights used are different depending on which method you use to connect to SQL Server.  SQL Server service account if you are connecting via a SQL login or your own account if you are connecting via Windows Authentication.  Just making people aware of that fact. Michael

• Anonymous
  March 20, 2014
  We have a process on eight domain controllers and this error surfaced on only one of them after years of running without issue. A network trace on the domain controller showed only one SMB2 call for the file; it contained 'sqlquery' (named pipes). The Client Protocol Properties on the SQL server showed the following enabled protocol order: TCP/IP,Named Pipes. We were not able to determine why this one domain controller had the issue (possibly IPv6?). As a resolution, because we did not want to remove Named Pipes from the SQL server configuration (other databases on that server may have been utilizing them), we changed the script code to include the 'tcp:' prefix for the Data Source in the connection string. This forced the use of TCP/IP. Oddly, the next day, changing the code back (removing 'tcp:'), the issue did not return. In any event, we'll keep the code change to specify 'tcp:'. HTH REFERENCE: SqlConnection.ConnectionString Property msdn.microsoft.com/.../system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx

• Anonymous
  July 09, 2014
  Thanks for the information. That was an useful blog. I felt that the roll of CIFS has not been documented clearly as the option says to Enable the Delegation of All services on the Database Server. But later Microsoft itself accepted that this option should not be used due to security risk. In this case, the Delegation should be set for the Service CIFS (not MSSQLSvc) on the Domain controller running on the File server ( where the file resides) against the Database server . The role of CIFS is that when DB server communicates to the External server (File Server) the objects are passed as stream and in order to have the SQL server reads back the stream from the file server, delegation has to be put on this. the blog way still works as it covers all Services. In detail, Open up AD for the Database server and go to delegation tab. select "Use  any  Authentication Protocol" and add service CIFS running on File Server. Do not forgot to restart the DB server after the delegation, for the bulk insert to work. Hope this helps

• Anonymous
  February 25, 2015
  Thank you for helping me fix my problem! I had set up all the sharing and permissions correctly, but then I didn't change the way I was pointing to the file. Using the share name did the trick.

• Anonymous
  January 25, 2017
  We have this process working on multiple machine, except for one user. the machine where this doesn't work. we are all on the same domain. not sure if different subnet makes the difference