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
lapolikoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

drydenhoggCommented:
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
lapolikoAuthor Commented:
"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
drydenhoggCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

lapolikoAuthor Commented:
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
drydenhoggCommented:
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
lapolikoAuthor Commented:
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
drydenhoggCommented:
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
lapolikoAuthor Commented:
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
drydenhoggCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lapolikoAuthor Commented:
Thanks for all of the information. I have a good idea where I need to go to get this working.

Lee
0
surajgupthaCommented:
"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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.