September 22, 2010 at 4:20 pm
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
September 22, 2010 at 4:34 pm
Have you assigned the db_ssisltduser role to the Reader role of the package?
September 23, 2010 at 12:50 am
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
September 23, 2010 at 3:41 pm
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
September 23, 2010 at 3:52 pm
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 .
September 24, 2010 at 7:12 am
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
October 9, 2010 at 3:53 am
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
October 10, 2010 at 7:13 pm
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.
October 11, 2010 at 6:58 am
October 11, 2010 at 8:01 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply