1

I have two server (in the same network), Server A, is the web server, with IIS and all, Server B is the second server, the DB (MS SQL Server 2012), both Windows 2012 R2.

In the Server A, files are uploaded to this folder, uploaded-bills. That I have shared with \\web-server-01\uploaded-bills\.

I have given FULL ACCESS (in the security tab) to EVERYONE in that folder!

Now, this bulk insert runs in the SQL Server in Server B

INSERT INTO BillDetails (
    ItemID
    ,Qty
    ,Amount
    )
SELECT ItemID
    ,Qty
    ,Amount
FROM OPENROWSET(BULK '\\web-server-01\uploaded-bills\itemised-bill.csv', FIRSTROW = 2, FORMATFILE = 'D:\itemised-bill-format-file.fmt') AS BulkLoadFile;

Which runs perfectly fine. But if I create a job, to ran at a particular schedule, it always fails. When it is triggered automatically (due to time cycle) or I ran the job manually. When I view the history, it shows me this error:

The job failed.  The Job was invoked by Schedule 16 (InsertItemisedBillsEveryFiveMinutes).  The last step to run was step 1 (BulkInsert).,00:00:00,0,0,,,,0
05/29/2017 15:30:00,InvoiceUploadRawInsertJob,Error,1,SERVER-B,BillsUploadRawJob,BulkInsert,,Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot bulk load because the file "\\web-server-01\uploaded-bills\itemised-bill.csv" could not be opened. Operating system error code 5(Access is denied.). [SQLSTATE 42000] (Error 4861).

As I mentioned, I have given everyone the whole access, even then the sql sever agent can't seem to access it. Also, I can access this folder fine in the windows explorer without any issues, and even the query executes fine when you run this query in a new query window. But for the love of Microsoft I can't figure out why the job is failing, and what should I do to fix that. Can anyone help me figure out what am I doing wrong?

Update Update after comment and Remus's answer.

Yes Server A and Server B are in domain, in the same domain.

The SQL Server service (MSSQLSERVER) is running under a windows account name Expo (in the log on tab in the service configuration, in services.msc) amd that Expo account is available domain, so I can log in in Server A as well as Server B using that account's credentials.

The SQL Server Agent is running under (in the log on tab in the service configuration, in services.msc) NT Service\SQLSERVERAGENT.

As for Everyone, I grant it Read\Write in the file sharing dialog, under Sharing tab, and in the Security tab, I've added everyone with Full Control checkbox checked. (But I am guessing the Everyone on Server A could be different from the Everyone on Server B, I could be wrong though).

Please let me know if I could provide any more information to help sort out this.

Update 2
I've created the credentials and proxy, but in the job step dialog, Job Step Properties, the Run as option is disabled because the Type I have selected is Transact-SQL script (T-SQL) which is actually it's type. So, how do I specify the proxy account for this?

10
  • Who is "everyone"? All the local accounts on the local machine? The web server doesn't know who those accounts are. – Aaron Bertrand May 29 '17 at 15:40
  • Yes, Everyone is all local account on the Server A, but I guess for the accounts in Server B, the Server A is unaware of those. I've also updated the question with further details. – Razort4x May 29 '17 at 18:40
  • File shares unfortunately have two security permissions settings. Usually at the share level you just give Everyone access. But then you actually need to go into the folder (not the share) right click and and set access there. – Nick.McDermaid May 30 '17 at 0:39
  • Also I notice the path in your error message isn't the same as the one in the command line - is that a typo? – Nick.McDermaid May 30 '17 at 0:41
  • 1
    Everyone on the server with the share only includes accounts explicitly defined on that server. Local accounts on the other server are not included in that group. So you would have to use a domain account or proxy for the SQL Server Agent service, and make sure that account is explicitly granted access to the share. There are other ways too, such as Agent calling a procedure that executes as that domain account. – Aaron Bertrand May 30 '17 at 1:29
3

When you run that query interactively then SQL Server impersonates you when accessing the share. Normally this would fall into double-hop Kerberos Constrained Delegation and fail unless set up appropriately. It works because probably one of the hops is short-circuited (eg. you log on from a session on Server A or Server B).

When the query is run as a job the query impersonates the SQL Server agent service, and it all depends on how the SQL Server Agent is configured to run and connect to SQL Server. Different answers apply depending on whether the connection is made using a SQL Server login (eg. sa), a domain account, a virtual account, a local built-in account (eg. LocalService) and so on and so forth.

We here cannot guess what the problem is. To document it, please provide the details of the accounts involved. Are Server A/B in a domain? In the same domain? Is SQL Server running as a domain account, as a virtual account, as a local account, as local service, or what? Is SQL Server Agent running as who?

When you granted access to Everyone, did you grant access to the share or did you grant access to the folder?

Ideally you should set up an audit policy and then simply consult the event log, which will tell you exactly which account attempted the access. See Auditing File Access on File Servers

Finally, you may be interested in reading up about SQL Server Agent proxies.

3
  • I've updated the answer, with the details you told me to share. – Razort4x May 29 '17 at 18:37
  • Pay special attention to this line: "grant access to the folder?" that's probably your problem – Nick.McDermaid May 30 '17 at 0:40
  • @Razort4x I suggest you set up audit so you can figure out what account exactly is trying to access the resource. – Remus Rusanu May 30 '17 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.