Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Excel and Excel Services RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    I installed Project 2010 and I am trying to play with the sample Reports in the Business Intelligence Center.

    All is configured (e.g. Excel Services, Secure Store etc). I use Alternate Access Mappings, with Intranet set as NETBIOS name, and Default as FQDN (I am connecting from the internet using FQDN).

    The Trusted File Locations include both FQDN and simple name (for the sample reports directories), so I managed to have the reports working from the web.

     

    However, when I open the reports in Excel, I get the SQL error saying:

    "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication"

     

    I looked inside the ODC file for that report, and the connection string is:

    <odc:ConnectionString>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=PROJECT2010\SharePoint ;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=PWA_Reporting_ADADBA4F_C3AD_46C8_8C3D_C3B10175D2E8</odc:ConnectionString>

    .. where PROJECT2010 is the name of my server. I tried changing that to the FQDN with the same results.

     

    Is this the right behavior or am I missing something in the configuration. Does Excel need to connect straight to MSSQL (I thought it uses the Excel Services, and from there the connection is done to MSSQL) ?

     

    Thanks for your help.

     

     

     

     

    Tuesday, December 7, 2010 2:04 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote
    Your assumption was correct. Only Excel Services uses the Secure Store. If you use the Excel client application, the user running Excel needs db_databasereader role directly on the Reporting database in SQL. The videos on Technet does a good job of explaining this. http://technet.microsoft.com/en-us/library/ee662106.aspx . It is recommened to create an Active Directory group for Report Authors and give that group db_databasereader role on the ProjectServer_Reporting database.
    • Proposed as answer by Ben Howard [MVP]MVP Tuesday, December 7, 2010 8:37 AM
    • Marked as answer by Alex S.O_ Tuesday, December 7, 2010 10:45 AM
    Tuesday, December 7, 2010 2:27 AM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote
    Your assumption was correct. Only Excel Services uses the Secure Store. If you use the Excel client application, the user running Excel needs db_databasereader role directly on the Reporting database in SQL. The videos on Technet does a good job of explaining this. http://technet.microsoft.com/en-us/library/ee662106.aspx . It is recommened to create an Active Directory group for Report Authors and give that group db_databasereader role on the ProjectServer_Reporting database.
    • Proposed as answer by Ben Howard [MVP]MVP Tuesday, December 7, 2010 8:37 AM
    • Marked as answer by Alex S.O_ Tuesday, December 7, 2010 10:45 AM
    Tuesday, December 7, 2010 2:27 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for clarifying this.

     

    Do you know if there is any way of connecting from Excel client but without being in the same domain ? (e.g. from my machine at home, over the net, but without using openvpn etc). Is there any other authentication option ?

     

    Thanks again.

    Thursday, December 9, 2010 4:30 AM