SSIS "Access to the path is denied" error?
I have created a SSIS package that works just fine from my desktop. I am now testing it by deploying it to the server. The task was imported into the "File System" store. I connected to the SQL Server, from my desktop, with SSMS. I right clicked the package and selected "Execute" If I just validate the task, it validates without errors. When I go to run the task I get a "Access to the path is denied" error at the start of the first file system task. I am sure it is a permission issue. Based on the info from another thread in another forum:
"Security to network shares is dependent on the user who is running the package. If you are running it yourself, make sure you have permissions to the share. If you are running it from SQL Agent, you might need to use a proxy account (search for SQL Agent Proxy in Books Online) to access it."
I have some questions:
1. When I select "execute" what account is SQL Server using for permissions for accessing network folders? Is it mine, SQL Server Agent, something else? If it is my account, why would I get an "Access to the path is denied" error?
2. I don't understand how to use a proxy account, or even if I need one. I assume that when I set the task for scheduling I'll need one. I read the BOL pages, but I'm still confused as to what I need to set up.
Any info would be greatly appreciated.
Thanks,
Lee
"Security to network shares is dependent on the user who is running the package. If you are running it yourself, make sure you have permissions to the share. If you are running it from SQL Agent, you might need to use a proxy account (search for SQL Agent Proxy in Books Online) to access it."
I have some questions:
1. When I select "execute" what account is SQL Server using for permissions for accessing network folders? Is it mine, SQL Server Agent, something else? If it is my account, why would I get an "Access to the path is denied" error?
2. I don't understand how to use a proxy account, or even if I need one. I assume that when I set the task for scheduling I'll need one. I read the BOL pages, but I'm still confused as to what I need to set up.
Any info would be greatly appreciated.
Thanks,
Lee
ASKER
"The account being used is the one specified in the step within the configured job." - What step? Where did I set this (I don't remember doing anything).
"You do need a proxy and a credential. The credential is literally a user name password for a domain account. " - Would this be MY domain account, or is there some other account I should be using. If it was my account, wouldn't I have to change this everytime my account password changed?
having asked these questions, why would the task fail when I manually selected "execute"?
"You do need a proxy and a credential. The credential is literally a user name password for a domain account. " - Would this be MY domain account, or is there some other account I should be using. If it was my account, wouldn't I have to change this everytime my account password changed?
having asked these questions, why would the task fail when I manually selected "execute"?
To get to the drop down box for the account:
Bring up the properties of the SQL Job in question.
Click Steps in the top left panel.
Edit the step that runs your package.
In the job step properties the top three boxes read Step Name, Type and Run As.
The default is that Run As is set to SQL Agent Service Account, if you have any proxies they will also be in the drop down list, select the appropriate proxy.
When you manually say execute, it still runs as the SQL Agent account, not yours unless you change it, so it would still fail. Even calling it programmatically using msdb.dbo.sp_start_job will use the agent account, because it is the job that controls the running user, not the person calling the job.
You are right in that your account is not a great plan long term, for testing you can use your account but in production it is normal to have an AD account specifically for tasks such as this.
hth.
Bring up the properties of the SQL Job in question.
Click Steps in the top left panel.
Edit the step that runs your package.
In the job step properties the top three boxes read Step Name, Type and Run As.
The default is that Run As is set to SQL Agent Service Account, if you have any proxies they will also be in the drop down list, select the appropriate proxy.
When you manually say execute, it still runs as the SQL Agent account, not yours unless you change it, so it would still fail. Even calling it programmatically using msdb.dbo.sp_start_job will use the agent account, because it is the job that controls the running user, not the person calling the job.
You are right in that your account is not a great plan long term, for testing you can use your account but in production it is normal to have an AD account specifically for tasks such as this.
hth.
ASKER
Sorry to be so anal, where do I do the following at?
"Bring up the properties of the SQL Job in question.
Click Steps in the top left panel.
Edit the step that runs your package.
In the job step properties the top three boxes read Step Name, Type and Run As."
I can't seem to find this.
Thanks,
Lee
"Bring up the properties of the SQL Job in question.
Click Steps in the top left panel.
Edit the step that runs your package.
In the job step properties the top three boxes read Step Name, Type and Run As."
I can't seem to find this.
Thanks,
Lee
In SQL Management Studio, connect to your server.
Expand the treeview of the server, SQL Server Agent should be at the bottom.
Expand that and there should be a folder 'Jobs'
Expand that and there will be a list of SQL Jobs.
Find your job, right click on it and select properties
That will bring up the SQL Job properties.
hth
Expand the treeview of the server, SQL Server Agent should be at the bottom.
Expand that and there should be a folder 'Jobs'
Expand that and there will be a list of SQL Jobs.
Find your job, right click on it and select properties
That will bring up the SQL Job properties.
hth
ASKER
SQL Server agent is not displayed. I would guess that that service needs to be started? If it is needed to run the job, why woulld the task run at all?
Ah, I see you are using the Execute Package Utility to manually run the package (DTSExecUI) kicked off from Intergration services connection in SSMS. Executed that way you just need Integration Service running for it to execute. It will execute under the account that IS is installed to, probably Network Service. Outside of changing the service to run under the account you want, I'm not sure there is a command line option to change the user. The username / password options are for retrieving a package using a SQL login, not for making the package run under a different account.
Only way I've moved a SSIS package into a different AD account is through running it under a job, since any job step can be set to impersonate another user (given a credential / proxy). It also made it easier to manage running the job programatically / from stored procedures etc. If that is not available as an option to you, keep searching for a way to configure the dtexec, but I'm not sure it's doable.
Only way I've moved a SSIS package into a different AD account is through running it under a job, since any job step can be set to impersonate another user (given a credential / proxy). It also made it easier to manage running the job programatically / from stored procedures etc. If that is not available as an option to you, keep searching for a way to configure the dtexec, but I'm not sure it's doable.
ASKER
So based on what you said, running the Execute Package Utility, the package runs as the account that Integration Services is running as, not my login account. I would assume that that account is local to the server and is not a Domain Account, hence could not be given access to the share. Would you agree with my assumptions, or am I missing something?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all of the information. I have a good idea where I need to go to get this working.
Lee
Lee
"Only way I've moved a SSIS package into a different AD account is through running it under a job, since any job step can be set to impersonate another user (given a credential / proxy). "
Can you tell me how did you impersonate the file?
Can you tell me how did you impersonate the file?
You do need a proxy and a credential. The credential is literally a user name password for a domain account. The proxy then allows that credential to be used for specific types of activity (SSIS being one of them). Once both are set up, edit the job that runs your SSIS package, edit the step and where is specifies account to be run as in a drop down list, the proxy should appear.
hth.