Start here

The target principal name is incorrect (Microsoft.AnalysisServices.AdomdClient)

The-target-principal-name-is-incorrect-Microsoft-AnalysisServices-AdomdClient

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

Reporting Services ‘New Document’ requires a Microsoft SharePoint Foundation-compatible application and web browser…

Once again I’ve exhausted all possible searches on google to find an answer as to why I am getting an error when I click new for either Report Builder report or new data source content types in my BI Site using the out of the box SharePoint Business Intelligence site template.

Ok, so all of my Reporting Services features are activated as per the default installation and the SharePoint Add-in has been installed/reinstalled plenty of times and this makes no difference as suggested by other forums.

Error:

RSContenttypes

‘New Document’ requires a Microsoft SharePoint Foundation-compatible application and web browser…

NewDocError

Resolution:

STEP 1:

Download or copy the rssharepoint.msi to your local drive and extract the msi package.

Msiexec.exe /i rsSharePoint.msi SKIPCA=1
  1. Open a command prompt with administrator permissions and run a files only installation as described in the previous section.
  2. Find the rsCustomAction.exe file on the file system. This file is copied to your computer by the Setup program. The file will be located in the %Temp% directory. To get the path information for this file, Type the following from the command prompt:
  3. CD %temp%.The file should be located in: \Users\<your name>\AppData\Local\Temp
  4. Type the following command. This configuration step will take several minutes to finish. The W3SVC service will be restarted during this process. Several Status messages will be displayed as the program copies files, registers components, and runs the SharePoint Product Configuration Wizard.
  5. rsCustomAction.exe /i

SharePoint Products Configuration Wizard version 15.0.4420. 1017.

Copyright (C) Microsoft Corporation 2012. All rights reserved.

copyappbincontents command completed successfully.

Adding ReportServer feature to farm.

Installed ReportServer feature.

Adding ReportServer feature to farm.

This process can take up to about 5-10mins, so be patient.

STEP 2:

Once this has reinstated the Reporting Services content types, proceed to the next step of deleting the Reporting Services content types in your document library.

Deleting Reporting Content Types and Add them back into your document library again.

1. Browse to your document library and go to the Library settings

2. We will want go through the same process for all three content types listed. Click on Report Data Source

RSContenttypes-list

3. Proceed to Delete the content type

RSContenttypes-DeleteRSContenttypes-Delete-Yes

5. Add the Content Type by choosing the ‘add from existing content types’RSContenttypes-Add

6. Select the SQL Server Reporting Services Content Types Group, and Add the Report Data Source and click OK.

RSContenttypes-Add-Selection

Browse or refresh your Reporting/BI site and choose the Report Data Source or the Content type that you have just refreshed again.

In my case I had to perform step 1 first before this would work. It would be worth while testing step 2 before going through the reinstall and extraction process to force the content types to install and activate. Step 1 will also modify and add the Reporting Services extensions in the DOCICON.XML for you.

SharePoint 2013 – OWA Server 2013 “To start seeing previews, please log on by opening the document.”

Interesting issue when clicking on the preview window for all office documents in SharePoint. “To start seeing previews, please log on by opening the document”

This functionally worked fine when accessing from inside the network, but not externally.

previewerror

Investigation:
Using SharePoint ULS and Fiddler, I captured web requests from both Internal and External requests and compared them. The difference with the External URL request was simply not authenticating my logon credentials the to Wopiframe.aspx page and therefore my access was an anonymous.

Since the request was anonymous, it makes perfect sense why I’m not authorize to preview the document. After googlizing this issue, there was no solution out on the web.
I did find some really good explaining around the authentication behaviors with the Wopiframe.aspx which does not force the user to authenticate whereas the Wopiframe2.aspx does, so this was a clue for the next step.

Refer to blog by Wictor Wilen, he has more specific explanation around the authentication behaviors for the WopiFrame.aspx and Wopiframe2.aspx.
http://www.wictorwilen.se/an-explanation-to-to-start-seeing-previews-please-log-on-by-opening-the-document-in-sharepoint-2013

 


Results of the External web request which are not being authenticated by SharePoint.

External Request:

https://mysharepointsite.com/_layouts/15/WopiFrame.aspx?sourcedoc=%2FTest%2Exlsx&action=interactivepreview&wdSmallView=1)
Authentication Authorization agb9s Medium Non-OAuth request. IsAuthenticated=False, UserIdentityName=, ClaimsCount=0

 

Internal Request:
https://mysharepointsite.com/_layouts/15/WopiFrame.aspx?sourcedoc=%2FTest%2Exlsx&action=interactivepreview&wdSmallView=1)). Parent No
Authentication Authorization agb9s Medium Non-OAuth request. IsAuthenticated=True, UserIdentityName=0#.w|domain\user, ClaimsCount=75


 

If was to take the external web request that is anonymous and alter the request to use the WopiFrame2.aspx and refreshed the page, this allow my login to authenticate and display the document in the preview. Success!
eg.

https://mysharepointsite.com/_layouts/15/WopiFrame2.aspx?sourcedoc=%2FTest%2Exlsx&action=interactivepreview&wdSmallView=1

Resolution:

There are two ways to integrate a solution depending on how complex your network setup. Firstly, using a URL Rewrite module or load balancer device can update the external request to replace the WopiFrame to WopiFrame2 in the URL.

Alternatively, if there is no option to use an network device to rewrite the URL request then a manual edit to update the filepreview.js in “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\LAYOUTS” directory on each Web server in the farm can do the rewriting for us.

1. Find method embeddedWACPreview.onVisible=function()
2. Backup the filepreview.js file first before modification
3. Change the method to below:

embeddedWACPreview.onVisible=function(){a:;var serverRedirectedURL = this.fpListItem.ServerRedirectedEmbedUrl;var customizedURL = serverRedirectedURL.replace("WopiFrame", "WopiFrame2");var b=customizedURL+"&wdSmallView=1",e=String(this.getWidth())+"px",d=String(this.getHeight())+"px",a=[];a.push('

‘);

4. IISRESET

Note: Always consider any modifications changes to the your environment, as it can leave your farm in a unsupported state. Use an IIS rewrite module or external device were possible.

SSRS SharePoint 2010 to 2013 Migration Microsoft.SharePoint.SPException: User cannot be found

Recently I did a SP2013 Enterprise and SQL Server 2012 with Report Services Integrated mode setup. The SharePoint environment is setup to support the Claims Based Authetication and Kerberos Authentication.

The Reporting content from the Source farm (SharePoint 2010) was successfully migrated over a Test SP2013 SharePoint farm. Carried out the required steps to upgrade the SP2010 content to SP2013 compatibility and convert the Web Application to Claims Based Authentication as Microsoft advises.

Everything was looking good, however I found reports in the some site collections migrated perfectly fine whereas some reports in subsites gave me this error: Microsoft.SharePoint.SPException: User cannot be found

I managed to resolve this through the steps below. It would be interesting to know what exactly I did differently the first time, so I can pin-point the resolution.

Resolution Steps:
Interestingly, I noticed if went to the properties on the Reporting Services Service Application and then wait for the properties page to appear, I clicked Ok to close it. Reporting Services took a while longer to process whatever it needs to do and when I run the report again it then would say accessed denied to running the report rather than the exception error ‘User not Found message’. I had permissions to the reports and access denied was to the reports was not the case.

Temporary fix:
I found downloading a copy of the report and re-uploading again fixed the problem but I wasn’t prepared to do this for hundreds of reports. I suspected the the problem started when converting the SP2013 Web Application from Classic to Claims or when performing the visual appearance upgrade from SP2010 to SP2013.

What resolved the issue for me was to re-migrate the SP2010 content again from the source farm (SP2013) and performed the following:

I Kept the existing SP2013 Claims Web Application that I had been working with, but created a dummy SP2013 Web Application in Classic Mode in the same SharePoint farm and dismounted the content database which contained the broken reports.

Created a Dummy Classic Mode Web Application:
New-SPWebApplication -Name “Migration Site” -ApplicationPool “Report Application Pool” -AuthenticationMethod “NTLM” -ApplicationPoolAccount (Get-SPManagedAccount “domain\SSRS_ServiceAP”) -Port 80 -URL “yourdummywebapp”

Mounted the SQL backup copy of the SharePoint 2010 Content Database which contains the reports to be migrated:
Mount-SPContentDatabase -name SP2010_Content_Database -WebApplication http://yourdummywebapp

Setup the site Administrator: Ensured I had site admin permissions over the restored content I then tested the content for integrity, by using the SharePoint PowerShell Export-SPWeb. Exporting all content from the top level of the web application content and searched the export.log for any errors.

Set-SPSite -Identity “yourdummywebapp” -SecondaryOwnerAlias “domain\youraccount”

Export the Content and Analyse:
Export-SPWeb -Identity http://yourdummywebapp-Path C:\SPExport\export.cmp -ItemUrl http://yourdummywebapp -IncludeUserSecurity -Verbose

Grant Reporting Services Service Account full access to the Web application
$w = Get-SPWebApplication -Identity http://yourdummywebapp/
$w.GrantAccessToProcessIdentity(“domain\SSRS_ServiceAP”)

IISRESET
Performed a IISRESET and went to the properties on the Reporting Services Service Application and clicked OK – as mentioned above.

At this point validated all reporting content is working under the dummy Classic Mode Web Application with stored credentials while the content is still in SP2010 visual appearence.

Converted the Dummy SP2013 Classic Web Application to Claims Based Authentication

$WebAppName = “yourdummywebapp”
$wa = get-SPWebApplication $WebAppName
$wa.UseClaimsAuthentication = $true
$wa.Update()

Migrate Users to Claims Authentication
$account = “domain\youraccount”
$account = (New-SPClaimsPrincipal -identity $account -identitytype 1).ToEncodedString()
$wa = get-SPWebApplication $WebAppName
$zp = $wa.ZonePolicies(“Default”)
$p = $zp.Add($account,”PSPolicy”)
$fc=$wa.PolicyRoles.GetSpecialRole(“FullControl”)
$p.PolicyRoleBindings.Add($fc)
$wa.Update()
$wa.MigrateUsers($true)
$wa.ProvisionGlobally()

IISRESET
Performed another IISRESET and went to the properties on the Reporting Services Service Application and clicked OK again – as mentioned above.

Testing
Tested the Reporting content again, – The reports are still running successfully without the user not found, and the last thing to do was to perform the visual upgrade to SP2013.

Move that Database back and Test
Dismounted the content database from the dummy SP2013 Web Application and remounted it back over the Original SP2013 Claims Web application that I initially had issues with the reports.

Perform the Visual upgrade (optional)
Performed the visual upgrade and tested the reports which had errors with, this time they worked fine.

Internal Exception Error – SharePoint Enterprise Search Service

Found a number of postings to resolve this error in the ULS log… When you try to search for results in the search center and you experience an error ‘Interal Exception Error’

For me, the issue wasn’t any thing I found searching around in google blogs or forums, lots of suggestions such as activing search features and creating new search sites etc…, or IIS resets, reboots and I dont need to reprovision the Search service application!

Error

CoreResultsWebPart::OnInit: Exception initializing: System.NullReferenceException: Object reference not set to an instance of an object

Diagnostics

Had this issue twice with two different methods of remedies. I have found the simple fix by finding the application pool account that is hosting the Search admin query component to be in the stopped state in IIS server and when its started it fails again after a new search query.

Resolution

1. Simply changing/generating a new password in SharePoint managed accounts section for the search service account running under the admin or query application pool account (or the account that is stopped in IIS) and then performing an IISReset.

2. Another resolution is to create new application pools in SharePoint for the search service and its up to you if you want to specify new identity accounts or use the same ones. Once the new application pools are created for the search service application you can verify in IIS that they are running in the started state and while you are in IIS, check the search service guids exists under the SharePoint Web Services virtual directory and the SearchService and web.config file are created.

Once this is done, IISRESET or Reboot.

SharePoint 2013 Integrated Mode, Reporting Service Application (Login failed for user)

Reporting Service application hosted in SharePoint 2013 application server causing endless fail logins when running rendering a report.

I couldn’t find any quick and easy Microsoft documentation or information round setting up Kerberos, so here is my configuration to get this working successfully.

This blog assumes you have already configured your Web Applications and SQL Server service accounts and SharePoint Computer accounts for Kerberos Authentication

Error:

Cannot create a connection to data source ‘DataSource’. —> System.Data.SqlClient.SqlException: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

SharePoint Setup:

SharePoint Web Application (that is associated with the Reporting Services Application proxy) is configured to Claims Based Authentication and setup for Windows Authentication / Negotiate as the authentication provider.

Reporting Service Application

Specified a service identity account (SPSSRSAppPool) for host the Reporting Service Application IIS processes.

Configure Kerberos for Reporting service account for constrained delegation

  1. On your domain controller run, setspn -a http/ domainname\SPSSRSAppPool
  2. Delegation tab in AD for SPSSRSAppPool account (Trust this user delegation to specified services only)
  3. Use any authentication protocol
  4. Specify all your service accounts running under your Web applications, every site should be setup with HTTP SPNs (if not then register them using setspn and configure your sites for Kerberos authentication)
  5. Specify your SQL Server MSSQLSvc SPNS for your SharePoint database server (if not then register them using setspn and configure your SharePoint database server)

Claims to Windows Token Services (SharePoint Service is started)

Specified a service identity account (SPC2WTS) to run the Claims services on every farm server except Web Apps and SQL Server.

Setup the server security for the Claims account under the local security policy on each farm server (except Web Apps, SQL Server)

  1. Membership in the local Administrators Group on all farm servers (except Web Apps, SQL Server)
  2. Grant the rights to impersonate a client after authentication
  3. Logon as a service
  4. Act as a part of the operating system

Configure Kerberos for Claims service account for constrained delegation

  1. On your domain controller run, setspn -a http/ domainname\spc2wts
  2. Delegation tab in AD for SPC2WTS Account (Trust this user delegation to specified services only)
  3. Use any authentication protocol
  4. Specify all your service accounts running under your Web applications, every site should be setup with HTTP SPNs (if not then register them using setspn and configure your sites for Kerberos authentication)
  5. Specify your SQL Server MSSQLSvc SPNS for your SharePoint database server (if not then register them using setspn and configure your SharePoint database server)

Configure Reporting Service Application to Kerberos Authentication:

Modify the RSReporting.config and add RSWindowsNegotiate for the Authentication Provider.

RSReporting file is located under the Application farm server or which ever server you Installed the SSRS service on.

  1. C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\WebServices\Reporting (SharePoint 2013)
  2. C:\Program Files\Common Files\microsoft shared\Web Server Extensions\14\WebServices\Reporting (SharePoint 2010)

Make sure your Authentication section in your RSReporting.config file looks like this:

<Authentication>
<AuthenticationTypes>
<RSWindowsNegotiate/>
</AuthenticationTypes>
<EnableAuthPersistence>true</EnableAuthPersistence>
</Authentication>

Perform IISRESETS or Reboot.

Missing Report Server Content Types (rsSharePoint Add-in) – SharePoint 2013

After 4hrs searching the web for an answer why I couldn’t find the three report content types were not showing up after installing the Reporting server Add-in for SharePoint 2013 and finally found a way to bring them back.

I noticed a change on how you install the RS SharePoint add-in for SharePoint 2013. Its now a component in SQL Server 2012 rather than a SharePoint prerequisite via the installer and my experience having to use the SQL Server 2012 installation wizard to add/remove this component was a slow and painful and didn’t fix this issue.

So i found a quicker and better way to bring them back!

Note: When you install RS SharePoint Add-in feature to your Web Front end servers using the SQL Server 2012 and Service Pack 1 media you wont see any errors about the content types not installing correctly.

Symptoms:

No Report Builder Model, Report Builder Report or Report Data Source content types listed when you Add Content Types to your document library and the Report Server Integration Feature is Activated

You at attempt to deactivate and re-active the “Report Server Integration Feature” (E8389EC7-70FD-4179-A1C4-6FCB434) for your site collection feature via PowerShell or the GUI.

Enable-SPFeature -Identity E8389EC7-70FD-4179-A1C4-6FCB434
2D7A0 -Url http://sharepoint/

Error:
Enable-SPFeature : The Feature is not a Farm Level Feature and is not found in
a Site level defined by the Url http://sharepoint

Server Topology:

  • SQL Server 2012 with Service Pack 1 (hosts SharePoint databases)
  • SharePoint 2013 Application Server (Hosts Reporting services service application)
  • SharePoint 2013 Web Front end Server (hosts the all content websites)

Resolution:

1. So what is extracted the file rsSharePoint.msi from the SQL Server 2012 Service Pack 1 x64 binaries.

  • Name: rssharepoint.msi
  • Author: Microsoft Corporation
  • Revision Number: {D99F505C-205A-4063-B08B-1769D140AA42}

2. Copy the rssharepoint.msi to your local drive and extract the msi package.

Msiexec.exe /i rsSharePoint.msi SKIPCA=1
  1. Open a command prompt with administrator permissions and run a files only installation as described in the previous section.
  2. Find the rsCustomAction.exe file on the file system. This file is copied to your computer by the Setup program. The file will be located in the %Temp% directory. To get the path information for this file, Type the following from the command prompt:
  3. CD %temp%.The file should be located in: \Users\<your name>\AppData\Local\Temp
  4. Type the following command. This configuration step will take several minutes to finish. The W3SVC service will be restarted during this process. Several Status messages will be displayed as the program copies files, registers components, and runs the SharePoint Product Configuration Wizard.
  5. rsCustomAction.exe /i
    SharePoint Products Configuration Wizard version 15.0.4420. 1017.

    Copyright (C) Microsoft Corporation 2012. All rights reserved.

    2012-12-20 16:25:58: copyappbincontents command completed successfully.

    2012-12-20 16:26:19: Adding ReportServer feature to farm.

    2012-12-20 16:26:20: Installed ReportServer feature.

    2012-12-20 16:26:40: Adding ReportServer feature to farm.

    2012-12-20 16:26:40: Installed ReportServer feature.

    2012-12-20 16:27:00: Adding ReportServerStapling feature to farm.

    2012-12-20 16:27:00: Installed ReportServerStapling feature.

    2012-12-20 16:27:20: Adding ReportServerStapling feature to farm.

    2012-12-20 16:27:20: Installed ReportServerStapling feature.

    2012-12-20 16:27:40: Adding ReportServerItemSync feature to farm.

    2012-12-20 16:27:40: Installed ReportServerItemSync feature.

    2012-12-20 16:28:00: Adding ReportServerItemSync feature to farm.

    2012-12-20 16:28:00: Installed ReportServerItemSync feature.

    2012-12-20 16:28:20: Adding ReportServerCentralAdmin feature to farm.

    2012-12-20 16:28:23: Installed ReportServerCentralAdmin feature.

    2012-12-20 16:28:43: Adding ReportServerCentralAdmin feature to farm.

    2012-12-20 16:28:43: Installed ReportServerCentralAdmin feature.

    This process can take up to about 5-10mins, so be patient.

    IISRESET and you should see the content types have reappeared.

Follow

Get every new post delivered to your Inbox.