locked
Secure Store Service and ProjectServerApplication connection RRS feed

  • Question

  • Hi,

    I've setup the ProjectServerApplication within Secure Store Services as outlined by the documentation on MSDN.  In brief I created a ProjectServerDataAccess account and have given that account dbdatareader access to my reportingdb and analysis services db's.  I've then created an AD group called ProjectServerReportViewers and added myself and 5 other people on my team.  I then went into Secure Store Services and set credentials using the ProjectServerReportViewers as my Creditial Owner and ProjectServerDataAccess as my Windows Account to the ProjectServerAppliction.

    In a nutshell this allows the folks in the AD group to have dbdatareader access to the databases in order to use the excel services reports I'm creating.

    The problem I'm seeing is I have no problem accessing and creating reports but the folks in the AD group get a popup in excel to authenticate to the SQL Server DB.  When I check the properties in their excel report it is set to use Secure Store Services accessing ProjectServerApplication.

    Can anyone explain what might be going on with the security for the users in my ProjectServerReportViewers AD group?  Should I add the ProjectServerDataAccess user to the ProjectServerReportViewer AD group?  I didn't think this was necessary as SharePoint is supposed to make the connection in secure store services.


    Donald R. Landry

    Friday, March 18, 2011 6:16 PM

Answers

  • Hi Donald,

    This error will usually mean that the account trying to use the secure store credentials is not in the group registered to use that application.  That would also include the ProjectServerDataAccess if that is the account trying to use the secure store application.  Just because the account itself has direct access to the db does not give it the ability to by-pass the secure store - it still makes the request for credentials (its own in this unique case) and if it isn't in the right group it gets denied.

    It isn't clear from your description if the account you are seeing the error with is actually this one or not - but testing should be fairly easy - just add them to the group.

    Best regards,

    Brian.


    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Monday, March 21, 2011 8:26 PM

All replies

  • While Excel Services uses Secure Store when rendering a workbook, the Excel client does not and will require you to authenticate directly to the DB. (The Secure Store setting in Excel not used by Excel itself - it's just passed on to SharePoint Server.)

    If you can grant db_datareader access to the ProjectServerReportViewers AD group, then those users will be able to render the reports in the Excel client.

    Friday, March 18, 2011 10:18 PM
  •  

    Thanks Mike I can give this a try Monday but I understood from these instructions http://technet.microsoft.com/en-us/library/ee662106.aspx that when you configure secure store services to handle reporting then the authentication should happen by just associateing the reportviewers ad group with the projectserverdataacces account that I gave db datareader access to it. 

    So if I'm understanding this I can create an excel report and deploy it within the BI center but the moment someone brings it down to their desktop and enables the connection it will require them to authenticate to sql server.  Like I said above when I look at the connection authentication settings when they bring the excel report down to their desktop it shows SSS and ProjectServerApplication as the connection.  Seems to me if the user is part of that ProjectServerApplication connection it should work.

    Any idea how I can connect with Treb Gatte who is the Microsoft Program Manager that outlined how this connection should work in the link above.


    Donald R. Landry
    Sunday, March 20, 2011 9:03 PM
  • Correct, when you open the report in the Excel client, that user must have direct database access rights for the data to refresh. Although the Secure Store ID appears in the spreadsheet, the Excel client doesn’t use Secure Store for authentication. The Secure Store ID is there for Excel Services when rendering the report in a browser.

    In the article and video there is mention of creating a Report Author's group for users who will be authoring reports in the Excel client. This is necessary because of the Excel client's data access requirements. I’ll update the article to clarify that this is needed anytime you want to work with the report in the Excel client.

    Monday, March 21, 2011 2:13 PM
  • Thanks Mike,

    Hopefully you can help me with one more related question.  I've been creating some temp reports using the report wizard in BI and I've set my credentials between my group ProjectServerReportViewers and the account on SQL Server that has access to the DB's called ProjectServerDataAccess.  When I'm bringing up these reports under the BI in a brower I get an error stating that the following connection failed to refresh.  When I look in the ULS log I see the following error "ValidateCredentialClaims - Access Denied: Claims stored in the credentials did not match with the group claim for a group app"

    I've seen this in other threads but the other threads indicate that if you have your folks setup in the AD Group and you have the DB access account as long as you've set the credentials right (which I've triple checked on this) the report should render in the browser. Do you have any other ideas of what else I should check..I've done the following.

    I created the ProjectServerApplication Secure Store Service as outlined in the link above target application admins would be me and my farm account; members would be ProjectServerReportViewers (which includes the team of folks that need to render the reports)

    Then I set credentials using the members tying it too the DB Access account ProjectServerDataAccess which has dbdatareader to my PWA reporting db.  I then restarted all services.  This definitely should work so I can't see why it wouldn't unless I have to add the ProjectServerDataAccess account to the ProjectServersReportViewers AD group.  Thats the only thing I haven't done.

    Any suggestions?

    Thanks


    Donald R. Landry

    Monday, March 21, 2011 5:24 PM
  • Hi Donald,

    This error will usually mean that the account trying to use the secure store credentials is not in the group registered to use that application.  That would also include the ProjectServerDataAccess if that is the account trying to use the secure store application.  Just because the account itself has direct access to the db does not give it the ability to by-pass the secure store - it still makes the request for credentials (its own in this unique case) and if it isn't in the right group it gets denied.

    It isn't clear from your description if the account you are seeing the error with is actually this one or not - but testing should be fairly easy - just add them to the group.

    Best regards,

    Brian.


    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Monday, March 21, 2011 8:26 PM
  • Thanks Brian will do I believe that will fix the issue.

    Thanks


    Donald R. Landry
    Tuesday, March 22, 2011 12:36 PM
  • Hi Brian and Don,

      I passed this fix on to Don a few years ago. Hopefully, this may help someone stuck in the same situation in the future.

      Don was working on this issue for our organization. I grabbed an AD admin to help me put a fresh set of eyes on this issue (thank-you Laura!).

     Laura noticed that (in AD) the Pre-Windows 2000 username was listed as ProjectServerDataAcc. By shortening the account to 20 characters, we could log in. 

      Another co-worker's guess was that along the way, ProjectServerDataAccess was being passed as a parameter and shortened to 20 characters (thanks for the explanation Mike B!)

      Once Don shortened the name, this was no longer an issue. Strange, because the ProjectServerDataAccess account worked in PWA 2010, but not after upgrading to 2013.


    • Edited by hastidav Tuesday, April 25, 2017 9:46 PM
    Tuesday, April 25, 2017 9:44 PM