I have created an Excel workbook in MS Excel 2010 (32-Bit) on my SharePoint 2010 development server. This Excel workbook connects to an Oracle View and shows the data in a Pivot table. The data connection it’s using is present in a SharePoint trusted data connection library. I’ve set the Workbook to refresh data whenever the file is opened and also after every 1 minute. And this works fine. Note that I’ve 32-Bit oracle Client (Oracle 11g R2) installed on the server.
But when I add this workbook to SharePoint and then using the Excel Viewer webpart, I’m trying to display this, it gets displayed but I’m getting this error on the page:
The data connection uses None as the external data connection authentication method and Unattended Service Account has not been configured. The following connections failed to refresh:
In other words, it is not refreshing the data and still shows the old data.
Note that I used ‘None’ in the Excel Services Authentication settings in Microsoft Excel for this data connection.
From the error, it looks like I forgot to specify the UnAttended service account. But believe me, I haven’t. I have clearly specified an Unattended Service Account Application ID i.e. ‘OracleDB’ in the Excel Services global settings in Central Admin. And I’ve already created a target Application i.e. ‘OracleDB’ of type ‘Group’. I’ve also added ‘All Authenticated Users’, ‘All Users’ , ‘Domain Users’ and ‘spdev’ account (the app pool account for Excel Service) as the members in the target application.
I’ve also set the credentials for this target application i.e. UserName and Password.
Also, after enable Verbose logging, I get this in my log file:
Unable to establish connection using only the connection string. If a username and password are saved in the connection string, they may not be correct, or the Unattended Service Account may not be configured. [Session: 1.V21.823HSoNpfns41DFGSz3vT90.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.806e56cd-d368-4ac9-84ee-c1606232bfe21.N User: 0#.w|<domainName>\spdev]
I’m 100% sure that the crednetials are correct because I’m already using the same credentials.
I’ve the whole sharepoint portal defined as the trusted location i.e. ‘http’.
Allow External data is set to ‘Trusted data connection libraries and embedded’.
I’ve added ‘MSDAORA.1’ (with type OLEDB) as a new Trusted Data Provider as this is what I’m using.
If I use the ‘Open in Excel’ option, then only the updated data is loaded from the Oracle i.e. Refresh is successful within the MS Office Excel client application.
I’ve tried resetting the IIS and dragging and dropping the webpart again. Nothing works. Please help.
Excel Services accesses external data sources by using a delegated Windows identity. Consequently, external data sources must reside within the same domain as the SharePoint Server 2010 farm or Excel Services must be configured to use the Secure Store Service. If the Secure Store Service is not used and external data sources do not reside within the same domain, authentication to the external data sources will fail.
Please make sure you are aware of that.
In addition, check if you configure unattended service account. This is a low-privileged account that is impersonated by Excel Services if either of the following conditions are true:
- Any time that it is trying a connection where the None authentication option is selected.
- Whenever the SSS (Secure Store Service) option is selected and the stored credentials are not Windows credentials.
For more information about unattended service account, check out the following article:
Rock Wang TechNet Community Support
- 已标记为答案 Rock Wang– MSFT 2012年4月28日 6:09
Found the solution. The password of the Service Account that I was using for Secure Store Service application got expired. After I created a new Service Application with the new (correct) password of the Service Account, the problem was solved.