none
Cannot refresh data from external data connection with excel services RRS feed

  • Question

  • I cannot get excel services to refresh data on external data connection.  If I open excel on desktop, it will refresh.  I have created a new ODC from a copy of one in the default connection library.  The connection file is ...."Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=default_project_evolution_db;Data Source=jxn-ms-hpalm1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LC3000021819;Use Encryption for Data=False;Tag with column collation when possible=False"....  Again, this is an external data connection to an HP ALM testing repository (SQL)   It will not even refresh if I do the manual data refresh on all connections in excel services.  When I try the manual refresh all connections in excel services, I get errors that are shown in pic 1 and 2 below.  I have tried with two different ids in the SSS ID authentication field and I get two different errors.  The first pic shows I tried using ProjectServerApplication (which is the one set up with project server) and second pic shows I tried the sappsunattended id.  Really confused....  works with desktop but does not with services.  I should also note that the actual unattended service account AD name is sappsunattendedprod which we have given rights to in the SQL db with db_datareader and the actual target application that came up with PPS is this ...63aefc03-2e47-4c41-973e-3d3f3ab664f0-PPSUnattendedAccount.  Completely stumped.  Does anybody have any idea at all as to how I can fix this?  REALLY need to get this working.  


    Cletus51

    Thursday, May 2, 2013 10:44 PM

Answers

  • Got it!!   Thanks everyone....  Here is the feedback.

    Excel Services was started....but not completely configured.  The firm we used apparently did not complete some of the activities.  So, I had to create a target application for the unattended service account.  I followed the steps in http://technet.microsoft.com/en-us/library/hh525344(v=office.14).aspx.  By using this doc, I was able to create the target app of ExcelServicesUnattended.  I had to make sure that in the Global Settings within the ExcelServicesUnattended appliction that I set the Target ID.  Then, I had to go into the ODC files and within the Authentication settings, place the new ExcelServicesApplication as the SSS ID.  I should note that the video in the above link takes you through each step with utmost clarity. 

    THANKS!!!!  for the help from all of you on this forum.....  Would not have made this happen without the great advice and direction.


    Cletus51

    Friday, May 3, 2013 9:20 PM

All replies

  • If it worked in the desktop, I would guess that it's using your Windows credentials to access the data source, and that works.

    My guess is that the Secure Store service is not configured properly and/or the account it's using doesn't have access to the data source.  Revisit that and ensure it's working correctly.


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Friday, May 3, 2013 12:27 AM
    Moderator
  • Hi,

    To view Excel report in Web Please add Users EID in Report viewer group.Report viewer group need to be add in Sharepoint side.

    To view Excel Report in Excel client Add users EID in report Author group. Report Author group needs Read only permission in the reporting DB.

    After checking all this and Adding UID if problem persist please restart the IIS. It will solve your problem.


    kirtesh

    Friday, May 3, 2013 6:00 AM
  • Thanks ...  question if I may.  Can you tell me if I missed something on setup....here is my check list.

    1. Created AD account sappsunattendedprod and set to non-expire..
    2. I then went to Performance Point Services in app settings in central admin and put the unattended service account i just created, sappsunattendedprod, in the unattended service account fields and clicked ok.
    3.  I then went to secure store and checked to see if it added.  This is what I see.

    4.  I then added the sappsunattended account to the SQL database i want to access with db_datareader permissions.
     As i mentioned, I can get to the external sql db from sql queries and with ODC data connections with the client BUT it will not let me refresh with launching just the browser excel.  I have been researching and found the following info in the pic below......  Do I have to add an excel services account of some kind as well using Secure Store New action?


    Cletus51

    Friday, May 3, 2013 2:20 PM
  • Hi,

    Have you configured Excel services??

    Please go through http://technet.microsoft.com/en-us/library/ee662106%28office.14%29.aspx#section3

    also add sappsunattended account in secure store.

    Hope this helps...


    kirtesh

    Friday, May 3, 2013 3:47 PM
  • The excel services is started and the trusted file locations are there with children accepted for both templates and samples libraries.  When I configured PPS I added the secure store AD account titled sappsunattendedprod to the pps secure store.  I am wondering if this is my problem....link below.  Could this be what is happening?  I have not done this for Excel services and I noticed in the pic i included in chain above that they do have an excel services target id. Could this be the issue?  If so, can you use the same AD account for the unattended service account in project server, pps and excel?

    http://technet.microsoft.com/en-us/library/hh525344(v=office.14).aspx


    Cletus51

    Friday, May 3, 2013 4:01 PM
  • Hi,

    first of all i would like to mention Excel and PPS configuration are not interrelated.You need to follow all the instruction for Excel sheet as per the link.

    It would be good  to use Service account for Excel and PPS.

    first configure Excel service it will create excel service target id use your service account which you used for Project server installation. also use that account in secure store while passing Account and password.


    kirtesh



    • Proposed as answer by Kirteshtiw Friday, May 3, 2013 6:22 PM
    • Edited by Kirteshtiw Friday, May 3, 2013 8:03 PM
    Friday, May 3, 2013 6:20 PM
  • Thank you for this information.  I will give this a try and reply back with my findings. 

    Cletus51

    Friday, May 3, 2013 6:31 PM
  • In the scenario that you describe at the top of this thread, you're using an ODC file to connect to your data source. That's an entirely different thing than using an unattended service account. If you want to use an ODC file, see Configure Excel Services data refresh by using external data connections (SharePoint Server 2010).

    For a look at the various options and how they interrelate, see Secure Store for Business Intelligence service applications (SharePoint Server 2010). I would also recommend watching the Target Application video at the bottom of Configure the Secure Store Service (SharePoint Server 2010). That provides a look at how Secure Store works.

    As Kirtesh mentions, PPS is unrelated, so if you're using Excel and Excel Services, then PPS never comes into play. Given that, based on your original description, you could probably try 63aefc03-2e47-4c41-973e-3d3f3ab664f0-PPSUnattendedAccount as your SSS ID and it might work. You'd basically be pointing your Excel workbook at the PPS unattended account (sappsunattendedprod) which has the access that you need. Maybe worth a try to see if it works, but not an ideal solution in the long term as you're mixing and matching PPS and Excel Services and that could be a maintenance headache.

    If you continue to have trouble, check out the Excel Services data refresh flowchart for help with troubleshooting where the error is coming from.

    Friday, May 3, 2013 7:53 PM
  • Got it!!   Thanks everyone....  Here is the feedback.

    Excel Services was started....but not completely configured.  The firm we used apparently did not complete some of the activities.  So, I had to create a target application for the unattended service account.  I followed the steps in http://technet.microsoft.com/en-us/library/hh525344(v=office.14).aspx.  By using this doc, I was able to create the target app of ExcelServicesUnattended.  I had to make sure that in the Global Settings within the ExcelServicesUnattended appliction that I set the Target ID.  Then, I had to go into the ODC files and within the Authentication settings, place the new ExcelServicesApplication as the SSS ID.  I should note that the video in the above link takes you through each step with utmost clarity. 

    THANKS!!!!  for the help from all of you on this forum.....  Would not have made this happen without the great advice and direction.


    Cletus51

    Friday, May 3, 2013 9:20 PM