I’ve been working with a vendor to setup Microsoft Project Server on a client’s site, and they came across an issue of The Data Connection File … workbook is not in a trusted location.
The simplest answer is to go to Excel Services, and add the URL location to the Trusted Data Connections. The only problem was that this setting did not seem to work. Every reference to this error on the web indicated this was all that was required. After trying a few different things, I attempted to add the trusted data connection library via PowerShell:
$excel = Get-SPExcelServiceApplication New-SPExcelDataConnectionLibrary -Address"http://projectserver/BICenter/Data%20Connections%20for%20PerformancePoint/English%20(United%20States)/SQLSERVER_AnalysisServices_Database" -Description "Project Server DCL" -ExcelServicesApplication $excel
I attempted to load the spreadsheet and I did not get the error! It came down to the fact that while looking through all the parameters for the PowerShell cmdlet of New-SPExcelDataConnectionLIbrary that I set a description for the entry for the trusted data connection.
After trying a few other connections, I also discovered that as well as having to set a description, I also needed to ensure I had the the full path to the data connection files including the document library and folder structure. Also of note, you may need to wait a few minutes for the changes to take affect, there is some caching done by Excel services before it recognises the changes made to its configuration.
For reference, the system build this client was running was SharePoint 2010 SP1 with the June 2012 Cumulative Update applied. Given that these particualar details are not mentioned elsewhere online, I wonder if this is perhaps a regression. If what I have described addresses the same problem you are having with the error Data connection file … workbook is not in a trusted location, please leave a comment below with details of your farm build.