asked on

Job execution error

Hi there,

I just created some SQL jobs in 2005 which points to a SSIS package. When I execute this job directly I am able to execute it just fine. However, when I execute it from the SQL Server Agent job I get this error:

Date            10/17/2007 3:01:33 PM
Log            SQL Server (Current - 10/17/2007 3:01:00 PM)

Source            Logon

Message
Login failed for user 'TEST'. [CLIENT: <local machine>]

This login has full access rights so I am not sure why it is keep failing? Any ideas?

Thanks

Marcel Hopman

When running a job under SQL Agent it runs with the credentials of the sql agent service account. You need to set up een SQL agent proxy account with the proper credentials.

sgaurav7

ASKER

Sorry forgot to mention....I have already done that. Just to be sure...I restarted the agent to make sure there are no issues with teh service account

Marcel Hopman

When you ran the job directly, where you logged in as 'TEST'. [CLIENT: <local machine>] ?

sgaurav7

ASKER

Yes, I am logged in as 'TEST'

sgaurav7

ASKER

When I run the package manually...it prompts for the password and after entering the password it works fine. Do you think that could be causing the issues here? How do I pass these credentials when running the job?

Thanks

Marcel Hopman

Then it could be a SSIS Issue.
If there is a Connection in the Connection Manager that does NOT use Windows Authentication and you enter a username and password you must set the ProtectionLevel (Package Properties) to EncryptSensitiveWithUserKey because the default (DontSaveSensitive) does not save the password when you save the SSIS package.

sgaurav7

ASKER

Where do I change this protection level to "EncryptSensitiveWithUserKey".....currently when I open the package it asks for this password when I go to the package's properties.....so where do I change this setting?

Marcel Hopman

When you opened the package and right click the Control Flow work area and select Properties from the pop-up menu you'll find ProtectionLevel property there.
(You'll also have to (re)enter any password in the connection manager before you save the package)

sgaurav7

ASKER

It is already set with : "EncryptSensitiveWithUserKey"

ASKER CERTIFIED SOLUTION

Marcel Hopman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial