April 18, 2019 at 4:43 pm
I have SQL Agent job which I just created but I am running into some issues when I run the job and hoping to get some help from experts. SSIS package has 2 steps. Read the data from an Excel and insert the data into a table. When I run it manually from Visual Studio, it runs just fine. When I run it through SQL agent, it fails. I have admin rights on both source and dest. This job also has me as an owner. I also had networking team gave admin rights to the service account which SQL agent is running under but when I run the job, I get this error
Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "The Microsoft Access database engine cannot open or write to the file '\\filepath\filename.xlsx'. It is already opened exclusively by another user, or you need permission to view and write its data.
The file is not open by any user and service account has full permission on the path so what am I missing?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
April 18, 2019 at 7:16 pm
The issue is that the service account for SQL Agent does not have permission on the share. You may be listed as the job owner, but that is irrelevant here.
If Agent is running under a domain account, you grant access to that account.
If Agent is running under a local account like NT SERVICE\SQLSERVERAGENT, you could grant access to the machine account, that is DOMAIN\ACCOUNT$, although this may be a bit dubious.
Yet an alternative, and this may be the best choice, is to use a proxy. To to this you first create a credential with CREATE CREDENTIAL. Example:
CREATE CREDENTIAL MyCred WITH IDENTITY = 'DOMAIN\USER' WITH SECRET = 'ThePassword'
The you create a proxy for this from Object Explorer in the SQL Server Agent node. The you use this in the Run As dropdown for the step. Proxies cannot be used for T-SQL steps, but this is an SSIS step as I gather.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply