Post reply

SQL Agent will not allow SQL login connections

  • February 6, 2006 at 6:55 am

    #618933

    Hi Gillaume & Tony,

    The solution is in three relatively simple parts.

    1. The solution seems to be to create a "Credential" under the "Security" node in SSMS for an existing account that has the appropriate rights to run the job.
    2. Then in SQL Agent, under "Proxies" then "SSIS Package Execution" create a new "Proxy".
    3. Now you need to go into your job and instead of running under the "SQL Server Agent" account you can choose your proxy. Now it should run! If you search for help on "Proxies" you can find more info (but not much more ).

    Hope this helps!

    Regards

    Chris

  • February 6, 2006 at 7:08 am

    #618934

    The following outlines how I successfully got SQL Agent to run my SSIS packages. I first address the my failing process, then the process that worked for me. I am not claiming to have the best solution, but it worked for me.

    I was unsuccessful at getting a SSIS package to run from the SQL Agent when I stored the SSIS package in file format within Integration Services using the Import Package wizard. I was using the "EncryptSensitiveWithUserKey" Protection Level setting for the package in BIDS. From what I understand, this is supposed to only work if the "CreatiorName" in the package properties in BIDS is also the login used by SQL Agent. When I tried the matching logins, the SQL Agent job still would not run the package. I believe there is a bug in SQL 2005 for this.

    I was successful, however, in running my SSIS packages from SQL Agent if I saved them to SQL from within BIDS. There is a trick to making this happen, and I will outline the steps I took to make it work.

    1. The default Protection Level setting for a SSIS package in BIDS is "EncryptSensitiveWithUserKey". This will allow for saving the package and running it in BIDS, so I maintained this setting while developing the package in BIDS.

    2. When completed with creating and saving the package in BIDS, change the Protection Level in the package properties to "ServerStorage". The package will not be able to be saved in BIDS with this Protection Level setting. (this is a temporary setting, only for the purpose of saving the package to Integration Services, there is no intent to save the package to BIDS with this setting)

    3. Do a mouse click within the Control Flow package graphic area to set it as the in-focus window. This is necessary, otherwise the option selected in the next step will not appear as an option.

    4. Select "File" then "Save Copy of ", where is the name of the package. (do not select "Save ").

    5. In the "Save Copy of Package" window, select "SQL Server" for the Package Location. After entering the server and security settings, select the instance name and enter a name for the package to be saved in the browse for Package Path. Select OK to save.

    6. The package should now appear in Integration Services, after a refresh, under Stored Packages / MSDB / .

    7. A SQL Agent job can now be created for the package, and the SQL Agent job should run the package without issue.

    I hope this is helpful.

    Gary

  • February 6, 2006 at 8:30 am

    #618992

    Chris,

    Tried your solution - still no joy. I tried qpestka's solution - WOO HOO! Yup it worked.

    A warning though guys - I don't know if it was one solution or a combination of them both, but my jobs are working now.

    Thanks guys.

  • March 1, 2006 at 10:57 am

    #624061

    gpestka you rock!  I've been struggling with this for hours and fighting the system admin thinking it was a rights issue and your solution worked like a charm!

    THANKS!!!!

  • June 2, 2006 at 7:34 am

    #641420

    Ref gpestka you rock!

     

    I second that ! Thanks

  • June 2, 2006 at 7:38 am

    #641422

    You can also set it to deploy to the MSDB of the server. this is the only way I found - other than to change the security settings of the package, but you REALLY don't want to go there

    "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein

  • October 10, 2006 at 9:34 am

    #665035

    I have used the manifest file to deploy the package on SS-2k5. I tried first from the client box where I created the project. During deployment I got error"Login failed for user 'sa'".

     Then I copied the manifest file to the server, tried to deploy from there. Same error. I can login as 'sa' both from client and a server. What can it be?

  • October 10, 2006 at 10:18 am

    #665049

    Gary,

    I have followed steps you have outlined well, until actually running package under Agent. The error is nondesriptive "The package execution failed. The step failed."

    Same is executed ion BIDS OK. Login in the package in Agent step is 'sa'. JUst in case I tried to run under Win Authentication - same error.

    Appreciate possible ques.

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply