Description of Issue:
There is currently an unresolved issue with SharePoint 2013, PowerPivot and the oracle client in which data refresh of the workbook fails. The errors returned are either "excel services returned an unknown error" or "refresh failed…null password or logon denied". I am actively researching this issue to find a permanent solution. In the meantime, the steps outlined below will provide a workaround to enable both scheduled and interactive data refresh to an oracle data source.
Workaround:
To get around this issue you will need to leverage the SharePoint 2013 Secure Store.
Your first step will be to get a Secure Store ID created with the oracle credentials you will use to access the oracle database. Once the secure store id is setup and ready to use you will have to add it to each of your workbooks that connect to oracle.
Below are the steps to accomplish this:
To configure the workbook to refresh interactively, do the following:
(Note! Interactive data refresh is only available for workbooks that created in Excel 2013. If you try to refresh an Excel 2010 workbook, Excel Services displays an error message similar to “PowerPivot Operation Failed: The Workbook was created in an older version of Excel and PowerPivot cannot be refreshed until the file is upgraded”. ) Reference.
Open the workbook locally on your computer in Excel.
Click on the "Data" tab and select "Connections".
In the workbook connections window that opens highlight your data connection and click on “properties”.
In the Connection Properties window that pops up click on the "definition" tab, then click on "authentication settings".
In the Authentication Settings windows select the option "Use a stored account" and enter the Secure Store ID that you created.
Click "OK" through each open window and save your workbook back to SharePoint. You should now be able to refresh the workbook interactively.
To configure the workbook to refresh on a schedule do the following:
Open the "manage data refresh" window for the PowerPivot workbook.
Expand the data source at the bottom of the page.
Under Data Source Credentials check the option to "Connect using the credentials saved in a Secure Store Service…" and enter the Secure Store ID that you created.
Once these two steps are complete you should be able to refresh your workbook with Oracle.
Good Luck!
There is currently an unresolved issue with SharePoint 2013, PowerPivot and the oracle client in which data refresh of the workbook fails. The errors returned are either "excel services returned an unknown error" or "refresh failed…null password or logon denied". I am actively researching this issue to find a permanent solution. In the meantime, the steps outlined below will provide a workaround to enable both scheduled and interactive data refresh to an oracle data source.
Workaround:
To get around this issue you will need to leverage the SharePoint 2013 Secure Store.
Your first step will be to get a Secure Store ID created with the oracle credentials you will use to access the oracle database. Once the secure store id is setup and ready to use you will have to add it to each of your workbooks that connect to oracle.
Below are the steps to accomplish this:
To configure the workbook to refresh interactively, do the following:
(Note! Interactive data refresh is only available for workbooks that created in Excel 2013. If you try to refresh an Excel 2010 workbook, Excel Services displays an error message similar to “PowerPivot Operation Failed: The Workbook was created in an older version of Excel and PowerPivot cannot be refreshed until the file is upgraded”. ) Reference.
Open the workbook locally on your computer in Excel.
Click on the "Data" tab and select "Connections".
In the workbook connections window that opens highlight your data connection and click on “properties”.
In the Connection Properties window that pops up click on the "definition" tab, then click on "authentication settings".
In the Authentication Settings windows select the option "Use a stored account" and enter the Secure Store ID that you created.
Click "OK" through each open window and save your workbook back to SharePoint. You should now be able to refresh the workbook interactively.
To configure the workbook to refresh on a schedule do the following:
Open the "manage data refresh" window for the PowerPivot workbook.
Expand the data source at the bottom of the page.
Under Data Source Credentials check the option to "Connect using the credentials saved in a Secure Store Service…" and enter the Secure Store ID that you created.
Once these two steps are complete you should be able to refresh your workbook with Oracle.
Good Luck!
Should I only add Oracle's user name and password in secure store services? can you please add step by step configuration?
ReplyDeleteregards
Yes.
ReplyDeleteI don't have access to screen shots but here is a break down of what to do...
You would create the secure store target application ID as follows:
In Central Admin > Mange Service Applications > Secure Store. Click to create a new secure store target application. First screen in the wizard you will give the ID a name and email then select Type = "Group Restricted". The next screen of the wizard change Field Type to = "Username" and "Password" rather than "windows username" and "windows password". Next you will grant the users who will be accessing the workbook that uses this ID access to do so. Then click finish.
Your final step is to set the oracle credential to the ID. To do so, select the new id from the Secure Store service application settings, bring down the drop down and click on "Set credentials". This is where you will enter the oracle username and password.
One hang up i have found is that in order for the workbook to do a scheduled refresh, which ever account you are logged into sharepoint with and setup the schedule on the workbook must also be added as an administrator of the secure store service with the "Manage Target Application" permission. I use a domain group for this purpose. To do so, open Central Administration > Manage Service Applications > and highlight your secure store service application. Click on "Administrators" in the ribbon, then add the users or groups and assign the "manage target application" permission only.
I hope this helps as it is very annoying that this bug exists. I plan on talking to Microsoft in the next couple of weeks about this issue and I'll post if i get a more permanent fix.
Can you provide any feedback on the back-end configuration of SharePoint? Do we need the 32-bit or 64-bit Oracle client installed and where? How about configuring the Excel Service Application with a "Trusted Data Provider"? We have installed the 64-bit Oracle client on all SSAS and SharePoint servers, validated the connections to Oracle using the Oracle OLEDB provider and configured the Secure Store Target App ID as you describe above. When we run a scheduled data refresh on our PowerPivot workbook we're receiving an error "Engine error during processing of OLE DB or ODBC error: The specified module could not be found".
ReplyDeleteI used the x64 bit oracle client version 11.2.0.3 and put it on the SSAS/PowerPivot App servers and all of the WFE servers. I added "OraOLEDB.Oracle.1" type=OLE DB provider in the excel services trusted providers area.
ReplyDeleteOne thought is that you might need to build the powerpivot model with a x64 bit version of Excel 2013 and then save it up to sharepoint. We noticed a number of issues trying to use Excel 2010 with the updated powerpivot add-in.
Also, make sure you reboot your SSAS/PowerPivot SP servers after installing the x64 bit oracle client to make sure it gets registered.
That helps tremendously!! Thank you VERY much!
DeleteTo your knowledge, did this ever get fixed in a later rollup, etc....?
ReplyDeleteThanks
I have the same problem within excel / powerpivot.... haven't found a solution yet.
ReplyDelete