SSIS & Sql Agent Proxy Account Woes

Question

Post reply

SSIS & Sql Agent Proxy Account Woes

Allen Kinsel

SSC Veteran

Points: 268
More actions
September 22, 2010 at 4:20 pm

#229615

Im trying to run a ssis package using a SQL agent proxy and getting an error...
for testing I created a package that reads from 1 txt file and writes to another, nothing special.
package is stored in SQL Server with the protection level set at rely on server storage for access control & Ive also tried setting it to not encrypt sensitive data (there is none)
I have a non priviliged user creating the job
they are in the SQLAgent User role as well as the db_ssisltduser role in msdb
they have permisions to an agent SSIS proxy account that has rights on the files (doesnt matter but...)
When running the job the following error is returned
Executed as user: XXXXPROXYUSERXXXX. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:13:54 PM Could not load package "\ALLEN HATES SSIS" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Only the owner of DTS Package 'ALLEN HATES SSIS' or a member of the sysadmin role may create new versions of it.). The SQL statement that was issued has failed. Source: Started: 5:13:54 PM Finished: 5:13:54 PM Elapsed: 0.047 seconds. The package could not be loaded. The step failed.
I can make it work by granting the SSIS Proxy account db_ssisoperator in msdb but, that defeats the purpose of having the proxy.
Environment SQL 2008/windows 2008R2 64 bit Cluster 10.0.2531

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sabotta SSC Eights! Points: 818 More actions · Answer 1

Have you assigned the db_ssisltduser role to the Reader role of the package?

subbu_e SSCrazy Points: 2431 More actions · Answer 2

file is under any shared location on the same machine. such as \\localhost\sharename

Regards,

Subbu

http://mssqlforum.wordpress.com

Regards,
Subbu
Click here to Get Speedy answer or solution

Allen Kinsel SSC Veteran Points: 268 More actions · Answer 3

I did assign the role db_ssisltduser as both reader and writer to no avail, of course I wouldnt expect that to be needed since it should be there by default

LadyRuna SSCrazy Points: 2312 More actions · Answer 4

I've seen that issue in my own environment. I believe it has to do with the fact that the creators of the package and the file don't match. Also when running the package, the user has to have appropriate rights to touch things in the file system. You may just have to adjust the Proxy, because as far as I know it's the only way to force it to work .

Andy Leonard SSChampion Points: 10134 More actions · Answer 5

Hi Allen,

I don't use proxies. I've never had any luck getting them to work as I thought they should. Here's what I do instead: I grant the SQL Server Agent service account permissions on the databases and in the file system and I execute SSIS from SQL Server Agent. It runs under the security context of the service account and accesses everything via that context. Administration (password changes, etc) is conducted by the network admins.

I'm curious: What is the use case for proxies over this approach? There must be one... I'm seeing questions about proxies more and more.

:{> Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

bundid_c Valued Member Points: 56 More actions · Answer 6

i have a problem like you but i can find solution like this

1 install SSIS in all nodes of cluster

2 import SSIS package in MSDB

3 logon window with SQLProxy user

4 create job specify in job step

-Package source--> SSIS package store

-Server--> .

-Log on to the server --> Use Window Authen (Default)

-Packge --> browse from MSDB

i hope it work for u

mnDBA SSCrazy Points: 2032 More actions · Answer 7

Andy Leonard (9/24/2010)
Hi Allen,
I don't use proxies. I've never had any luck getting them to work as I thought they should. Here's what I do instead: I grant the SQL Server Agent service account permissions on the databases and in the file system and I execute SSIS from SQL Server Agent. It runs under the security context of the service account and accesses everything via that context. Administration (password changes, etc) is conducted by the network admins.
I'm curious: What is the use case for proxies over this approach? There must be one... I'm seeing questions about proxies more and more.
:{> Andy

Hey Andy. Thought I would jump in here on this one.

A bit of background.... we are currently deploying an in house built data warehouse solution. I decided to have the SSIS packages (stored in MSDB) fire via scheduled jobs using a proxy account. The reason for it is security based. Microsoft will tell you that the service account for SQL Server Agent needs to be a Sysadmin. Even if you do not go that route (I personally have encountered issues when not), the agent service account has more permissions than what any of our SSIS packages need to run. I did not want our developers sticking anything in the packages that could be run with these elevated permissions.

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 8

I just ran across this article, which does a great job of breaking down and running you through how to troubleshoot.

Greg E

P Jones SSChampion Points: 12352 More actions · Answer 9

I use proxy accounts on all our servers. The agent account has access only to the local sql server program and data and local backup drives/directories and can thus only work in limited areas on its own server whilst the proxy account has access to other network servers.

Anything needing ssis or cmdexec e.g. copying the backup files or importing data from a network location, is run as the proxy account. All SSIS packages are stored on the servers and it all works fine.