The recording of the Builders' Café Webinar Series - PI OLEDB Enterprise 2010 has been posted at the auditorium.
Have you any experience in using the PI-OLEDB Enterprise driver referenced as a trusted Data Connection in SharePoint 2007. I would like to create a Excel Services report which references an Office Data Connection stored in the SharePoint database. I build the report fine and it brings back the rows of data from AF, but as soon as I publish the report to SharePoint and try to open using IE, it complains that table queries do not work with Excel Services. Any advice would be appreciated on how to use AF within Excel Services.
I think this is because of feature limitation in Excel Services, as listed here. If you refer to the list, we can see that table queries is one of them. One way to workaround this is to use pivot table that flattens to a table to display your result that is retrieved from Data Connections mentioned in this url.
Hope this helps.
Yes after I made the post I realised that query tables were not allowed so I switched to a pivot table. I now have a spreadsheet that works perfectly and references an ODC file in a SharePoint data connection library, which in turn has teh connection string and SQL statement to get data from AF. I have said to use windows authentication in Excel and also in the AF OLEDBENT connection string. Unfortunately when I try and view the report in the browser and then try and refresh the connections I get an error "The data sources may be unreachable, may not be responding, or may have denied you access". As far as I know I have full access to everything so I really do not know where the problem is or how to debug. Nothing is in the PI Messge Log, nor in the PIOLEDB.LOG file so this is something internally within SharePoint. Things are just too complicated these days!
Any ideas? I find it hard to believe that this is not the first time that this is the first time this has been tried.
Do you have any other way of trying the connection from the same box? My goal here is to see if the problem lies in your piece or is just a lack of authentication.
Thanks for your reply.
I can use the same ODC file from within Excel itself and the query returns data to me as expected. I can also run queries from SQL Commander. It only breaks when I try and use the ODC file from the same published excel spreadsheet to the portal in explorer mode then try to do a refresh so it must be a permissions thing. I have set in the ODC file to use Windows Authentication and my connection string to AF also uses the Integrated Security Option. I do not get any messages in the PI Log indicating that there is a connection attempt from the OLEDB driver, so it could be that something in SharePoint itself is preventing the query from being executed. Do you know if there is any way to debug SharePoint to see where the problem is?
OSI must have done this kind of thing before - it is on the demos that they show but there does not appear to be a step by step description available. Maybe because this is a ShsrePoint problem and not PI.
I figured this out in the end after much searching in Google and log files on the SharePoint server machine.
I looked at the log files for the SharePoint site (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS\ on my machine) and found the following message just after I tried to refresh the data connections on an Excel report in the web browser.
“excel services can not use credential delegation when it is configured to use a trusted subsystem with windows sharepoint server”
I googled the message and found that the Excel Services AccessModel attribute was setup for TrustedSubsystem. For a standalone / development server it should have been set to Delegation.
I followed the procedure on this thread (social.msdn.microsoft.com/.../b5bf8091-ac3f-46f9-8edd-bbb81cec66be) and now the report works!
So it looks like the OLEDB and OLEDBENT drivers will not support Windows Authentication if the Excel Services has not been configured to allow the windows users credentials to be passed through. If using TrustedSubsystem, I guess that you would have to use an explicit username/password for the connection to the PI Server.
One thing I did notice when I did this was that the sample dashboard (http://xxx/Reports/ReportsLibrary/Sample%20Dashboard.aspx) under the Reports site on a blank SharePoint 2007 Enterprise install stopped working after the change. The dashboard contained Exced workbook web-parts and the referenced excel workbook could no longer be opened. So for these I had to make a change to the library permissions to make it visible again.
Thanks a lot Simon for the elaborate description of the problem and the thorough analysis of the solution. I will make sure that the product owners notice this thread to see if we can make our documentation better
Indeed, thanks for sharing the results of your investigation, and glad to see it now works fine!
As a quick security primer (I think we need a webinar on the topic ), the concept of Trusted Subsystem (of which PI Trusts are an implementation) is effectively opposite to the concept of Impersonation (e.g. Windows Integrated Security). Just like when you set up Security/Authentication settings on a website in IIS (you can select between Anonymous access, impersonation, specific identity, etc.), Excel Services can be configured to use either of the security model. All the pieces along the line need to be set up for impersonation, for impersonation to take place: PI Server, PI OLEDB Enterprise, and Excel Services/SharePoint.
This is a nice piece of "Best Practices", and I will pass this on to make sure we document it somewhere.
Great explanation post from Simon...shouldn't someone sticky this in the Sharepoint forum section?
Simon...small world, your name came up in conversation this week...was speaking with Richard Bond.
How about this: techsupport.osisoft.com/.../KB00270.htm ;)
Looks very thorough to me and hopefully will prevent others from the having same pain as I had.
Retrieving data ...