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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rainer JeschorCommented:
have you installed the correct Oracle Data Access Components (64bit on SharePoint) ?
Some steps mentioned here:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.