SQLTeam.com | Weblogs | Forums

Bulk load - Operating system error code 5(Access is denied.)


#1

I have a user who is trying to do a Bulk Insert on a database using a Python script and get´s the error - Cannot bulk load because the file "//server.local.com/File01/In Progress/Work/Script/file.csv" could not be opened. Operating system error code 5(Access is denied.)

Now I am no SQL expert or even an Admin, my knowlegde of SQL is very limited, so I hope someone who has far more knowledge and experience than I do can shed some light on what I can do to help this user.


Details:

  • SQL Cluster - both SQL Servers are running on Windows Server 2012 R2 with SQL Server 2012

Using SQL Server Management Studio:

  • User Domain account (Domain\Fred) is listed in the Security -> Logins on the SQL Cluster
  • Properties of Domain\Fred - Server Roles: bulkadmin & public are both checked
  • Properties of Domain\Fred - User Mapping - Domain User account is mapped to the Database: Firm1 and the default schema is dbo. * Properties of Domain\Fred - User Mapping - Database role membership for Firm1: db_datawriter; db_owner; and public checked

Python Script:
BULK INSERT Firm1.dbo.pyFILE FROM '//server.local.com/File01/In Progress/Work/Script/file.csv' WITH (FIELDTERMINATOR='\

\t', ROWTERMINATOR='\n')

Error:
ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot bulk load because the file "//server.local.com/File01/In Progress/Work/Script/file.csv" could not be opened. Operating system error code 5(Access is denied.).')

Error message in SQL Server Manager:
Msg 4861, Level 16, State 1, Line 3
Cannot bulk load because the file "//server.local.com/File01/In Progress/Work/Script/file.csv" could not be opened. Operating system error code 5(Access is denied.).


The SQL Cluster has access to the UNC: //server.local.com/File01/In Progress/Work/Script/

SPNS have been set for Service Accounts for both SQL Servers which are part of the SQL Cluster.

Any tips or what I should try next would be greatly appreciated.

Steve


#2
  1. Is Fred running this script manually from his machine? Does he have permission to the UNC folder
  2. Is this script being run scheduled task from? If scheduled on a utility/tooling server which user is it using to run the script?
  3. You have 2 distinct SPNs for each Service Accounts for each sql server?
  4. Do these SPNs have RW permissions to this folder UNC folder
    Can you verify by logging in using these SPNs and trying to access above UNC folder
  5. Could the file be already open and the access denied is just a red herring

#3

Likely someone has the CSV file open in excel.


#4

This is not a Python script - it is a T-SQL command and most likely is being run from within SSMS. Even if that is not being run in SSMS it is being run on the SQL Server instance.

When using BULK INSERT the command will be executed in the context of the service account running SQL Server if the user logged into SQL Server using SQL Authentication. If the user logs into SQL Server using Windows Authentication then the permissions will be based on that user.

The access is denied message is stating that the user executing that code does not have access to that file and/or location.

Verify the permissions of the user and how they are accessing SQL Server.


#5

This error generally comes while using SQL Server Authentication and SQL Server is not allowed to access the bulk load folder.
So giving SQL server access to the folder will solve.

You may check this: http://stackoverflow.com/questions/14555262/cannot-bulk-load-operating-system-error-code-5-access-is-denied
Hope!!!!! this helps you.


#6

Thanks for everyone´s Input, much appreciated.

I will test with the user this week and see what the outcome is. Will provide an update as soon as I can.

Thanks again.

Steve