August 16, 2011 at 9:34 am
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
August 16, 2011 at 9:42 am
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
August 16, 2011 at 12:02 pm
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.
August 16, 2011 at 12:14 pm
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
August 16, 2011 at 12:15 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy