May 9, 2011 at 9:48 am
Hey all,
At a bit of a loss here. I setup a connection for Oracle in SSIS using Microsoft OLE DB Provider for Oracle (using the 11g client install). When testing the connection on the connection manager, it succeeds. When building/parsing the query within a data flow step, it works fine, I can even preview the data without issue. I also have other processes (DTS/VB) connecting to the same Oracle connection with the same username/password and they work without issue.
The problem is when I attempt to run this particular SSIS package interactively and/or at a job step level. It fails with:
Information: 0x4004300A at Data Flow Component, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at SSISnameRemoved, Connection manager "Oracle": 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
".
Error: 0xC020801C at Data Flow Component, Oracle Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Oracle" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Component, SSIS.Pipeline: component "Oracle Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Component, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Component: There were errors during task validation.
SSIS package "SSISnameRemoved.dtsx" finished: Failure.
The program '[6724] SSISnameRemoved.dtsx: DTS' has exited with code 0 (0x0).
Any help would be most appreciated.
Thank you
May 9, 2011 at 10:36 am
A couple of things:
1:) If you're using Microsoft OLE DB Provider for Oracle, you're not using the OLEDB driver from the 11g Client Install - that will be called something like "Oracle in Oracle_Home". You're using the stock MS driver.
2:) The error's telling you the username/password is invalid. How are you providing it? If you're inputting it into the Connection Dialog box and ticking save password, you must have the package ProtectionLevel set to one of the encrypted settings rather than DontSaveSensitive. If you've set DontSaveSensitive, you must provide the username/password in a config file or through a custom method
May 9, 2011 at 11:19 am
Thanks Howard.
Yeah I just realized that a big ago that in regards to your first comment.
You were correct, it was set to DontSaveSensitive. Changed and appears to be operable.
Thank you much!
May 19, 2011 at 5:22 pm
I'm getting a similar error, but with different circumstances. My team has a group account set up with the Oracle owners, and all of us can use the name/password to see the data using Oracle's TOAD interface tool. In addition, all of us can set up an SSIS package that grabs data from the Oracle server using the same login information... HOWEVER... if one of us creates the package, no-one else can run it. Only the person that creates the package can run the package. What's even more baffling, we have another connection to another Oracle server with another group account and that one works fine for everyone no matter who creates it.
Any thoughts?
May 20, 2011 at 6:29 am
thomas_emerson (5/19/2011)
I'm getting a similar error, but with different circumstances. My team has a group account set up with the Oracle owners, and all of us can use the name/password to see the data using Oracle's TOAD interface tool. In addition, all of us can set up an SSIS package that grabs data from the Oracle server using the same login information... HOWEVER... if one of us creates the package, no-one else can run it. Only the person that creates the package can run the package. What's even more baffling, we have another connection to another Oracle server with another group account and that one works fine for everyone no matter who creates it.Any thoughts?
Two thoughts.
1- Starting on Ora11g - by default - credentials are case sensitive so, be sure UserId and Password are entered exactly as created.
2- Package exec issue is privileges related. You may have different privileges setups in different instances, that's why it works on some, does not works on others. Be sure the account that is supposed to execute the package has exec privilege granted at account level.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 20, 2011 at 9:11 am
Paul, I really really appreciate your help here...
When you say "2- Package exec issue is privileges related. You may have different privileges setups in different instances, that's why it works on some, does not works on others. Be sure the account that is supposed to execute the package has exec privilege granted at account level."
...do you mean on their side (the Oracle side), or our side (some setting in the SSIS packages)? I'm sorry if the question seems stupid, we are an analyst group that reports up through business, not on the technology side, so we know enough about SSIS to be dangerous, but we really aren't IT. Is there anything else we should know about this?
Thanks again for your response.
Thomas
June 17, 2011 at 7:31 am
Thomas --
Let's start by fixing "ORA-01017: invalid username/password; logon denied" issue.
Take into consideration default credetials setting on Ora11g calls for case sensitive UserId and Password so, validate the righ "casing" of both UserId and Password by connecting to the Oracle database via SQLPlus.
Once you have figured out the right case of both UserId and Password be sure those are the ones provided by SQL Server during connection to Oracle.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 29, 2011 at 5:50 pm
Oracle's usernames and passwords are case insensitive, btw.
June 30, 2011 at 1:55 am
Bruce W Cassidy (6/29/2011)
Oracle's usernames and passwords are case insensitive, btw.
eh? Usernames are, passwords are not as with most implementations
June 30, 2011 at 3:20 am
Admittedly I haven't played with anything post 10g, but in my 15 years of Oracle DBA work, I haven't met a version of Oracle with a case sensitive password. But you know, I haven't actually tried varying the case in an Oracle password in a while, so perhaps they've changed it. I suspect if it's hooked up to Kerberos/AD or the like, then it would be a different matter.
June 30, 2011 at 4:48 am
Bruce W Cassidy (6/29/2011)
Oracle's usernames and passwords are case insensitive, btw.
That's not true. Ora11g credentials are case sensitive by default.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 30, 2011 at 1:40 pm
Thanks for replying Paul... I know for sure the cases are correct. I've checked and double-checked... and I've pasted in the password that the Oracle group gave me.
Let me repeat the issue: I can create an SSIS transfer from the Oracle database that works whenever I run it. My colleague can create a package that works whenever he runs it. Both of us are using the exact same login name and password. But, if I try to run his package, or he tries to run mine, it doesn't work. Somehow, the Oracle server is comparing not only our group login name and password, but also, I'm guessing, my Windows login to know that I created the connection. So, what should be a two part security (name and password), is actually a three part security (name, password, and Windows ID). It might also be important to note that we save our packages on shared corporate folders ("file system"), not on a SQL server. I've looked at everything that I can think of, this has to be on the Oracle side. We have a completely different connection to a totally different Oracle source that does not have this problem.
The Oracle group has not been helpful to us at all so we ended up coming up with a kloogy work around: Basically, the data transfer from Oracle is just the first part of a longer process, so, everyone in my group has created their own data tranfer module. I've chained these up with "on completion" so, whoever runs the package - the module they created works, the others fail, and the system continues. It's not ideal, but it works...
Thanks again for all ideas that you have provided. I really appreciate it.
September 9, 2011 at 1:26 am
Starting from 11g passwords in Oracle could be case sensitive.
September 9, 2011 at 6:43 am
kiro1234 (9/9/2011)
Starting from 11g passwords in Oracle could be case sensitive.
Actually, they are case sensitive by default - as previously posted.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply