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?
Everyone
is all local account on theServer A
, but I guess for the accounts inServer B
, theServer A
is unaware of those. I've also updated the question with further details.Everyone
access. But then you actually need to go into the folder (not the share) right click and and set access there.