SharePoint 2013 correlation id not found on SharePoint farm – Enable job Diagnostic Data Provider: Trace Log and query view ULSTraceLog

Today for one of the SSRS report (SharePoint integrated) the export to Excel failed and it gave the correlation id which we were unable to find on the various nodes of SharePoint farm.

So using Central Admin we enabled job “Diagnostic Data Provider: Trace Log” by navigating to Monitoring => Review Job definitions. On SQL server execute following query against view Logging.dbo.ULSTraceLog:

SELECT [MachineName],
[Area],
[Category],
[Level],
[EventId],
[Message],
[CorrelationId]
FROM ULSTraceLog
WHERE [CorrelationId] = ‘9990009d-cccc-xxxx-07xx-xx99f9999xx9’

sharepoint server User Profile service error – Windows cannot find the local profile and is logging you on with a temporary profile. Changes you make to this profile will be lost when you log off.

Reference 1 and Reference 2

During a review of the SharePoint farm health, we saw a lot of these errors in the event logs. The farm itself was still functional, but these errors were filling the event logs. The account is question was the same one that was used for the IIS website application pools.

  1. Stop the relevant application pools
  2. Stop the IIS Admin Service (in services.msc)
  3. Stop the Web Server
  4. Provision logon locally rights to DOMAIN\AppPoolAccount 

Now open a command window (Start-Run-> type “CMD” ) and in the window type the following:

net localgroup administrators DOMAIN\AppPoolAccount /add

runas /u:DOMAIN\AppPoolAccount /profile cmd

After the second command, you will be prompted to enter the password of the DOMAIN\AppPoolAccount and when you hit enter it will launch a new window. In the window type the following to confirm the user profile directory

echo %userprofile%

5. Launch the User Profiles dialog or check “C:\users” to verify that the directory for your application pool account ID has been created.

Close the second command window, and in the original one type the following to remove the app pool account from the administrators group.  For whatever reason it only needed to be there to create the local profile and it’s not best practices or recommended that you leave the account as an admin on a production farm.

net localgroup administrators DOMAIN\AppPoolAccount /delete

7. Exit the command window

8. Start the Web Server

9. Enable and then Start the IIS Admin Service.

 

Recurring Application server DCOM error The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID {000C101C-0000-0000-C000-000000000046} and APPID {000C101C-0000-0000-C000-000000000046} to the user

Reference

First change Component Service Owner and Permission

In the DCOM Config dialog box, you noticed that the settings are grayed out. In order to enable those settings, perform the following registry modification.

  1. Open Registry Editor by typing “regedit.exe” in Start > Run.
  2. Navigate and select the key “[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\{000C101C-0000-0000-C000-000000000046}]”
  3. Right click and select Permissions.
  4. In the popup windows, click Advanced.
  5. In the Advanced Security Settings windows, select Owner tab and change the owner to Administrators. Click OK to close the window.
  6. In the Permissions screen, select Administrators and check Full Control. Click OK to close the screen.
  7. Close the registry editor.
  8. Restart the machine.

This error is being logged into the System event logs in Event Viewer due to inadequate permission for the farm admin account on the DCOM component 000C101C-0000-0000-C000-000000000046. In order to change the permission settings, perform the following steps:

  1. Open Component Service by clicking Start > Administrative Tools > Component Services.
  2. Navigate and expand the node Component Services > Computers > My Computer > DCOM Config.
  3. Right Click the sub node “000C101C-0000-0000-C000-000000000046” and select “Properties”.
  4. On the Properties dialog box, select Security tab. In most cases you will notice that the settings are grayed out. This is because the logged in user doesn’t have enough permissions on this component to change the settings. In order to provide this permissions, perform the steps described above “Change Component Service Owner and Permission”. You may need to restart the server after doing this.
  5. Once you set up the permission mentioned in previous step, in the Launch and Activation Permissions section in Security tab, select Customize option and click Edit.
  6. In the popup window, add the farm admin user account and check Local Launch and Local Activation permissions. In case everything is checked, uncheck the Remote launch and Remote Activation.
  7. Restart IIS and SP Timer. If possible, restart the server itself. 

Powershell script to list all the SharePoint 2013 integrated SSRS 2012 subscription details

MSDN reference

Using PowerShell script we can find all the SharePoint 2013 integrated  SSRS 2012 subscription details:

Script:

# Parameters
# server – server and instance name (e.g. myserver/reportserver or myserver/reportserver_db2)

Param(
[string]$server,
[string]$site
)

$rs2010 += New-WebServiceProxy -Uri “http://$server/_vti_bin/ReportServer/ReportService2010.asmx” -Namespace SSRS.ReportingService2010 -UseDefaultCredential ;
$subscriptions += $rs2010.ListSubscriptions($site); # use “/” for default native mode site

Write-Host ” ”
Write-Host “—– $server’s Subscriptions: ”
$subscriptions | select Path, report, Description, Owner, SubscriptionID, lastexecuted, Status

powershell c:\scripts\ListAll_SSRS_Subscriptions.ps1 "[server]/_vti_bin/reportserver" "http://[server]" 

How to find out the application pool account that runs Excel Services Application

If you do not know what account is running the Excel Services application pool in your farm, use the following procedure to determine the account. If you know the account, skip this procedure.

To determine the Excel Services application pool account

  1. On the SharePoint Central Administration Web site home page, click Security.
  2. On the Security page, under General Security, click Configure service accounts.
  3. On the Service Account page, in the Credential Management section, from the drop-down list, select the application pool that runs Excel Services Application.

    When this option is selected, the name of the Excel Services service application appears in the box underneath the drop-down list. The account shown in the Select an account for this component dropdown list is the Windows identity that you need to add as an Analysis Services administrator.

  4. Click Cancel.

Technet Reference

How to configure EffectiveUserName in SharePoint 2013

EffectiveUserName is a SQL Server Analysis Services connection string property that contains the name of the user who is accessing a report or dashboard.

In SharePoint Server 2013, we can use this property in conjunction with Excel Services or PerformancePoint Services to pass the identity of the user who is viewing the report or dashboard to SQL Server Analysis Services.

This allows per-user identity WITHOUT the need to configure Kerberos delegation

Using the EffectiveUserName feature with Excel Services requires the following:

  • The Excel Services application pool account must be an Analysis Services Administrator.
  • You must enable the EffectiveUserName option in Excel Services Global Settings.
  • You must select the Use the authenticated user’s account option in the Excel Services Authentication Settings in Excel.

Use the following procedure to enable the EffectiveUserName feature in Excel Services.

To enable EffectiveUserName in Excel Services

  1. In Central Administration, in the Application Management section, click Manage service applications.
  2. Click the Excel Services service application.
  3. Click Global Settings.
  4. On the Excel Services Application Settings page, in the External Data section, select the Use the EffectiveUserName property check box.
  5. Click OK.

.

Configure Analysis Services access

 Using the EffectiveUserName feature requires that the account that is running the Excel Services application pool be an Analysis Services administrator.

If you do not know what account is running the Excel Services application pool in your farm, use the following procedure to determine the account.

If you know the account, skip this procedure.

To determine the Excel Services application pool account

  1. On the SharePoint Central Administration Web site home page, click Security.
  2. On the Security page, under General Security, click Configure service accounts.
  3. On the Service Account page, in the Credential Management section, from the drop-down list, select the application pool that runs Excel Services Application.

    When this option is selected, the name of the Excel Services service application appears in the box underneath the drop-down list. The account shown in the Select an account for this component dropdown list is the Windows identity that you need to add as an Analysis Services administrator.

  4. Click Cancel.

You must add the Excel Services application pool account as an Analysis Services administrator. Use the following procedure to add this account as an administrator in Analysis Services.

To add an Analysis Services administrator

  1. In SQL Server Management Studio, connect to Analysis Services.
  2. Right click the Analysis Services top node, and then click Properties.
  3. On the Security page, click Add.
  4. Type the name of the account that runs the Excel Services application pool, and then click OK.
  5. Click OK.

Technet reference – Use Analysis Services EffectiveUserName in SharePoint Server 2013

Technet Reference – how to determine the account that is running the Excel Services application pool