Bulk insert statement cannto open file on the share

Question

Bulk insert statement cannto open file on the share

Viewing 6 posts - 16 through 20 (of 20 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

suman.dey0709 - Tuesday, May 30, 2017 2:05 PM
I am having the same issue. I have checked for my SQL service account, it has access to the fileshare and also enabled for delegation after its SPN was set. Funny part is user can do the bulk insert using the SQL authentication login but the same fails when Windows authenticated login. When I try to run the query locally from the SQL server it runs fine but it fails when I am remotely connecting to the SQL server from my terminal serer using SSMS. Appreciate any help. I am all out of ideas here. 🙁

Did you try the script Lowell posted on page 1 to see if the account in use has permissions to the share when you're connected through SSMS?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

suman.dey0709 SSC Rookie Points: 44 More actions · Answer 2

@Ed Wagner-- Yes I did execute it yesterday itself and it gave me the output showing the SQL service account and the files which are present in that path. But I have a query here as for the output, the second insert query involving 'cd %userprofile%', why does it show NULL?
Also, please note that I have SPN set for the SQL service account and delegation enabled too, still no luck with the Windows authenticated logins.

Lowell SSC Guru Points: 323518 More actions · Answer 3

suman.dey0709 - Wednesday, May 31, 2017 3:16 AM
@Ed Wagner-- Yes I did execute it yesterday itself and it gave me the output showing the SQL service account and the files which are present in that path. But I have a query here as for the output, the second insert query involving 'cd %userprofile%', why does it show NULL?
Also, please note that I have SPN set for the SQL service account and delegation enabled too, still no luck with the Windows authenticated logins.

null would be normal for a Mydomain\SQLServiceAccoun you createdt, since that account has probably never actually logged into the machine in question. the real question was whether it has access to the file share location. %userprofile% gets created the first time you loginto a machine you never sat at before. no big deal.
you said this portion returns the files in the target path, which was the important part.
insert into @Results (TheOutput) exec master..xp_cmdshell 'dir "\\servername\pj_sharepoint\external\*.*" /b' select * from @Results
Can you post the BCP command you are using? maybe we are missing something obvious, like mapped drives ro something instead of a UNC path.

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!

suman.dey0709 SSC Rookie Points: 44 More actions · Answer 4

here is the command we are using to bulk insert:

EXEC BULK_INSERT
@TABLE_NAME = 'SAS_MASS_UPLOAD_PF',
@SOURCE_FILE_PATH ='\\gwng-vi.eu1.1corp.org\intern\GIMW\port_upd.csv' ,
@FIRST_ROW =1,
@FIELD_TERMINATOR = ';',
@ROW_TERMINATOR ='\n',
@ERROR_FILE_PATH = '\\gwng-vi.eu1.1corp.org\intern\GIMW\bulk_err'

also, please find below the SPN set for my SQL service account and it also enable for delegation on Kereberos service only.

C:\Users\sumand>setspn -l s_<servername>_sql
Registered ServicePrincipalNames for CN=s_<servername>_sql,OU=Clients,DC=eu1,DC=
1corp,DC=org:
MSSQLSvc/<servername>:1433 EU1\s_<servername>_sql
MSSQLSvc/<servername>.eu1.1corp.org:1433 EU1\s_<servername>_sql

Let me know if you need anything else.

suman.dey0709 SSC Rookie Points: 44 More actions · Answer 5

Got another clue! When I am trying the bulk insert locally from my SQL server, the session is using Shared memory as network protocol and the query runs successfully but while trying, remotely it is using TCP when the query fails. Please find the outputs below for the query.

Query:: select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid

Locally from the SQL server;
net_transport auth_scheme
Shared memory NTLM

Remotely( from terminal server using SSMS)
net_transport auth_scheme
TCP NTLM

Can anyone explain this, and if I can force my session to use shared memory as net_transport?

mmezei Grasshopper Points: 17 More actions · Answer 6

Looks like sql server inherently uses the the sql server service login to do the bulk load. HOWEVER they added additional security checks that the user running the query, if connected to sql server as a windows domain user (integrated security), has permission to access the file. It seems that if you are connected to sql server from a remote computer the double hop raises it's ugly head when sql server does this security check. Per documentation, if you connect to sql server as a sql server user you will not have the issue. In that case sql does not do the additional check. See the following two links for some details under the section labeled "Security..." (they even mention the double hop issue). https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server