SSAS(64 Bit) and Oracle Client Issues

  • Article

While trying to create an Analysis Service Data Source that connects to Oracle using ORAOLEDB Provider, you get an error –
TITLE: Connection Manager
Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified
BUTTONS:

Environment
Windows 2003 x64 Sp2
SQL Server 2005 x64 SP2 CU10

There can be 2 Scenerios:

64 Bit (Need to use Oracle Provider)
For 64 bit connectivity issues (failing from UDL or ODBC Data Sources or 64 bit AS Service) – - Install 64 Bit Oracle Client

32 Bit (Need to use Oracle Provider0
For 32 bit connectivity issue (from BIDS or SSMS) – the issue is related to an Oracle bug,
SQl 2005 64 Bit Analysis Services, Datasource is Pointing to Oracle
The networking layer is unable to parse program locations that contain parenthesis in the path or location where the executable starts from. The following bug has been filed with Oracle to correct this behavior:
Bug 3807408 https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=3807408  
 
CANNOT EXTERNALLY AUTHENTICATE USER WITH QUOTE IN USERNAME
But the role of the above issue comes into play after all other connectivity tests look OK - the above bug just contributes an error in Visual Studio or BIDS or any other application that has ‘)’ in the path
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159581&SiteID=1 
 
Open BIDS using a Short notation (C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe) and you will be able create a data source successfully.
Or
Copy program files (x86) (C:\Progra~2\Microsoft Visual Studio 8) to a new folder and rename it and then launch VS from there and try setting up Data source.

If you will get below mentioned error message after creating right datasource to Oracle Server:
<error>
Errors and Warnings from Response
 Internal error: The operation terminated unsuccessfully.
 Internal error: The operation terminated unsuccessfully.
 Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: ORA-01005: null password given; logon denied
.
 Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'test', Name of 'test'.
<\error>
In Such Situation  Provide Impersonation Information with specific User  & Password (domain login with Password)

Comments

  • Anonymous
    December 11, 2008
    Configuring Datapump in IIS 7 SSAS (64 Bit) and Oracle Client Issues Issue while Saving BLG File related