Hello,I try to run a job from SQL Server Agent to execute an SSIS package, but it always fails. However, if I execute the SSIS package directly, it will succeed. The SQL Agent Job History shows "Unable to start execution of step 1 (reason: Error authenticating proxy DB1\DB1_CREDENTIAL, system error: Logon failure: unknown user name or bad password.). The step failed."P.S. DB1 is my server name, DB1_CREDENTIAL is the account creating the credentialThe SQL Server Agent Error Log shows "[298] SQLServer Error: 22046, Impersonation error. [SQLSTATE 42000]"I've created an account on OS and assigned it to a credential. Then, I created a proxy with the credential and then created the SQL Server Agent job with that proxy.P.S. My SSIS package protection level is created as "Rely on server storage and roles for access control", because if it is set as "Do not save any sensitive data", package execution will fail even I run it directly.How can I successfully run SSIS package from SQL Server Agent?Do I lose any setting steps?Thanks for help.Lilia

HI Check this http://support.microsoft.com/kb/918760

Thanks, PK_SSIS.But I've read this article before.This problem occurs when one of the following conditions is true: The user account that is used to run the package under SQL Server Agent differs from the original package author. The user account does not have the required permissions to make connections or to access resources outside the SSIS package.I run the package with DB1_CREDENTIAL and create it with DB1_CREDENTIAL, too.The user account DB1_CREDENTIAL have enough permission to make connections and access resources outside the SSIS package.I have set PROXY. I have set the ProtectionLevel to ServerStorage................I cannot find anything else I can to according to this article.Do you (or anybody with experiences about this) have any ideas about what else I should check/do now?Lilia

Hi Your execution is failing because of reasons mentioned in Method 1 of KB, SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account Regards

ignore my last past as user key is not invloved,..,.

Dear PK_SSIS, Thanks for reply. But I still do not understand.I use the same computer (local), what does "current computer" and another computer" mean?--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------If anybody has experiences on this problem and is willing to watch my problem........My setting steps are:1. create OS account ServerAgent, join Administrator group2. use SQL Server Configuration Manager, change SQL Server Agent to start by "ServerAgent" account, restart SQL Server Agent.3. create OS account DB1_CREDENTIAL4. create credential in SQL2005, named SSIS_CREDENTIAL, using OS account DB1_CREDENTIAL as identity.5. add OS account DB1_CREDENTIAL as SQL server login account. grant msdb SQLAgentUserRole, db_dtsltduser role and other resources read/write permission.6. create proxy SSIS_PROXY with credential name SSIS_CREDENTIAL, subsystem ActiveX Script, SQL Server Intergration Services Package, CmdExec. And add proxy account principals DB1_CREDENTIAL(strange, it will not apear in the list when I re-check the principals) and SQLAgentUserRole.7. use Import and export Wizard to import a db table from local database server to another db table and select save SSIS package / SQL server. package protection level "Rely on Server storage and roles for access control" ==> create a package "New Package".8. try to execute the package by DB1_CREDENTIAL ==> success!9. create a new job in SQL Server Agent named "test". owner DB1_CREDENTIAL. New step "ste1" type "SQL Server Intergration Services Package" run as "SSIS_PROXY". Package source "SSIS Package Store", Package "\MSDB\New Package".10. try to run the job by DB1_CREDENTIAL, fails. Error message: Unable to start execution of step 1 (reason: Error authenticating proxy DB1\DB1_CREDENTIAL, system error: Logon failure: unknown user name or bad password.). The step failed.But if I change the job step "ste1", set run as "SQL Agent Service Account", it will end up successfully.However, since I plan to let other user that do not have permission to use OS account (can only use SQL Authentication) use SQL Server Agent to execute the SSIS create by themselves, using "SQL Agent Service Account" is obviously impossible.What's wrong on my SQL Server Agent?How can I use SQL Server Agent to run SSIS package correctly?Thanks for any help.by Poor Lilia (have tried to solve this problem for a whole day....)P.S. I am using SQL Server 2005 Standard Edition 64bit

""" I've created an account on OS and assigned it to a credential. Then, I created a proxy with the credential and then created the SQL Server Agent job with that proxy."" Hi Please check whether your Administrator account have access to MSDB ? as DB1_CREDENTIAL is created from it.

Hi Yes.I am not sure whether the whole Administrators group has access to MSDB, but administrator and DB1_CREDENTIAL do have access to MSDB (as least read/write is ok)Lilia

Hi try to execute the package by DB1_CREDENTIAL ==> success! .It is still not running as agent job,. grant msdb SQLAgentUserRole, db_dtsltduser role and other resources read/write permission. Packages can run via sql server agent can only be performed by following people db_dtsadmin ,sysadmin and db_dtsoperator . db_dtsltduser role is no sufficient here. Try db_dtsoperator role if you want avoid db_dtsadmin and sysadmin

Thanks, PK_SSIS. After fixing this error, now I have granted db_dtsoperator,db_dtsltduser,SQLAgentUserRole,and other resources read/write permission to DB1_CREDENTIAL.But the job "test" still fail with the same error message.I think there must be still something wrong.......

Hi We can either use built in SSIS roles or database roles in MSDB but not both.Make sure there is no overlap. I think SQLAgentUserRole is overidding db_dtsoperator,db_dtsltduser roles. SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles and Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. Try using SQLAgentOperatorRole. Regards

Hi,Now DB1_CREDENTIAL has been granted "db_dtsadmin", "SQLAgentOperatorRole", and other resources read/write permission needed.But it still fails with the same error message.......Lilia

Lilia,Read through this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=236423&SiteID=1 I don't believe your problem has anything to do with SSIS, rather it has everything to do with account authentication on the agent side.

I'd agree with checking on the agent authentication...

Hello,Thanks for all your help. Now it works!! according to http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=236423&SiteID=1reference http://www.atrevido.net/blog/PermaLink.aspx?guid=fec7f456-d259-4221-bc08-2fcdb3ac4517 use SQL Server Configuration Manager, change SQL Server Agent to start by "Local System" built in account, restart SQL Server Agent. create OS account DB1_CREDENTIAL create credential in SQL2005, named SSIS_CREDENTIAL, using OS account DB1_CREDENTIAL as identity. add pubuser as SQL server login account. grant msdb SQLAgentUserRole, db_dtsltduser role and other resources read/write permission. create proxy SSIS_PROXY with credential name SSIS_CREDENTIAL, subsystem ActiveX Script, SQL Server Intergration Services Package. And add proxy account principals pubuser. pubuser use Import and export Wizard to import a db table from local database server to another db table and select save SSIS package / SQL server. package protection level "Rely on Server storage and roles for access control" ==> create a package "New Package" owned by pubuser. pubuser create a new job in SQL Server Agent named "test". owner pubuser. New step "1" type "SQL Server Intergration Services Package" run as "SSIS_PROXY". Package source "SQL Server", Package "\MSDB\New Package". try to run the job by pubuser, success!!

Hi, I got lost on step 6. What do you mean by "pubuser use Import and export Wizard to import db table from local database server to another db table"?Someone should make a beautiful step by step with print screens manual about how to put a bloody SSIS task running with SQL Server agent.Thanks. =)

Hello, I had a similar problem, I was always getting the following errors when trying to run the SSIS package: In SQL Agent Job history: Unable to start execution of step 1 (reason: Error authenticating proxy DOMAIN\username, system error: ConnGetProxyPassword). The step failed. In SQL Agent log file: ! [298] SQLServer Error: 22046, Impersonation error. [SQLSTATE 42000] I've spent hours searching the web and trying all the solutions proposed (i.e. Delegation settings for the user, etc...), but none of them was working. It's also interesting that this problem occured only when SQL Agent service was runnig under some defined user (not under Locat System). Finally I got it working by following these steps: 1) in Management Studio, right click on your server, choose Properties 2) go to "Security" tab 3) check "Enable server proxy account" and enter "some" account - I've entered the same account which I also use as a credential for the proxy and as an SQL Agent service account, but it may also work with a different account 4) restart the whole server - it may be enough just only to restart the SQL server, but you never know :) Hope it helps... Regards Petr