CodeOverEasy.com

Tag: Reporting Services

  • PowerView report fails – No credentials are available in the security package

    PowerView report fails – No credentials are available in the security package

    So I’ve got a PowerView report hosted in SharePoint.  And it throws up the following when I try to load it:

    PowerView Auth fail

    The full error details are:

    <detail><ErrorCode xmlns="https://www.microsoft.com/sql/reportingservices">rsCannotRetrieveModel</ErrorCode><HttpStatus xmlns="https://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message xmlns="https://www.microsoft.com/sql/reportingservices">An error occurred while loading the model for the item or data source 'EntityDataSource'. Verify that the connection information is correct and that you have permissions to access the data source.</Message><HelpLink xmlns="https://www.microsoft.com/sql/reportingservices">https://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsCannotRetrieveModel&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.3128.0</HelpLink><ProductName xmlns="https://www.microsoft.com/sql/reportingservices">Microsoft SQL Server Reporting Services</ProductName><ProductVersion xmlns="https://www.microsoft.com/sql/reportingservices">11.0.3128.0</ProductVersion><ProductLocaleId xmlns="https://www.microsoft.com/sql/reportingservices">127</ProductLocaleId><OperatingSystem xmlns="https://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId xmlns="https://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation xmlns="https://www.microsoft.com/sql/reportingservices"><Source>ReportingServicesLibrary</Source><Message msrs:ErrorCode="rsCannotRetrieveModel" msrs:HelpLink="https://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsCannotRetrieveModel&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.3128.0" xmlns:msrs="https://www.microsoft.com/sql/reportingservices">An error occurred while loading the model for the item or data source 'EntityDataSource'. Verify that the connection information is correct and that you have permissions to access the data source.</Message><MoreInformation><Source>Microsoft.ReportingServices.ProcessingCore</Source><Message msrs:ErrorCode="rsErrorOpeningConnection" msrs:HelpLink="https://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsErrorOpeningConnection&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.3128.0" xmlns:msrs="https://www.microsoft.com/sql/reportingservices">Cannot create a connection to data source 'EntityDataSource'.</Message><MoreInformation><Source>Microsoft.AnalysisServices.AdomdClient</Source><Message>Authentication failed.</Message><MoreInformation><Source>Microsoft.AnalysisServices.AdomdClient</Source><Message>No credentials are available in the security package</Message></MoreInformation></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns="https://www.microsoft.com/sql/reportingservices" /></detail>

    And so begins the journey of trying to uncover just which set of credentials isn’t in the right place.  Let’s stop and look at the flow that occurs when you try to load that report:

    PowerView Report hands off to BI Semantic Model (also hosted on SharePoint on the WFE server, running under an application pool)

    BI Semantic Model points to SQL Server Analysis Server Instance on your SQL Server.

    Credential check occurs on SSAS Instance to determine if adequate permissions exist for requesting user.  If so, query runs, data sent back, and report displays.  If not, we get an error like the one above.

    Like many things in SharePoint, this transaction can be governed by Kerberos, which facilitates the passing of credentials from one server or service to another.  It’s easy to confirm if this is a Kerberos issue by changing our report to use a specific username and password.

    In the PowerView gallery, change to the All Documents view, then drop down the menu for your specific report and select Manage Data Sources.

    In my case, the entry for EntityDataSource is shown.  Click on that to get the details.

    Here’s what happens when we use the Windows authentication (integrated) or SharePoint user option and click Test Connection.  No worky.

    pass-through-fail

    Here’s what happens when we put in specific credentials and click Test Connection.

    named-user-success

    So while the symptoms and appears would point to a Kerberos configuration issue, in truth, the solution (in my case) lies with the Claims to Windows Token Service.

    By default, this service is provisioned to run as Local System.  But there is guidance to run this as a domain account.  However, in doing so, additional local security policy changes must be made on the server on which the service is running, in this case, the SQL server.

    The domain account used by the Claims to Windows Token Service needs to be granted the following rights through the Local Security Policy:

    1. Act as part of the operating system

    2. Impersonate a client after authentication

    3. Log on as a service

    You can find these settings under Administrative Tools > Local Security Policy > Local Policies > User Rights Assignment.

    No reboot is necessary for these changes to take effect.  As soon as I returned to my PowerView report and refreshed, the report loaded without error.

    Hat tip to this thread for pointing me in the right direction.

  • SQL Server 2012 SP1 Reporting Services Add-In for SharePoint installation fails with error 1603 – SOLVED!

    SQL Server 2012 SP1 Reporting Services Add-In for SharePoint installation fails with error 1603 – SOLVED!

    Oh, the depths of the nuances found in SharePoint.

    While setting up a new SharePoint 2013 farm that included the Business Intelligence components, we were not able to get the Reporting Services Add-In to successfully install.  The installer would run all the way through, then roll back the entire thing and show this:

    Failed Install

    Looking to the log of the installer, here’s what we found:

    2014-01-08 11:27:54: User: svc_sp2013farmadmin
    2014-01-08 11:27:54: Installing Report Server feature.
    2014-01-08 11:27:58: Beginning uninstall of cab files.
    2014-01-08 11:27:58: Cab files uninstalled successfully.
    2014-01-08 11:27:58: Calling copyappbincontents command.
    2014-01-08 11:28:52: SharePoint Products Configuration Wizard version 15.0.4420.1017. Copyright (C) Microsoft Corporation 2012. All rights reserved.

    Performing configuration task 1 of 3
    Initializing SharePoint Products configuration...

    Successfully initialized the SharePoint Products configuration.

    Performing configuration task 2 of 3
    Installing the application content files...

    Installing the SharePoint Central Administration Web Application content files...

    Installing the SharePoint Web Application content files...

    Failed to install the application content files.

    An exception of type System.NullReferenceException was thrown. Additional exception information: Object reference not set to an instance of an object.

    Total number of configuration settings run: 2
    Total number of successful configuration settings: 1
    Total number of unsuccessful configuration settings: 1
    Successfully stopped the configuration of SharePoint Products.
    Configuration of SharePoint Products failed. Configuration must be performed before you use SharePoint Products. For further details, see the diagnostic log located at D:\SharePointLogs\PSCDiagnostics_1_8_2014_11_27_58_106_1169585150.log and the application event log.

    I swear Object reference not set to an instance of an object is the most infuriating error message ever created.

    We also were getting an event log entry.

    Failed to install the application content files.
    An exception of type System.NullReferenceException was thrown. Additional exception information: Object reference not set to an instance of an object.
    System.NullReferenceException: Object reference not set to an instance of an object.
    at Microsoft.SharePoint.Administration.SPAspConfigurationFile.ApplyActionToXmlDocument(XmlDocument xdAction, XmlDocument xd, String sourceFileName, SupportedXmlDocutmentActions supportedActions)
    at Microsoft.SharePoint.Administration.SPAspConfigurationFile.MergeWebConfig(XmlDocument xdWebConfig, String fileMask)
    at Microsoft.SharePoint.Administration.SPWebService.ApplyApplicationContentToLocalServer()
    at Microsoft.SharePoint.PostSetupConfiguration.ApplicationContentTask.Run()
    at Microsoft.SharePoint.PostSetupConfiguration.TaskThread.ExecuteTask()

    After a bit of searching I finally came across this thread on MSDN, which suggested (of all things) to remove any Comments from the web.config files for my web applications.  Odd, but worth a try.

    I found the following in the web.config for my MySites web application.  I don’t remember commenting that line out, but I went ahead and removed the comments and saved the file.

    <modules runAllManagedModulesForAllRequests="true">
    <remove name="AnonymousIdentification" />
    <remove name="FileAuthorization" />
    <remove name="Profile" />
    <remove name="WebDAVModule" />
    <!--<remove name="Session" />-->
    ...
    </modules>

    I then re-ran the Installer for Reporting Services, and sure enough, it finished successfully.

    Given that the comment is legitimate XML markup it seems odd that the SharePoint Configuration Wizard would choke on it.  But nevertheless, I’m past this issue an on to the next one.