• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

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
0
sgaurav7
Asked:
sgaurav7
  • 5
  • 5
1 Solution
 
Marcel HopmanCommented:
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.
0
 
sgaurav7Author Commented:
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
0
 
Marcel HopmanCommented:
When you ran the job directly, where you logged in as 'TEST'. [CLIENT: <local machine>] ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sgaurav7Author Commented:
Yes, I am logged in as 'TEST'
0
 
sgaurav7Author Commented:
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
0
 
Marcel HopmanCommented:
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.
0
 
sgaurav7Author Commented:
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?
0
 
Marcel HopmanCommented:
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)
0
 
sgaurav7Author Commented:
It is already set with : "EncryptSensitiveWithUserKey"
0
 
Marcel HopmanCommented:
So when you open the package in BIDS it asks you for a password? Or is it a connection password before it starts to validate the package?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now