SharePoint BI Data Refresh to Oracle DB failing

I'm working on a BI center built in Sharepoint 2013, and we are seeing some oddities with our farm.

Our team as already built one PowerPivot workbook of the following:
1. MS SQL Server datastore.
2. Teradata Data Warehouse.

Now we are going after a PowerPivot that needs to connect to Oracle.  I have been able to build the connection and recordset on my desktop through an ODBC driver connection.  But when I post it up to the SharePoint farm we cannot get the data refresh cycle to work.  It's just throwing an error that says "Invalid user credentials or permissions."

Here are the things that we have tried:
1. Installed Oracle Data Access Components for 11gR202 on the SharePoint farm to install the .NET data provider.  This is the same pattern that we used on the Teradata problem.
2. Setup the Secure Store with the Account & Pwd for the connecting to the Oracle DB.

Manually built the workbook and the PowerPivot tables on the desktop connecting to the Oracle DB.  Then Saved and published it up to our PowerPivot Gallery on the SharePoint site.

Have I over looked something with data connectivity to Oracle?
Allan ReitanChief Financial OfficerAsked:
Who is Participating?
Rainer JeschorConnect With a Mentor Commented:
have you installed the correct Oracle Data Access Components (64bit on SharePoint) ?
Some steps mentioned here:

Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
>>Invalid user credentials or permissions<<
It clearly states that it someting to do with permission for the user, do the oracle user as permission to connect to sharepoint?

Also let us know do you any oracle related error like "ORA-% "

Check ithis doc for more info.
Allan ReitanChief Financial OfficerAuthor Commented:
Thank you for the responses, here is an update on the details based on some of the clarifying questions I received.

1. ODAC drivers used for the installation were 64-bit.
2. The type of account(s) that are being used to connect to the Teradata & Oracle DB were database internal accounts, not a domain user/service account.
2.a. we were observing a like style of failure using a SQL Login account on a known DB server.

I was able to get one BI dashboard like this to refresh by having my DBA setup a service account from my SharePoint 2013 farm on a MS SQL Server.  Additionally, I have installed the Teradata and ODAC drivers on the SharePoint - SQL/SSAS instance for testing interoperability.
Allan ReitanChief Financial OfficerAuthor Commented:
The checklist on the link was helpful.  in working with IT department I was able to use the Basic LiteClient from Oracle and the ODBC pack in lieu of the full client.

The ODAC version for my corresponding Databases were correct, and the settings for the Data Refresh cycle and Secure Store Services account were correct.

I had to install the Oracle LiteClient, plus ODBC pack, plus ODAC the SQL Server Analysis Server in order to get everything working.

Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.