BULK INSERT - Access denied

Question

BULK INSERT - Access denied

RVO

SSCertifiable

Points: 5495
More actions
October 13, 2016 at 12:11 pm

#331347

Access is denied when
running BULK INSERT.
Using Windows Authentication.
running from SSMS.
My account is in bulkadmin.
BULK
INSERT Scene.TempStore
FROM '\\10.150.173.2\User_Share\Scene\In-Files\Scotia\ksmtzo2mseg08'
WITH
(
ROWTERMINATOR = '\\'
)
GO
---------------------------------------------
Msg 4861, Level 16, State 1, Line 3
Cannot bulk load because the file "\\10.150.173.2\User_Share\Scene\In-Files\Scotia\ksmtzo2mseg08" could not be opened. Operating system error code 5(Access is denied.)
Troubleshooting steps:
1. Added SQL service account to \\10.150.173.2\User_Share\Scene\In-Files\Scotia\ (read/write)
2. Added my Windows Account to \\10.150.173.2\User_Share\Scene\In-Files\Scotia\ (read/write)

Viewing 3 posts - 1 through 2 (of 2 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

RVO (10/13/2016)
Access is denied when
running BULK INSERT.
Using Windows Authentication.
running from SSMS.
My account is in bulkadmin.
BULK
INSERT Scene.TempStore
FROM '\\10.150.173.2\User_Share\Scene\In-Files\Scotia\ksmtzo2mseg08'
WITH
(
ROWTERMINATOR = '\\'
)
GO
---------------------------------------------
Msg 4861, Level 16, State 1, Line 3
Cannot bulk load because the file "\\10.150.173.2\User_Share\Scene\In-Files\Scotia\ksmtzo2mseg08" could not be opened. Operating system error code 5(Access is denied.)
Troubleshooting steps:
1. Added SQL service account to \\10.150.173.2\User_Share\Scene\In-Files\Scotia\ (read/write)
2. Added my Windows Account to \\10.150.173.2\User_Share\Scene\In-Files\Scotia\ (read/write)

Is the service account trusted for delegation?

Are the SPNs created for the service account? If you type this at the command line:

setspn –L Account YourDomain\YourSQLServiceAccount

you should get two SPNs back - one by host name and one by fully qualified domain name.

Sue

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

This sounds like the Kerberos Double-Hop problem. What happens when someone with sysadmin privs runs the code from the server itself?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)