sql server dts: We recently upgrade from SQL 2000 to 2005 and everything works great. But I
have a problem which I know due of lack of knowledge.



When I create a SSIS package with "SQL server business intelligence
developer studio" and then import it to "Stored packaged" and schedule it,
then it fails!! Even though it works fine when I run the package manually!
Here is the catch: my SQL agent runs under a domain admin account. If I
login with that account to server and create the SSIS package and schedule
it, it works. But if I log in with my account which also is a domain admin
it doesn't work. I believe I have a permission issue which I don't know how
to fix,



Any help will be appreciate



Thanks

Hooman

Hi Hooman,

Sorry, it's no longer as easy as that. Microsoft has made it more fine tuned
(and perhaps more complicated as a result) to avoid exactly the kind of
problem you are reporting. Here is what you now have to do - this is the only
supported method of creating SQL Server Agent jobs to execute SSIS packages:

If you want a login to be able to create SQL Server Agent jobs to run SSIS
packages, you must do the following (SSMS means SQL Server Management Studio):

1) Make sure the user has a login account to SQL Server (obvious!)
2) create a user account for the user in the msdb database (this may not be
so obvious!)
3) assign the msdb user account to at least the SQLAgentUserRole and the
db_ltduserrole. The SQLAgentUserRole confers permissions to create a job, and
the db_ltduserrole enables the user to access packages saved in msdb.
4) Create a new credential and give it the identity of a Windows account
that has the access rights you need (SSMS Security node, Credentials node,
right-click then New Credential). Notice that it is this Windows account that
needs the permissions - see below!
5) Create a proxy account with the credential you created in the step above.
Make sure the proxy is given access to the SSIS Package subsystem (Expand SQL
Server Agent node in SSMS, then Proxies, right-click New Proxy)
6) Assign the proxy account to the login (Proxy properties, Principals screen)
7) When the login creates a job, he/she should use the Run As drop-down to
run a job step as the proxy. This is a new feature in SQL Server 2005 that
enables job steps to be run in a specified security context.

Charles Kangai, MCT, MCDBA

Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm





[quoted text, click to view]