December 21, 2009 at 8:53 am
I posted the following to the MSDN forum and have gotten no useful replies. Any help appreciated and my Oracle OLEDB password capabilities are completely broken after attempting to configure them.
---
We recently started using XML configurations. Our packages use both SQL Server and Oracle. For Oracle, we use the Attunity drivers as source and destination components but also use OLEDB Oracle connection managers for Execute SQL Task (Control Flow) and OLEDB Command (Data Flow) components as needed. When we configured everything, all the connection managers, including the Attunity pieces, configured without issue. OLEDB Oracle, however did not work. No matter what we tried, there was no way to get the password configured. We tried all the suggestions about using only the connection string or the individual pieces, tried all the protection levels, etc. We also tried both the Microsoft and Oracle provided OLEDB components. When this all failed, we completely backed out configurations. This is the strange part: now, I can't save any passwords at all in OLEDB components. We've rolled back the entire code base and I've verified that the package XML files contain the encrypted password with the "persist" setting set to true. What we see is this: Bring up an OLEDB Oracle connection manager in Edit mode; password is empty; put in password; test connection; connection works; check the "save password" box; close connection manager edit box; open connection manager again. Password is empty. Now we can't run any flows since none of the Oracle OLEDB components can connect to our Oracle databases. If I create a new package, it also can't save the password for any new Oracle OLEDB connection manager I create. In short, trying to configure these, hosed our entire OLEDB Oracle SSIS environment on the machine we tried it on.
Any ideas? It seems that something must have gotten set outside of SSIS in the registry or something.
We're running SQL Server 2008 SSIS. Oracle is 10g clients and 10g/11g servers. One big note: I was running only 10g Oracle client software on this box. I installed 11g. Outside of SSIS, I've verified that I can switch between the two environments without issue. Inside SSIS, it *mostly* works but some SSIS connection managers (not all) complain that they can't find the 10g DLL when I change the environment to point to 11g. It seems odd to me that only some connection managers do this ... And, there is no change in the password behavior between the 10g and 11g instances.
---
Follow-ups: Yes, I did try putting the passwords in the XML files. Now I'm just trying to get any password use to work, as trying that broke ALL Oracle OLEDB passwords in all SSIS flows -- even old ones, even ones I took out of source control, etc. I've also since backed out the 11g client install and that had no effect.
As it stands now, with no configurations enabled other than parent variable configurations (we use child packages), I see the following behavior:
- Take an existing Oracle OLEDB connection manager or create a new one.
- Go to connection manager's edit mode.
- Add server, user name, password properties.
- Test connection -- tests okay.
- Check the "save password" box.
- Exit connection manager's edit mode.
- Bring same connection manager back up again.
- Password is gone. Also gone if we try running the package.
It doesn't matter whether we use the Microsoft or Oracle OLEDB Provider.
Big note: Trying configurations in one package broke this in ALL SSIS packages used on the machine.
So, basically, attempting to set up a configuration has broken all SSIS flows on the box we tried it on.
We've tried all the different ProtectionLevel settings -- no effect.
December 21, 2009 at 9:05 am
I think most of what you are saying is not clear because there is fixed defined way to connect to oracle and that is the TNSNAMES.ORA file which includes Oracle permission it needs to be in the folder with the Oracle client and no you don't need both 10g and 11g client you need just one. I have used Oracle client in D drive with TNSNAMES.ORA file to connect to both 10g and 9i so you need to make relevant changes and post again.
Kind regards,
Gift Peddie
December 21, 2009 at 9:16 am
There is no problem with TNSNNAMES.ORA. Whether I use 10g or 11g, my TNSNAMES file points to one central place and I can connect to all Oracle databases fine outside of SSIS. Inside SSIS, as I have noted, I can also connect but there is no facility for saving the password. The issue is JUST the password. We used to save the password with the package. We tried using XML configurations for these same passwords and that failed. Backing that completely out now ALL Oracle OLEDB passwords are never saved in SSIS nor do they work with configurations. I can see the password entries in the package XML files -- they just never get saved or pulled in by the component. So this is strictly and SSIS problem.
December 21, 2009 at 9:40 am
steves06 (12/21/2009)
There is no problem with TNSNNAMES.ORA. Whether I use 10g or 11g, my TNSNAMES file points to one central place and I can connect to all Oracle databases fine outside of SSIS. Inside SSIS, as I have noted, I can also connect but there is no facility for saving the password. The issue is JUST the password. We used to save the password with the package. We tried using XML configurations for these same passwords and that failed. Backing that completely out now ALL Oracle OLEDB passwords are never saved in SSIS nor do they work with configurations. I can see the password entries in the package XML files -- they just never get saved or pulled in by the component. So this is strictly and SSIS problem.
You are not understanding me when I use Oracle for all development all permissions needed to resolve Oracle connections are in the TNSNAMES.ORA file so I don't use password to connect to Oracle maybe you need to talk to your Oracle team why you need to manually add password to SSIS package.
Kind regards,
Gift Peddie
December 21, 2009 at 9:46 am
That's the policy here. I'm a consultant so I'm not going to question it. I shouldn't have to change policy to get around what appears to be an obvious, severe SSIS issue. It seems SSIS must be setting something outside the packages themselves (e.g., the registry) since we've reverted all the source code to the last working version and it's still broken.
December 21, 2009 at 9:51 am
[font="Comic Sans MS"]
Gift - what you have mentioned is correct. With advance configuration using external password store for Oracle this can be done (details below) - but this is not a common practise.
http://www.oracle-base.com/articles/10g/SecureExternalPasswordStore_10gR2.php
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 21, 2009 at 9:53 am
steves06 (12/21/2009)
That's the policy here. I'm a consultant so I'm not going to question it. I shouldn't have to change policy to get around what appears to be an obvious, severe SSIS issue. It seems SSIS must be setting something outside the packages themselves (e.g., the registry) since we've reverted all the source code to the last working version and it's still broken.
I just checked the BIDs connection object and when you go to advanced in SV2005 persist permission is false you could try changing that to true because everything you could do in the first page is there and you could copy it and save it. I have also seen other save the password to SQL Server table. I will look for some alternative solution for you because your problem is neither Oracle nor Microsoft.
Kind regards,
Gift Peddie
December 21, 2009 at 9:54 am
[font="Comic Sans MS"]
What steve has mentioned here - using Oracle password through configuration file - can be done. However - I am unable to identify the problem.
@steve-2 - are you getting any specific message from Oracle saying 'Unable to connect .. etc' with a suitable ORA-XXXX error somewhere?
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 21, 2009 at 10:23 am
I am setting persist (save password) to true. I see it true (1) in the package XML file. But it's never being saved. Either that, or it is being saved but it can't be decrypted when I open the package again. I tried setting the encrypt false and putting the password in the package XML file directly but that also didn't work.
As I noted, when editing the connection manager it connects fine when I try "Test Connection." But once I exit the password is gone. Attempts to run in that state result in the expected Oracle "null password" errors.
The really odd/concerning thing here is that simply trying configurations has broken ALL Oracle OLEDB components on the machine. That, and the fact, that reverting back all the code (saved package configurations, etc.) did nothing implies that trying configurations set something either outside of SSIS or at some SSIS global level that now affects all Oracle OLEDB connections within SSIS. Outside of SSIS I have no problems accessing Oracle.
December 21, 2009 at 11:08 am
I think you are missing EncryptSensitiveWithPassword option so check the thread below which also added the need for correct TNSNAMES.ORA file in the Oracle home folder. The second link is Microsoft article that gives reason running SSIS package as job fails.
http://support.microsoft.com/kb/918760
Kind regards,
Gift Peddie
December 21, 2009 at 11:19 am
No, we tried all those suggestions several times. And, regardless of what we tried, we reverted everything back to just using package level passwords but those no longer work. We should be able to revert back and have everything work.
December 21, 2009 at 11:30 am
steves06 (12/21/2009)
No, we tried all those suggestions several times. And, regardless of what we tried, we reverted everything back to just using package level passwords but those no longer work. We should be able to revert back and have everything work.
This may be related to the accounts used to run SSIS, your account and if using the Agent the Agent accounts running without the relevant permissions. The Agent account must be admin, job must be admin but can run as proxy and SSIS must be admin. The short answer is ETL with none Microsoft system is best run with high level permissions because if your package is using the file system within the Microsoft system admin permissions are required.
Kind regards,
Gift Peddie
December 21, 2009 at 11:40 am
I'm not exactly sure what you mean by all this ...
Our flows access SQL Server as well. And they access Oracle with the Attunity drivers. In all those cases there are no issues trying to configure and, when we disable configurations, revert back to pre-configuration code, etc. all works as it did before. If I edit the Attunity connection managers, the passwords are saved in the package as requested. These are in the same packages where the Oracle OLEDB components now refuse to save passwords. Configuration of Oracle OLEDB passwords never worked (hence the reason to revert back to package level settings).
The user account running these has admin privileges and nothing has changed there. The only thing that changed was us attempting to configure Oracle OLEDB passwords. That's it. Now, no Oracle OLEDB passwords can be used, in any fashion, by any of the SSIS packages on the machine. This includes packages we never tried to configure in any way. So whatever SSIS did, it did it systemically.
I just tried reinstalling the Oracle OLEDB client (Oracle ODAC package). That changed nothing. I'm now trying to see if OLEDB tracing on the Oracle side tells me anything. So far. nothing.
December 21, 2009 at 11:56 am
Our flows access SQL Server as well. And they access Oracle with the Attunity drivers. In all those cases there are no issues trying to configure and, when we disable configurations, revert back to pre-configuration code, etc. all works as it did before. If I edit the Attunity connection managers, the passwords are saved in the package as requested. These are in the same packages where the Oracle OLEDB components now refuse to save passwords. Configuration of Oracle OLEDB passwords never worked (hence the reason to revert back to package level settings).
I am telling you the above is not relevant to your problem, you have a problem that others are not having that is a problem local to your company so you need to print the two MSDN docs and make sure all are correct in your setup. And it includes the relevant permissions.
http://blogs.msdn.com/debarchan/archive/2009/04/13/ssis-an-oledb-error-has-occurred.aspx
Kind regards,
Gift Peddie
December 21, 2009 at 12:07 pm
Well, it's definitely not "our company" since the only place everything broke is on the one machine where we tried to use SSIS configurations. All the other SSIS flows, including the ones broken on the one machine, work elsewhere. So, yes, maybe it's something about the Oracle configuration on this one machine but the TNS points to a global one -- the same one everyone else is using. If it is something broken just on this one machine it appears to be limited only to SSIS -- all other Oracle access on that machine works fine. It seems more than coincidental that only SSIS is broken and it all broke after trying to use SSIS configurations.
I'll check the Oracle settings again I guess.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply