locked
Management Dashboard authentication issue RRS feed

  • Question

  • When attempting to open PowerPivot Management Dashboard receive an login challenge (looks like can't authenticate to IIS). No errors in Windows logs. Security login 4624 event Kerberos. Excel Services working fine (can publish and view Excel workbooks with remote Analysis Services /SQL Server data sources). Local Analysis Services\POWERPIVOT with domain account. Can connect to it remotely with SQL Management Studio and see Sandbox database etc. PowerPivot is running its own application pool with its own domain user account. Can access and modify PowerPivot Configure service application settings. User is a farm administrator who installed all components including PowerPivot.

    Can publish + view PowerPivot documents OK

    The Workbook Activity - Chart webpart fails with: Excel An error has occurred. Please try again.

    Tuesday, August 17, 2010 12:11 PM

Answers

  • Hi Stephen,

     

    Do you have Kerberos configured in your farm? If you do have, let’s try this:

     

    1.      Go to your <MOSS Install Dir>\ISAPI\PowerPivot

    (for exemple: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\PowerPivot)

     

    2.      Open web.config

    3.      On the NtlmSecurity binding change “ClientCredentialType” from “Ntlm” to “Windows”

     

     

    THanks,

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Wednesday, February 9, 2011 1:51 AM

All replies

  • First, please verify that Excel Services is indeed working correctly with PowerPivot. It isn't enough to just view a PowerPivot workbook. You need to either click on a slicer or refresh the connection. Until then, Excel Services is working with it's pivot cache view of the pivot table data -- it does not actually make a connection to PowerPivot. You must click on a slicer to verify that PowerPivot is working.

    If click on a slicer works, then you will need to go to the ULS and see what events are being raised. Excel Services never gives good error messages. The real turth is always within the ULS.

     


    Dave Wickert (MSFT) blog: http://www.powerpivotgeek.com/ Enjoy!
    Monday, August 23, 2010 5:35 AM
  • Slicers work ok. From the logs (if you copy and paste into notepad it may be more readable):

    08/23/2010 14:18:22.09  w3wp.exe (0x0F9C)                        0x1BFC SSAS Mid-Tier Service          Unknown                        97 Verbose  Returning connection to the pool in ReturnServerConnection. There are 1 admin Connections and 3 user connections in the pool. 
    08/23/2010 14:18:22.09  w3wp.exe (0x1184)                        0x1424 SSAS Mid-Tier Service          ITOps Dashborad                107 Verbose  Finish rendering WorkbookWebPart 72d59e88-bba5-44ab-9f58-95e58285b895
    08/23/2010 14:18:22.09  w3wp.exe (0x1184)                        0x1424 SharePoint Foundation          Monitoring                     b4ly High     Leaving Monitored Scope (Render WebPart Workbook Activity - List). Execution Time=156.2416 72d59e88-bba5-44ab-9f58-95e58285b895
    08/23/2010 14:18:22.09  w3wp.exe (0x1184)                        0x1424 SharePoint Foundation          Monitoring                     b4ly High     Leaving Monitored Scope (Render WebPart Zone Row2Right). Execution Time=156.2816 72d59e88-bba5-44ab-9f58-95e58285b895
    08/23/2010 14:18:22.09  w3wp.exe (0x1184)                        0x1424 SSAS Mid-Tier Service          ITOps Dashborad                104 Verbose  Begin to render DataRefreshHistoryWebPart 72d59e88-bba5-44ab-9f58-95e58285b895
    08/23/2010 14:18:22.09  w3wp.exe (0x1184)                        0x1424 SharePoint Foundation          Topology                       e5mc Medium   WcfSendRequest: RemoteAddress: 'http://[MYSERVER]:32843/bbc68932cff24d38a2699cdf73d11f3a/SSASMidTierService.svc' Channel: 'Microsoft.AnalysisServices.SharePoint.Integration.IGeminiServiceApplicationClient' Action: 'http://tempuri.org/IGeminiServiceApplication/GetLastDataRefreshRuns' MessageId: 'urn:uuid:2740770a-8f4f-4aa5-8113-67f052d3d7d9' 72d59e88-bba5-44ab-9f58-95e58285b895
    08/23/2010 14:18:22.09  w3wp.exe (0x0F9C)                        0x1BFC SharePoint Foundation          Topology                       e5mb Medium   WcfReceiveRequest: LocalAddress: 'http://[FQDN MYSERVER]:32843/bbc68932cff24d38a2699cdf73d11f3a/SSASMidTierService.svc' Channel: 'System.ServiceModel.Channels.ServiceChannel' Action: 'http://tempuri.org/IGeminiServiceApplication/GetLastDataRefreshRuns' MessageId: 'urn:uuid:2740770a-8f4f-4aa5-8113-67f052d3d7d9' 72d59e88-bba5-44ab-9f58-95e58285b895
    08/23/2010 14:18:22.09  w3wp.exe (0x0F9C)                        0x1BFC SharePoint Foundation          Monitoring                     nasq Medium   Entering monitored scope (ExecuteWcfServerOperation) 72d59e88-bba5-44ab-9f58-95e58285b895
    08/23/2010 14:18:22.09  w3wp.exe (0x0F9C)                        0x1BFC SharePoint Foundation          Monitoring                     b4ly Medium   Leaving Monitored Scope (ExecuteWcfServerOperation). Execution Time=2.3911 72d59e88-bba5-44ab-9f58-95e58285b895
    08/23/2010 14:18:22.09  w3wp.exe (0x1184)                        0x1424 SSAS Mid-Tier Service          ITOps Dashborad                104 Verbose  Finish rendering DataRefreshHistoryWebPart 72d59e88-bba5-44ab-9f58-95e58285b895
    08/23/2010 14:18:22.09  w3wp.exe (0x1184)                        0x1424 SSAS Mid-Tier Service          ITOps Dashborad                103 Verbose  Finish rendering DataRefreshFailureWebPart 72d59e88-bba5-44ab-9f58-95e58285b895
    08/23/2010 14:18:22.09  w3wp.exe (0x1184)                        0x1424 SSAS Mid-Tier Service          ITOps Dashborad                105 Verbose  Begin to render ReportsWebPart 72d59e88-bba5-44ab-9f58-95e58285b895
    08/23/2010 14:18:22.09  w3wp.exe (0x1184)                        0x1424 SSAS Mid-Tier Service          ITOps Dashborad                105 Verbose  Finish rendering ReportsWebPart 72d59e88-bba5-44ab-9f58-95e58285b895

    Monday, August 23, 2010 6:25 AM
  • Is this still happening? I have never seen this before. You are saying that you are able to access all other pages in central admin without any problems but when you try to go to our dashboard page you get this prompt for credentials? Can you confirm that Central Admin's "Site" in IIS, has its IIS authentication settings to enable Windows Auth but not Forms or Basic or anything else? I would also be curious to see the fiddler trace but am not sure if you can put that up here.
    Lee Graber
    Monday, August 30, 2010 3:32 PM
  • Yep, still happening. PowerPivot from a bi site works fine. Central Admin works fine (never prompts for authentication). Only PowerPivot Dashboard does. Weird but true. Any thoughts about diagnosing problem welcome. How does Silverlight render the Workbook Activity Chart? The odd thing is in a new site collection Excel Services seems to work ok too. Is it something to do with my Central Administration configuration - like a feature not enabled or an SPN issue or...
    Tuesday, August 31, 2010 9:11 AM
  • The Workbook activity chart is just an Excel Services Web Part. Do you have anything special in the configuration of your ServiceApplication Proxy Groups? Central Admin uses the default proxy group. Are you using just a basic single, default proxy group? That is the first thing I can think of. Let me know

    Lee


    Lee Graber
    Tuesday, August 31, 2010 10:50 PM
  • Yes, just the pre-configured default proxy group.

    Excel Services Application Web Service Application Proxy is enabled in the default proxy group

    Wednesday, September 1, 2010 12:06 AM
  • Hi Stephen,

       I have sometimes found in the past that prompting for credentials is often related to Inter(Intra)net Zone settings in IE. Are you accessing central admin via http://localhost:<port>? Can you check your trusted sites settings and see what you have set for the domain you are trusting? Perhaps we are setting the url for the workbook to be shown via ECS Webpart to use a different variation of the name? Do you have any type of AAM setup for central admin (not even sure if you can)? Let me know if something pops (or not)

    Lee


    Lee Graber
    Friday, September 3, 2010 2:18 PM
  • Hi,

    I have the same issue as Stephen.  Everything is loading well in the management Dashboard except the "Workbook Activity - Chart" web part. If I open the Workbook activity file in the browser, it's working well.  Which file is used to run this web part?  The excel service account has now DB owner access on the SharePoint_AdminContent database and I tried to add it as a farm administrator also to make sure the issue is not coming from the access but it's still not loading.

    Dave

    Friday, September 3, 2010 3:58 PM
  • My default Public URL is: http://sharepointadmin:44598

    I have two Internal URL Alternative Access Mappings:

    http://sharepointadmin:44598 (default zone)  Public URL for zone: http://sharepointadmin:44598

    http://servername:44598 (default zone) Public URL for zone: http://sharepointadmin:44598

    I have noticed that if I attempt to browser to http://servername:44598 I can't get in due to security challenge

    However, the challenge I get for the Dashboard is always sharepointadmin.mydomain.com regardless of if I open Central Admin as:

    http://sharepointadmin:44598 or http://sharepointadmin.mydomain.com:44598

    The sharepointadmin site and sharepointadmin.mydomain.com are recongnised by the browser as "Intranet". 

    Saturday, September 4, 2010 5:14 AM
  • Anyone solve this?  I'm having the same issue.  Must be Kerberos related.  If I change the authentication provider for Central admin to use NTLM the issue Dashboard loads correctly.  If I change it back to kerberos I'm prompted for credentials.   Likely and issue with an SPN
    Thursday, February 3, 2011 6:31 AM
  •  

     

    Jamin,

     

    Did you try to stop and start the Claims to Windows Token Service in the SharePoint's Central Administration app at Services on Server page? For more information, you can check out this blog post:

     

    http://powerpivotgeek.com/2009/12/11/excel-services-delegation/

     

    Thanks,

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Thursday, February 3, 2011 5:12 PM
  • Did the trick..Thanks!
    Friday, February 4, 2011 4:00 AM
  • Excel services works fine for me. Chart in dashboard still fails
    Monday, February 7, 2011 11:59 PM
  •  

     

    What does happen when you open the Dashboard page? Can you share a screen shot and the ULS logs? What is the user role that you are using to load the Dashboard page?

     

    Best,

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Tuesday, February 8, 2011 12:02 AM
  • Hi Stephen,

     

    Do you have Kerberos configured in your farm? If you do have, let’s try this:

     

    1.      Go to your <MOSS Install Dir>\ISAPI\PowerPivot

    (for exemple: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\PowerPivot)

     

    2.      Open web.config

    3.      On the NtlmSecurity binding change “ClientCredentialType” from “Ntlm” to “Windows”

     

     

    THanks,

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Wednesday, February 9, 2011 1:51 AM
  • Mariano,

    I had previously changed the Authentication provider for Central admin to use "NTLM" rather than Kerberos.  This of course alleviated the issue that is discussed in this thread.  I have since changed the Authentication provider back to "Kerberos" for CA.  I was again receiving the prompts for credentials and the red X. 

    Finally, I made the configuration changes to the web.config that you suggested.  See below:

     <binding name="NtlmSecurity" maxReceivedMessageSize="2147483647">
              <readerQuotas maxStringContentLength="5000000" />
              <security mode="TransportCredentialOnly">
                <transport clientCredentialType="Windows" />
              </security>
    </binding>

    I then rebooted my server and this resolved the issue.  Only question left is whether an SPNs would be needed.  I have Central admin running on the same server as PowerPivot, but in the event that it was running a different server it stands to reason that it might be necessary to add an SPN to allow the PowerPivot Web Service to delegate to AS in SharePoint Integrated mode?

    Finally, it seems that the technet article Configuring Kerberos Authentication for SharePoint 2010 Products (http://go.microsoft.com/fwlink/?LinkId=196600) should be updated to include a scenario for configuring Central Administration site for Kerberos with a specific section regarding the PowerPivot Management dashboard.

    Thanks

    --JM

    • Edited by Jamin Mace Wednesday, February 9, 2011 3:05 AM type
    Wednesday, February 9, 2011 3:03 AM
  •  

    Hi Jamin,

     

    You want to configure SPNs when you need to pass along credentials from SharePoint to another system, like a web service. With Kerberos you avoid double hop issues. If your SharePoint farm does use Kerberos, than most likely PowerPivot will need to be aware of it at some point (in the PowerPivot Dashboard, or in some data refresh scheduling configurations, etc) so it can work properly.

    It's possible in a multi-machine farm, with the Central Administration application running in box A and the application servers running PowerPivot for SharePoint in boxes B, C and D to work using NTML for some user needs (for instance, if they need to access a Analysis Service cube external to that farm, this user must consider using Kerberos).

    Anyway, the issue that started this thread and the workaround I gave is a known bug that has already been fixed and will be available in SP1, I believe. After that no workaround will be needed.

    Thanks,

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Wednesday, February 9, 2011 3:35 AM
  • Be careful here, guys.  I made this change to get the Management Dashboard working (which did the trick - thanks Mariano!), but later when I tried to use an uploaded PowerPivot workbook as a data source, I got 401 Unauthorized errors out the ying-yang.  My debugging attempts went like this:

    - verbose ULS logs... useless for this issue
    - OLE DB for Analysis Services 10.0 driver re-install on my client machine and all servers in the farm... nada
    - Netmon 3.4... ok, I see the 401 response :-(
    - WIF/WCF Tracing... whoa, I could see my pool account impersonating me - cool, but no help
    - Failed Request Tracing in IIS 7.5 - voila!

    After nearly a full day of troubleshooting, I finally stumbled on the Failed Request Tracing functionality in IIS 7.5 (pretty cool, btw) and saw that I was sending kerberos tickets to the PowerPivot Redirector service, and I was getting an Unauthorized 401 from somewhere deeper in the stack (can't remember, exactly).

    I looked at the other Sharepoint Web Servcies (the guid ones) and they're all using Anonymous Authentication.  Well, that's all well and good for internal services, but these PowerPivot services are external facing.  Then, I figured that if making the change referenced in this thread had fixed the dashboard, maybe the services farther down the stack also need to be using "Windows" authentication.  So, I went back to "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\isapi\PowerPivot\web.config" and changed all 4 basicHttpBindings at the top to clientCredentialType="Windows" and also changed the 2 customBindings to authenticationScheme="Negotiate".  And voila!  I was immediately able to use powerpivot workbooks as data sources, AND my Management Dashboard still worked like a charm!

    Now, I may have broken other stuff that's yet to be discovered... however, I finally have a fully functional Kerberos-enabled Sharepoint farm that has PowerPivot and all its bells and whistles.  Hallelujah.

    So, moral of the story... if you've taken the time to configure a Kerberos-enabled Sharepoint farm, and your web applications are set to "Negotiate (Kerberos)" instead of "NTLM", then you'll have to change the web.config for the externally-facing PowerPivot services to match.  It seems like most everything will "fall back" to NTLM if Kerberos doesn't get it done (including IIS), but WCF services can only specify one "clientCredentialType" per binding.

    BTW - If you use the Excel -> Data -> From Other Source -> From Data Connection Wizard -> Other/Advanced -> Microsoft OLE DB Provider for Analysis Services 10.0 to connect to a PowerPivot workbook on Sharepoint AND YOU'RE ON A 64-BIT CLIENT OS and you get the "Test connection succeeded but some settings were not accepted by the provider." message, don't sweat it.  Evidently, this is a 64-bit OS issue, not a PowerPivot issue (http://www.reportportal.com/forum/topic.asp?TOPIC_ID=1473).  I can connect to workbooks using the Excel -> PowerPivot -> From Database -> From Analysis Service or PowerPivot -> approach with the expected "Test connection succeeded." result.

    Also, I don't have any issues connecting to PowerPivot workbooks that are under a managed path (http://www.bluedoglimited.com/SharePointThoughts/Lists/Posts/Post.aspx?ID=317).  However, I do have a site collection at the root with a fully-functional Reporting Services folder.

    I plan to cross-post this solution to http://social.msdn.microsoft.com/Forums/ar-SA/sqlkjpowerpivotforexcel/thread/daf20145-e996-4c93-9450-39bbdbf2c5ab as well, since I posted my ULS logs there yesterday.


    Also, see my post here for a solution to a stale PowerPivot Management Dashboard, due to a UNION ALL error in the SharePoint Foundation Usage Data Processing timer job (caused, ironically enough, by the SharePoint Foundation Usage Data Import timer job setting constraints to "not trusted" by using "insert bulk" statements).


    Joe Cole




    Thursday, August 4, 2011 9:30 PM
  • Hi,

    I have exaclty the same issue regarding the Management Dashboard and being prompted for credentials (and yes, flipping Central Admin to NTLM resolves the issues).

    I've tried the step above but to no avail: "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\isapi\PowerPivot\web.config" and changed all 4 basicHttpBindings at the top to clientCredentialType="Windows" and also changed the 2 customBindings to authenticationScheme="Negotiate". "

    Someone has mentioned that SharePoint 2010 SP1 fixes this, can anyone confirm this for me please?

    I personally believe an additonal SPN for Central Admin required, but as the authenticatio prompt is calling the Central Admin hostname, which already has a SPN assigned to it under the Farm Account identity, I don't see what option I have?

    Confused!

    Wednesday, December 21, 2011 8:37 AM
  • Thanks Joe, once again it is proved that just because a thread is many moons old, doesn't mean it won't help.

    In SharePoint2013 with PowerPivot, we were seeing the same prompt for authentication from the PowerPivot dashboard, in a new farm set up for Kerberos authentication.

    The web.config in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\PowerPivot still has the <bindings> element with the 4 <binding> elements, each with a <transport clientCredentialType="whatever"> subelement, but the <services> element doesn't have any subelement with an authenticationScheme attribute.  It looks to me as though the service is using the values defined in the <bindings> element in the <services> element

    I changed all 4 <binding> elements as you suggest to clientCredentialType="Windows," and 1) the authentication prompt went away and 2) the "Workbook Activity - Chart" seems to render fine.

    There may be more work to do, but this is substantial progress.

    Excellent work on your part and once again, many thanks.

    --Gront

    Tuesday, February 12, 2013 12:16 AM
  • Did you try with NTLM instead of Kerberos

    Thanks,

    Browse for Learning

    www.sqlservermanagementstudio.net

    • Edited by SQLSMS.NET Thursday, February 14, 2013 4:18 AM
    Thursday, February 14, 2013 4:17 AM