?
Solved

SSIS "Access to the path is denied" error?

Posted on 2008-02-11
11
Medium Priority
?
12,689 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:lapoliko
  • 5
  • 5
11 Comments
 
LVL 8

Expert Comment

by:drydenhogg
ID: 20866608
The account being used is the one specified in the step within the configured job. Unless you specifically changed this is will be the account that the SQL Agent is running under. It will not be your account.

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.
0
 

Author Comment

by:lapoliko
ID: 20866668
"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"?
0
 
LVL 8

Expert Comment

by:drydenhogg
ID: 20866734
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:lapoliko
ID: 20867276
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
0
 
LVL 8

Expert Comment

by:drydenhogg
ID: 20867449
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
0
 

Author Comment

by:lapoliko
ID: 20867469
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?
0
 
LVL 8

Expert Comment

by:drydenhogg
ID: 20867673
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.





0
 

Author Comment

by:lapoliko
ID: 20867867
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?
0
 
LVL 8

Accepted Solution

by:
drydenhogg earned 500 total points
ID: 20867924
You are correct, you can use the SQL Server Configuration manager to check what the services are being run as (or use the services in control panel etc). Integration services is most likely to be set up as a local account to the box, Network Service is a common one to be set to. I wouldn't look at altering it, since that alters it for everything run in that fashion, so not ideal. A network service account can be granted access to a resource, in that you grant that server the resource, but it is less than satisfactory from a security perspective.

When we have put these into production we run them via the SQL Agent, so we get control over the running user and use a proper AD account, making life a lot easier in controlling permissions, as well as being granular to each package, allowing different users per package.
0
 

Author Closing Comment

by:lapoliko
ID: 31429791
Thanks for all of the information. I have a good idea where I need to go to get this working.

Lee
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 21200295
"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?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question