Job failed when running in SQL server agent

  • I tried to run a SSIS package as a step in sql management studio.

    But failed with :

    05.45 Code: 0xC0202009 Source: myDataPump Connection manager "oracle" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E4D Description: "ORA-01017: invalid username/password; logon denied ". End Error Error: 2011-08-16 08:23:05.45 Code: 0xC020801C Source: df addr chg log dfSrc Oracle [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCON... The package execution fa... The step failed.

    If I run the pacakge on the same server in BID there is no problem.

    But when running it SQL agent, I got above error.

    I'm the sysadmin on the server.

    The package have a protect level of EncryptSensitiveWithPassword, is it because of this?

    If so, how can I make it work in SQL agent?

    Thanks

    Thanks

  • What credentials is the job running under? Not your credentials (those are what are used when you run from BIDS), but the job itself, SQL Agent as well. Do those have the necessary permissions?

    If you want to try using a different protection format for the package, try deploying it with "Use server roles for security" (or whatever the actual wording is for that).

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I find running the things from a script instead of the agent is a much more reliable way of getting the Encrypt things to work. Try this instead:

    EXEC xp_cmdshell 'dtexec /FILE "[FILENAME]" /DECRYPT [PASSWORD] /MAXCONCURRENT " -1 " /CHECKPOINTING OFF'

    Note that you'll need to have xp_cmdshell enabled for this to work. To enable that, run this script:

    -- To allow advanced options to be changed.

    EXEC sp_configure 'show advanced options', 1

    GO

    -- To update the currently configured value for advanced options.

    RECONFIGURE

    GO

    -- To enable the feature.

    EXEC sp_configure 'xp_cmdshell', 1

    GO

    -- To update the currently configured value for this feature.

    RECONFIGURE

    GO

    If you don't want to use this approach, then you could try to instead, when you create your job in SQL Server Agent, click on the command line tab, and ensure that the script which is running has the DECRYPT line in it.

  • As a caveat, if you enable xp_cmdshell, make sure you understand all the security implications of it. All of them. It's a good way to end up with your server, or even your whole network, hacked, if you do it wrong.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Yeah I really should remember to preface all suggestions that I post which involve xp_cmdshell with a comment like that. Sometimes I forget that the databases which I work with tend to be internal-only :/

Viewing 5 posts - 1 through 5 (of 5 total)

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