Bulk insert not working from different locations

Question

Post reply

Bulk insert not working from different locations

Peter Kruis

Mr or Mrs. 500

Points: 578
More actions
January 6, 2022 at 2:31 pm

#3970560
I'm having an issue where the user wants to bulk insert an image as a blob in a column. The following SQL is used.
```
INSERT INTO [Schema].[Table]
           (DateTimeCreated
           ,ReferenceId
           ,PrintModeId
           ,Image
           )
Values
           (getdate()
           ,11469 
           ,2
           ,(SELECT compress(BulkColumn) BulkColumn
             FROM OPENROWSET(Bulk '\\AMA1\Labels_printed$\06772013_1Bit.BMP', SINGLE_BLOB) AS BLOB))
GO
```
The statement is executed with a domain user, which has full rights on the folder and also on the file.
When I log in on the SQL Server with that specific user, I start SSMS on the server, I execute that query; It will work, however when I am executing it from another location, with exactly the same user, I get the error:
```
Msg 4861, Level 16, State 1, Line 4
Cannot bulk load because the file "\\AMA1\Triggers\TestFile\06772013Bit.BMP" could not be opened. Operating system error code 5(Access is denied.).
```
Is there a reason why the user can execute this from the server, but not from another machine? The files are accessible (and editable) for that user on both machines.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1381 More actions · Answer 1

The SQL Server service account must have permissions on that directory/file

https://www.sqlservercentral.com/forums/topic/operating-system-error-code-5access-is-denied

https://docs.microsoft.com/en-us/answers/questions/265195/cannot-bulk-load-because-the-file-could-not-be-ope.html

https://techcommunity.microsoft.com/t5/sql-server-support-blog/sql-bulk-copy-error-8220-operating-system-error-code-5-access-is/ba-p/316231

Peter Kruis Mr or Mrs. 500 Points: 578 More actions · Answer 2

SQL Server Service Account has full rights on the folder / file.

That is also confirmed because it works from the SSMS on the server itself

This reply was modified 2 years, 11 months ago by Peter Kruis.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 3

it is a double hop issue for sure - when you execute on the server the credentials are available locally so it can access the share - when you go from PC -> SQL Server -> share the credentials are only known to SQL Server, and are not passed down to the fileshare

also being a sysadmin or not may affect this (sysadmin will default credentials to that of the server account in some cases)

Peter Kruis Mr or Mrs. 500 Points: 578 More actions · Answer 4

Just validated; The Service has full rights on that map.

I am also able to execute the statement succesfully when using a sql login.