Solution: Can edit, but can’t process, an Analysis Server Cube using ORACLE data

  • Article

 

Recently I was developing a SQL Server Analysis Server cube using Oracle as a data source. Weirdly the the development environment (BI Dev Studio) could see the Oracle schema & let me explore the data. But when I tried to process the cube or one of its dimensions, it died indicating that I couldn’t connect to the Oracle Server. Which clearly I could do as I’d been building & running SQL Reports from the same project & could see the data in the development tool.

If you are experiencing any of the following errors this post may help.

  • Bad Username / Bad Password
  • ORA-01017: Invalid username/password; logon denied.
  • ORA-12154: TNS could not resolve the connect identifier specified.

While trying to resolve this issue I read a whole lot of misleading information on Internet forums. These misguided “solutions” distracted me. I hope this post will make it easier for you.

In Short:

You develop the cube in the Business Intelligence Development Studio (BIDS) tool. It is 32 bit process that connects to both Analysis Server & your Oracle Data source. (Purple arrow) It uses the connection information you defined in the Data Source object you added to the Project.

BIDS connects with Your User Credentials to Oracle & SSAS

When you process the cube or a Dimension, BIDS passes the relevant definitions to SQL Server Analysis Server (SSAS). (Purple arrow) The SSAS attempts to connect to the Oracle database to process the data. (Blue Arrow)
 SSAS connects to Oracle with the credentials of its Service Account

It makes no difference if BIDS is running on the same machine as SSAS. It is designed to work in any of the topologies shown above. That way if SSAS is on a dedicated Server it can run with the full power of a server. That means SSAS doesn’t use the same credentials as the BIDS process.

SSAS does use the same information in the data source to connect to Oracle as you are using from BIDS but runs them under the credentials of the Windows Account used to start the service. Commonly the Service is started as either “NT AUTHORITY\NetworkService”, "Local System” or “Local Service”. It is highly likely that this account will not have sufficient permission to connect thru your domain to the Oracle Server. Especially in a heterogeneous environment of Linx / Unix / Windows, with a variety of Authentication systems etc.

It is possible that the Account you are using to run SSAS doesn’t have permission to read the Oracle directories containing the TNSNAMES.ORA file(s) which the Oracle Client interface needs to connect to your Oracle database. (test this by temporarily putting the SSAS Account into the local Administrators group)

It is possible that you have the wrong “Bitness” Oracle drivers installed. BIDS is a 32 bit application, on 64 bit windows it will be running in a 32 bit shell. But your SSAS system may be full 64 bit. You may need both 32 bit & 64 bit Oracle drivers installed & configured. (test this by installing a 64 bit Oracle client & seeing that it works correctly)

 

Trouble shooting technique. 

Test 1: If you are running BIDS & SSAS on the same machine and BIDS is connecting to Oracle OK.

  • Try changing the SSAS Service Logon account to the account you used to logon to Windows. (see steps below). If it lets you process the cube, problem solved.
  • Now go create a dedicated account on the Windows Domain for your OLAP service & use it. (otherwise when you change your password, OLAP server will not start, as you will likely forget to change it at the same time.  :-) ). 

Test 2: If SSAS is on a different machine.

  • As above, check the Service Account, & possibly change it to an account you know can connect over the network to Oracle.

  • Also check that you can connect from that server to Oracle. The easiest way is to install an Oracle client tool, eg: Oracle Enterprise Manager, TOAD for Oracle, or similar.

Bum steer

While trawling thru the net, looking for Oracle insight I stumbled on this post. It seemed insightful. It proved to be total garbage. Analysis Service does not make connections by doing something funky with HOME0. You do not need to edit registries or change Oracle connection information an a special way for SSAS. If an Oracle app of the same bitness (32/64 bit) can connect, then SSAS should be able to too.

https://www.tek-tips.com/viewthread.cfm?qid=1016413&page=28
”In its wisdom, Analysis Services uses the Oracle "HOME" string in the registry to find the TNSNAMES.ora.
When you're building the cube and the dimensions, it finds TNSNAMES.ora ok using HOME1, but then when you want to run the cube, it decides to use HOME0 as the Oracle Home directory - and I didn't have TNSNAMES.ora. Why can't Microsoft write the software to use a standard Oracle Home location? “

Details

As some people are shaky on Windows & mention they like pictures. Here are some screenshots that my help you.

Step 1: Analysis Services cube processing failed.

When Processing Dimensions you get an error message indicating the connection couldn’t be made to with the DataSource. You can ignore the rest of the errors as they are caused by this failure.

ORA-01017 invalid username password logon denied
Picture: ORA-01017: Invalid username/password; logon denied.

 
Picture: ORA-12154: TNS could not resolve the connect identifier specified.

Note: It is likely you received this message after you attempted to process a cube or dimension. As below.

Process Dimension

Right Click on the cube or Dimension you want to process.

Process Dimension 2

Click Run to begin the processing operation.

 

Step 2: Ensure the Windows Domain Account you are using is in the appropriate SSAS Admin group

  • To Open Computer Management: Click Windows Start.
  • R.Click Computer –> Manage. Expand the Local Users & Groups node.
  • Search the Descriptions looking for Instances that say “Analysis Services” if you have more than one, make sure you change the one you are using :-)
  • Click “Add to Group…”

Computer Mgmnt Add to OLAP Group

  • Click “Add” to add a new user to this group.
    In this case I’m adding a service account “MyOLAPServiceAccount”.
  • Then click “Check Names” & if it finds it, click OK.

Add Domain Service Account to Group

You may also want to try adding the account to the local Administrators, in case it is a file permissions issue (can’t see the TNSNAMES.ORA file). But remember to remove it later. It is much safer to find the directory it needs & give the Service Account read access. Than to give it full Admin rights to the entire box.

 

Step 3: Try changing the Service Account of the SQL Service Analysis Services to logon with your account.

Note: Using your personal account to start any Windows Service is not a security '”Best Practice”. Once you’ve solved the issue, remember to create a Domain account for the OLAP service & ensure SSAS is using it & not yours.

Change OLAP Service properties

  • Windows Start Menu: All Programs => Microsoft SQL Server 200?? => Configuration Tools => Configuration Manager
  • Choose Appropriate Instance of Analysis Services
  • R.Click Properties.

Change Service Account

Change from Built-in account: to This Account & enter you own personal account. (the fictional account I created for this blog was “CENTRAL\DLEAN2") or you could use something more meaningful Account Name like “DomainName\SSASServiceAcount”.
Note: Some sites create one Service account for all SQL Server services on all servers. Others have a separate account for each type of service (eg: Analysis, Reporting, Database). Others create a specific account for each SQL Service on each machine. There are pros & cons with each approach. The right answer will vary depending on the scale & interdependency of your environment, your security needs & your process of managing accounts & passwords.

TIP: Oracle Datasource View configuration

You will notice this post applies to a variety of Connection error messages. You will get a slightly different wording depending on the values you used when configuring the “Impersonation Information” but the end result is the same, you can’t connect.

To check your connection, Open the DataSource you are using in BIDS.

Connection Manager

I’ve found both the Oracle & the Microsoft OLE-DB providers work well. You will need to specify your Oracle User name as I do not believe Oracle supports Windows NT Integrated Security (bummer huh).
(Note: I just invented the user name sepuser (September User) for this picture. It is not a special Oracle name)

Data Source Designer Impersonation Info

In the Impersonation Information page. The Inherit option worked fine once I’d resolved the SSAS service account issue. You can also choose the other options but remember they are WINDOWS accounts. You have already typed in the Oracle Account info in the prior tab. This dialog is generic to all different types of OLE-DB providers & different authentication schemes, what I’ve shown works most times in an Oracle world.

I trust this gets you up & running quickly.

Dave

Credits

My thanks to Richard Lees and Edward Melomed for insight that helped me solve this issue.