Cannot bulk load Operating system error code 5 Access is denied

Question

For some weird reason I m having problems executing a bulk insert  BULK INSERT customer stg FROM  C  Users Michael workspace pydb data andrew out txt  WITH       FIRSTROW 0      FIELDTERMINATOR   t       ROWTERMINATOR   n     I m confident after reading this that I ve setup my user role correctly  as it states     Members of the bulkadmin fixed server role can run the BULK INSERT statement   I have set the Login Properties for the Windows Authentication correctly  as seen below    to grant server-wide permissions on bulkadmin   source  iforce co nz  And the command EXEC sp helpsrvrolemember  bulkadmin  tells me that the information above was successful  and the current user Michael-PC Michael has bulkadmin permissions    source  iforce co nz  But even though I ve set everything up correctly as far as I know  I m still getting the error  executing the bulk insert directly from SQL Server Management Studio   Msg 4861  Level 16  State 1  Line 2 Cannot bulk load because the file  quot C  Users Michael workspace pydb data andrew out txt quot  could not be opened  Operating system error code 5 Access is denied     which doesn t make sense because apparently bulkadmins can run the statement  am I meant to reconfigure how the bulkadmin works   I m so lost   Any ideas on how to fix it

User · Answer

In our case it ended up being a Kerberos issue. I followed the steps in this article to resolve the issue: https://techcommunity.microsoft.com/t5/SQL-Server-Support/Bulk-Insert-and-Kerberos/ba-p/317304.

It came down to configuring delegation on the machine account of the SQL Server where the BULK INSERT statement is running. The machine account needs to be able to delegate via the "cifs" service to the file server where the files are located. If you are using constrained delegation make sure to specify "Use any authenication protocol".

If DFS is involved you can execute the following Powershell command to get the name of the file server:

Get-DfsnFolderTarget -Path "\\dfsnamespace\share"

User · Answer

Go to start run  services msc  SQL SERVER MSSQLSERVER  stop the service  Right click on SQL SERVER MSSQLSERVER    properties  LogOn Tab  Local System Account  OK Restart the SQL server Management Studio

User · Answer

sometimes this can be a bogus error message  tried opening the file with the same account that it is running the process   I had the same issue in my environment and when I did open the file  with the same credentials running the process   it said that it must be associated with a known program  after I did that I was able to open it and run the process without any errors

User · Answer

1  Open SQL  2  In Task Manager  you can check which account is running the SQL - it is probably not Michael-PC Michael as Jan wrote   The account that runs SQL need access to the shared folder

User · Answer

I had the same problem SSIS 2012 and the solution was to use Windows Authentication  I was using SQL authentication with the sa user

User · Answer

This is what worked for me   Log on SSIS with Windows authentication   1  Open services and find MSSQL NT Service account name and copy it     2  Open folder from which SQL server should read from  Security - Group or user names tab - Add and paste there copied account        You will probably get  Multiple names found error   just select MSSQL user      Your BULK INSERT query should run fine now  If problem persists try adding SQL Server Agent account to folder permissions in same way  Make sure you restart MSSQL server in services after you are done

User · Answer

I have come to similar question when I execute the bulk insert in SSMS it s working but it failed and returned with  Operation system failure code 5  when converting the task into SQL Server Agent    After browsing lots of solutions posted previously  this way solved my problem by granting the NT SERVER SQLSERVERAGENT with the  full control  access right to the source folder  Hope it would bring some light to these people who are still struggling with the error message

User · Answer

Make sure the file you re using   C  Users Michael workspace pydb data andrew out txt   is on the SQL server machine and not the client machine running MSSMS

User · Answer

This error appears when you are 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 the issue    Here is how to  Go to the folder right click - properties- Security tab- Edit- Add on the new window  - Advanced -  Find Now  Under the users list in the search results  find something like SQLServerMSSQLUser UserName SQLExpress and click ok  to all the dialogs opened

User · Answer

This is quite simple the way I resolved this problem   open SQL Server right click on database  you want to be backup  select properties select permissions select your database role  local or cloud  in the you bottom you will see explicit permissions table find  quot   backup database  quot  permission and click Grant permission    your problem is resolved

User · Answer

Try giving the folder s  containing the CSV and Format File read permissions for    MSSQLSERVER    user  or whatever user the SQL Server service is set to Log On As in Windows Services

User · Answer

I don t think reinstalling SQL Server is going to fix this  it s just going to kill some time     Confirm that your user account has read privileges to the folder in question  Use a tool like Process Monitor to see what user is actually trying to access the file  My guess is that it is not Michael-PC Michael that is trying to access the file  but rather the SQL Server service account  If this is the case  then you have at least three options  but probably others    a  Set the SQL Server service to run as you  b  Grant the SQL Server service account explicit access to that folder  c  Put the files somewhere more logical where SQL Server has access  or can be made to have access  e g  C  bulk      I suggest these things assuming that this is a contained  local workstation  There are definitely more serious security concerns around local filesystem access from SQL Server when we re talking about a production machine  of course this can still be largely mitigated by using c  above - and only giving the service account access to the folders you want it to be able to touch

[sql-server] Cannot bulk load. Operating system error code 5 (Access is denied.)

Examples related to sql-server

Examples related to tsql

Examples related to bulkinsert