Bulk Insert fails in SQL Agent Job, FILE NOT FOUND

Question

I've several entries for this kind of problem, and none of them seem to solve it for me.

My SQL SERVER and SQL SERVER AGENT services are both set to the same user account.

I have a stored procedure which does the following (error-checking cruft removed)

exec XP_CMDSHELL 'net use P: \\machine/share password /user:machine\user'
exec XP_CMDSHELL 'dir p: /b /a-d-h-s > p:\dir.txt'

CREATE TABLE #FilesTemp ( Filename varchar(200) )

BULK INSERT #FilesTemp
FROM 'p:\dir.txt'
WITH
    (
        ROWTERMINATOR = '\n'
    )

When I run the stored procedure from SSMS, it runs fine. When I run it via a job, the XP_CMDSHELL calls work properly and the 'dir.txt' file is created. But the bulk insert fails with an error 3 (p:\dir.txt not found).

I'm logged into SSMS, connected to the SQL Server using 'sa' and the job is owned by sa. The user the share is being created as has the necessary permissions.

MORE INFORMATION:

There was a comment placed (and then apparently deleted) asking if I was running SSMS from the server machine or not. I tried it.

Apparently, when I run using Windows Authentication, the bulk insert fails, but when running stored procedure as SA, it works. The Agent is running using Windows Authentication and fails.

But why? I'm at a loss to understand the security issue.

Any thoughts?

Mike · Answer 1 · 2016-06-28T17:14:17.347

0

Although you are using a mapped drive to create the security for the mapped drive, you CAN'T use the mapped drive for bulk insert.

But you can use the UNC path but the security is that of the SQL Server account.

Note you have a forward slash next to /share I presume that was a typo

exec XP_CMDSHELL 'net use P: \\machine\share password /user:machine\user'
exec XP_CMDSHELL 'dir p: /b /a-d-h-s > p:\dir.txt'


exec Xp_cmdshell  'move p:\dir.txt c:\dir.txt'


CREATE TABLE #FilesTemp ( Filename varchar(200) )

BULK INSERT #FilesTemp
FROM 'c:\dir.txt'
WITH
    (
        ROWTERMINATOR = '\n'
    )

when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data.In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process

https://msdn.microsoft.com/en-us/library/ms188365.aspx

edited Jun 28 '16 at 17:14

answered Jun 28 '16 at 16:15

Mike

1,645
3
13
21

Unfortunately, I get a login error when I try that. – Marc L. Allen Jun 28 '16 at 16:47
The problem is that bulk copy is done in the security context of the SQL Server. You can't use mapped drives, just unc paths. If the server account can't be granted access, then copy the role to a local drive and import from there – Mike Jun 28 '16 at 16:57
Apparently, it runs fine when logged into the SQL Server using sa. See updated post. So, it *can* use the mapped drive. But, there is obviously a security issue. – Marc L. Allen Jun 28 '16 at 17:00
When running as sa it is reverting to the server Windows process account. But when run as a job, it is using a lower level account. Can you change the job to run as sa and retest? – Mike Jun 28 '16 at 17:12
I'm trying to, but can't figure out how to do that. There is a Run As, but it's blank. Google claims that has to do with a proxy, but I can't find an sa proxy. – Marc L. Allen Jun 28 '16 at 17:13
The mapping of the drive letter is done in one context, and the bulk insert in another. Ideally this job should be run as sa, or the underlying Windows account so that the two contexts match. Sorry I am offline now but can you type sa into the run as box on the job? – Mike Jun 28 '16 at 17:24
I tried that... The problem is that there is no user 'sa' only a login. I can only assume that when you login as sa, it manufactures some godlike user context for you. – Marc L. Allen Jun 28 '16 at 18:38
Thanks for your help. I've just gone and used CMDSHELL to copy the files locally and then import. – Marc L. Allen Jun 28 '16 at 18:39

Bulk Insert fails in SQL Agent Job, FILE NOT FOUND

1 Answers1