Cannot bulk load because the file could not be opened error

Question

Cannot bulk load because the file could not be opened error

Brandie Tarvin

SSC Guru

Points: 173056
More actions
March 7, 2023 at 9:18 pm

#4157939

This is a weird one. My user has two NAS shares. QC and Test and two SQL Servers, QC and Test. She has bulk admin permissions on her account and the NAS share (according to the windows group) also has her permissions.
She's running the following code:
select * FROM OPENROWSET( BULK '//My/nas/Share/QC/MyFile', SINGLE_BLOB) AS x;
select * FROM OPENROWSET( BULK '//My/nas/Share/Test/MyFile', SINGLE_BLOB) AS x
Both queries work from the Test server. But when she runs the queries from the QC server, the Test NAS code works but the QC NAS code fails with the following error:
Cannot bulk load because the file could not be opened. Operating system error code 5(Access is denied.)
The ONLY difference I can see is the QC SQL Server is 2019 and the Test SQL Server is 2017. The service accounts are the same for both servers. Our Windows team says all security permissions are the same with both NAS shares. I'm at a loss on this one. The issue, I'm sure, is outside of SQL Server, but this user can't seem to get an answer from anyone as to where the problem might lie.
Any suggestions?
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

I would nearly bet that the QC server does not have the SPN's set or that the connection to that server is not being done using kerberos (thus being unable to delegate permissions)

SPN you can check easily - setspn -L Domain\SQL Service Account Name

it should return SPN's for both servers (or more if it is being used on other server (BAD Practice))

for the connection just have her do select auth_scheme from sys.dm_exec_connections where session_id=@@spid on both servers. if the QC server is not Kerberos that is one issue

Brandie Tarvin SSC Guru Points: 173056 More actions · Answer 2

The SPN thing is a definite possibility, but why would the test NAS work but the QC NAS not work on the QC server?

That's what is driving me nuts. One NAS works on that server and the other doesn't. But thank you for that query. I'll definitely look at the SPN.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 173056 More actions · Answer 3

Ha. I think you're right. NLTM is the method.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

durai nagarajan SSCoach Points: 15778 More actions · Answer 4

is it working ? is there are difference in service account on both servers ?.

Regards
Durai Nagarajan