March 31, 2009 at 4:05 pm
I have an SSAS Project where the Data Source is an Oracle 10g database and OLAP is running on a 64-bit server. I have installed and tried the Oracle 10g drivers but found that to be a miserable experience (the drivers fail because of the '(' in the path to the executable for SSMS and BIDS, gotta love Oracle for caring about their customers and wanting to fix that little gotcha!!!), I have installed the ODAC 11g drivers (Oracle Data Access Components), and can connect to my Oracle server with the 'Test Connection' button in the DS configuration, but when I try to process a Dimension, I get connection errors - "Unknown User Name or Bad Password", even though Im using the same User / Pswd..
Anyone have any ideas on how to fix this?
Details:
Windows Server: Windows Server 2003 Ent, 64 bit, SP2
SSAS: SQL 2005, SP2
BIDS: VS 2005 (32 bit)
SSMS: SQL 2005 (32 bit)
Oracle Database: 10g
Oracle Drivers: ODAC 11g
And before anyone asks, BIDS and SSMS don't come in 64-bit versions!
April 1, 2009 at 4:11 am
You will simply need to make sure you have the 64bit Oracle drivers installed on the server and on your workstation where you are doing your development the 32bit Oracle drivers. As you stated BIDS (Visual Studio) is only a 32bit application. Same thing in regards to driver setup if you are using SSIS (64bit on server and 32bit on workstation).
You the option to save the password and you shouldn't have any issues. Currently using Oracle 11g with SSAS 2008 on Windows Server 2008 with no issues (upgraded from SSAS 2005 and Windows Server 2003).
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
September 23, 2009 at 3:15 pm
What I found is install Oracle 64bit & 32bit both clients in the 64bit system.
That way you can design & test on BIDS (which is using 32bit OraOLEDB) and run it on SQL Server services (which is using 64bit OLEDB).
You can find more in http://knol.google.com/k/jeyong-park/accessing-oracle-data-source-from-64bit
September 24, 2009 at 2:46 am
I had problems with 64 bit oracle drivers before after lots of messing around, I ended up requesting a new box to act as a 32 to 64 bridge!
Mind you I was using SSIS and not connecting up directly from SSAS to oracle. Your very brave!
September 30, 2009 at 6:57 am
Hi,
I am facing the same problem when I try to process a dimension. But I am using Oracle 10g client. I have installed both 32bit and 64bit Oracle 10g clients on the server. After developing the SSAS project (on the server itself), I have deployed it on to the SSAS server without processing using Deployment wizard. Then I have created an SSIS package to process the dimension in that SSAS database. Then an SQL Agent job to execute this SSIS package. But, when I run this job, I am still getting the same "Logon failure: unknown user name or bad password" error.
Here are the details:
Windows Server 2008 64 bit
SSDS 2008 64 bit, SSIS 2008 64 bit and SSAS 2008 64 bit
SSMS 2008 32 bit and BIDS 2008 32 bit
Do I need to install Oracle 11g client to resolve this or can I do something else to resolve this issue using Oracle 10g client itself?
Any help on this is highly appreciated.
September 30, 2009 at 7:18 am
What is the Protection Level set to on the SSIS package? I would make sure that you have not left it at the default and that you setup the connection manager information appropriately.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
September 30, 2009 at 8:58 am
I have configured the connection manager properly. The Protection level of the SSIS package was the default option (EncryptSensitiveWithUserKey). I have now checked with DontSaveSensitive and then EncryptAllWithPassword options (I have rebuilt the SSIS package project and reimported the SSIS package to MSDB). But no luck and the same error. Can you please let me know which option I need to use and any other alternatives?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply