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

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

Thomas W Marshall — Fri, 08 Mar 2013 14:36:36 GMT

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.

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

Angshuman Nayak — Thu, 22 Mar 2012 14:20:13 GMT

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.

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

LukeS — Thu, 22 Mar 2012 14:09:59 GMT

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.

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

LONG PATH TOOL — Tue, 09 Nov 2010 09:39:14 GMT

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

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

LONG PATH TOOL — Tue, 09 Nov 2010 09:38:51 GMT

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

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

Miroslaw Majorek, Aspect Software — Fri, 05 Nov 2010 23:03:21 GMT

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!