Cannot Bulk insert - access denied

Question

Post reply

Cannot Bulk insert - access denied

terry999

SSCertifiable

Points: 5036
More actions
February 3, 2021 at 2:17 pm

#3840537
Hi
Its that most common of probs, can someone see where I might be going wrong. Don't think its a SQL issue.
2 servers on the same domain.
SQLServer
AppServer
On App Server there is a share called Data.
In both Scenarios I logged in using my domain account
1. If I run Bulk Insert on Management Studio on SQLServer it works. Even though its pointing to a share on another machine
2. It fails if I run it on the AppServer in Management Studio. Again logged in as my Domain User
```
Cannot bulk load because the file "\\AppServer\Data\my.csv" could not be opened. Operating system error code 5(Access is denied.).
```
```
select SUSER_NAME()
```
Returns the same user name in both cases i.e my Domain Account. SQL Server runs as a local system account but that shouldn't be relevant as I'm connected with trusted Authentication.
```
CREATE TABLE #t ([name] varchar(10), age int)

select SUSER_NAME()
Bulk Insert #t From '\\AppServer\Data\my.csv' WITH (FIELDTERMINATOR =',', FIRSTROW =2, MAXERRORS=0)
```
Is it a double hop issue.
If I cannot resolve it at least understand..
Thanks

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 2

Hi,

I think this is the same topic:

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

Maybe a kerberos problem with a double hop, or the service account got no permission on the file.

Kind regards,

Andreas

terry999 SSCertifiable Points: 5036 More actions · Answer 3

Thank you Andreas I will certainly check this out, the thread also links to

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

Which looks promising..

Lowell SSC Guru Points: 323518 More actions · Answer 4

the issue is most definitely that the SQL service is running as a local account, and not a domain account.

SELECT * FROM sys.[dm_server_services]

when you run any TSQL that accesses a resource outside of the SQL Instnace and it's databases(think \\incpath, drives on the server itself, etc.)

the service account running SQL server must have access to the resource.

it doesn't matter that suser_name() is both sysadmin and domain admin or anything, access to an external resource is via the context of THAT service account.

this is common when someone puts a file on their own desktop on the server, for example, too; the service account doesn't have access to C:\Users\Lowell\Desktop or C:\Users\Lowell\Documents either.

move the file to a location the service account would certainly have access to(SQLDataRoot,backup directory, etc)

or grant the computer resource YourSpecificServerName$ (note the dollar sign! to the\\AppServer\Data\ folder with at least read permissions , do the migration, and then remove the permissions

the screenshot below has a server named Mango as the example:

This reply was modified 3 years, 9 months ago by Lowell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!