SharePoint

Recently I was stuck on trying to figure out why my Power View report was displaying an error when created a sample Hello Word Picnic sample download from Microsoft. https://technet.microsoft.com/en-us/library/hh759325(v=sql.110).aspx

Following Microsofts Tutorial steps, I got to step 2 when creating the data source and testing the connection.  “Call to Excel Services returned an error”

1. In the Data Source Type box, click Microsoft BI Semantic Model for Power View.
2. The connection string for an XLSX file is the full URL to the file, including the file name. For example:

   Data Source=’http://<myserver>/Shared%20Documents/HelloWorldPicnicSQL2012/HelloWorldPicnicPowerViewRTM.xlsx’

Diagnosis:

There are few possible different solutions to the problem when searching google, however none of the solutions on web solved my issue.

Wasn’t even close to solving this issue until I tried to connect to the SQLSERVER\POWERPIVOT instance from another SQL Server and then got the error: The target principal name is incorrect

Using SQL Profiler, I noticed that the POWERPOINT SQL Instance wasn’t receiving any data connection traffic when rerunning the Power View report. A definite sign that SharePoint isn’t able to talk to the Analysis Server!

By now, I’ve check and rechecked my SPNs for MSOLAPSvc.3 and yes they are correct and no they are not corrupted. Ive even deleted the SPNs and recreated them to satisfy my own mind.

99% percent of the time it will likely be your SPNs, but the off chance it could be one of things in the resolution steps.

Errors:

1: When trying to connect to SQL Server Analysis services you receive the error “The target principal name is incorrect (Microsoft.AnalysisServices.AdomdClient)”. (Connecting from a different SQL Server or Management Studio tool)

2: When trying to test a data connection type for Microsoft SQL Server Analysis Services, Power Pivot and tabular models when using Reporting Services Data Source(RSDS) for Power View or Excel Services in SharePoint, you receive the error: “Call to Excel Services returned an error”

3. When you try to load a Power View Report and you receive “We cannot locate a server to load the workbook Data Model”

Resolution:

Temporarily disable the Windows Firewall on the Analysis Server which is hosting the SQL Instance, (don’t be caught out by a silly firewall rule)

In my case, I noticed that the SQL Browser service was running as the Local Service or Local System and it might be a possibility when the data connection queries the SQL Server, the SQL Browser service is not able to discover what dynamic port is being used. Perhaps the environment I was working in and using default SQL Browser Service account may not have right permissions setup or not able resolve the TCP Port number for the SQL instance.

1. Change the SQL Browser Service to use an identity account created in Active Directory.

2. Register the SPN for the SQL Browser Service if it does not exist.

MSOLAPDisco.3/serverHostName
MSOLAPDisco.3/serverHostName.Fully_Qualified_domainName

Results:

Testing the Analysis connection from another SQL Server worked, however the issue with the Power View Hello Word Picnic report still failed with the same Excel related errors.

Restarting the Excel services in SharePoint and reloading the Power View report again, worked the treat.

Lastly, go home and get drunk