Category: SharePoint 2013

SharePoint and User Profiles not updated


Another day, another issue with the SharePoint UPSA and FIM.


This time, it all began with an InfoPath which was fetching the manager of users to send an email. The form had worked correctly for a few months now and suddenly, it began to fail with some users only.

The issue of the form was related to empty Manager entries for those users. While it was correctly populated in the Active Directory, the field was empty in the SharePoint User Profile.


I opened the FIM client GUI to check why the Manager wouldn’t get updated correctly. During the last step where the profiles get exported in the SharePoint Profile DB, a lot of errors were saying “ma-extension-error“. Clicking on it for more details gave us this error message :

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.AggregateException: One or more errors occurred. ---> System.InvalidOperationException: More than one DN specified for the same profile.
 at Microsoft.Office.Server.UserProfiles.ProfileImportExportService.AddDNLookupTable(UserProfileApplicationProxy upaProxy, Guid partitionID, Int64 recordId, String objectType, String distinguishedName)

The total errors listed for the export process reached 1153 :


The error message told me I had to check directly in the Profile database to review what was going on with those “duplicate DN”.

After a few queries in the DNLookup and UserProfile_Full table, I was able to make an initial assumption. Not so long ago, I configured the User Profile sync with the “SharePoint Active Directory Import” built-in engine by mistake. I then re-configured it using the “SharePoint Profile Synchronization” (FIM engine) but a few synchronizations had already been going on. I didn’t thought that the switch would leave any artifacts but it seems that the Profile DB kept some information of the light-weight import.

Some profiles were still in the “DNLookup” table but with a DN value not similar to the one used by the FIM engine : there is no “MVID=” characters in front of the ID. Interestingly, running the below TSQL, it retrieved 1155 results. A pretty close number to the sum of errors (1153) that the FIM client exposed ūüôā

SELECT UPF.NTName, UPF.[PreferredName], DN.DN
FROM [UPA-Profile-DB].[dbo].[UserProfile_Full] as UPF with (nolock)
 INNER JOIN [UPA-Profile-DB].[dbo].DNLookup as DN with (nolock)
 ON UPF.RecordID = DN.RecordId
where DN.DN not like 'MVID=%'

Here are other SQL queries that helped me :

SELECT UPF.[RecordID], UPF.[UserID], UPF.[NTName], UPF.[PreferredName], DN.DN
FROM [UPA-Profile-DB].[dbo].[UserProfile_Full] as UPF with (nolock)
 INNER JOIN [UPA-Profile-DB].[dbo].DNLookup as DN with (nolock)
 ON UPF.RecordID = DN.RecordId
where Manager is NUll


SELECT UPF.[NTName], Count(UPF.NTName)
FROM [UPA-Profile-DB].[dbo].[UserProfile_Full] as UPF with (nolock)
 INNER JOIN [UPA-Profile-DB].[dbo].DNLookup as DN with (nolock)
 ON UPF.RecordID = DN.RecordId
Group BY UPF.NTName
Having Count(UPF.NTName) > 1
Order by UPF.NTName


There is no magic solution here. Each profile that has an entry in the DNLookup table without the “MVID=” prefix must be deleted from the SharePoint User Profile. Then an incremental sync must be run to add back the users.

I scripted the solution as usual ūüôā

Import-module dbatools
$SQL_Instance = ‘[your SQL server instance]’
$UPSA_DBName = ‘[your UPA Profile DB]’

$UPQuery = “SELECT UPF.NTName, UPF.[PreferredName], DN.DN
FROM [$UPSA_DBName].[dbo].[UserProfile_Full] as UPF with (nolock)
INNER JOIN [$UPSA_DBName].[dbo].DNLookup as DN with (nolock)
ON UPF.RecordID = DN.RecordId
where DN.DN not like ‘MVID=%'”

$SPUser_ToRemove = Invoke-Sqlcmd2 -ServerInstance $SQL_Instance -Database $UPSA_DBName -Query $UPQuery -As PSObject

$gc = Start-SPAssignment
$mySiteHost = ($gc | Get-SPSite ‘https://%5Byour mysite host url]’)
$spContext = ($gc | Get-SPServiceContext -Site $mySiteHost)
$UPM = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($spContext)

Foreach ( $User in $SPUser_ToRemove ) {
Try {
Write-host “Removing user from Profile DB : ‘$($User.PreferredName)'”
} Catch {
Write-warning “An error occured while removing user ‘$($User.PreferredName)'”
Stop-SPAssignment $gc

Once the deletion process has finished, run an incremental sync to re-populate the users.


FIM Client GUI is located in¬†‘C:\Program Files\Microsoft Office Servers\15.0\Synchronization Service\UIShell\miisclient.exe‘.
It must be run as the same account declared in the Central admin.

$cred = Get-Credential ‘domain\[service account]’
Start-Process -FilePath ‘C:\Program Files\Microsoft Office Servers\15.0\Synchronization Service\UIShell\miisclient.exe’ -Credential $cred




SSRS Encryption key backup location

Backup the Report Services encryption key:

If you don’t specify the filepath optional argument when using the cmdlet “Backup-SPRSEncryptionKey”, the backup file will be saved in the following folder :


SharePoint, Powershell and FIM

Many, many posts can be found on the Internet regarding issues provisioning the User Profile Synchronization service with one of them being the Holy Grail :
Spencer Harbar’s Bible

Context :

2 of our 3 farms began to fail during the full farm backup procedure we perform every week. After looking in the ULS to check what was going on, it appeared that the UPSA failed to provision and was blocking the whole full farm backup.

The error found in the ULS was :

Event 9i1w : ILM Configuration : Error “ERR_CONFIG_DB”

Solution :

After digging a while, I found out it was linked to the Powershell Module “PSReadline“.

I add installed WMF 5.1 on the servers and installed the module at some point. What I didn’t knew was that the module PSReadline is loaded in all and every powershell console once it has been declared. It doesn’t appears in the profile files but it is still loaded.

This module seems to be in conflict with the UPSA provisioning method and makes the FIM installation crash at provisioning.

Lesson learnt :



SharePoint 2013, AlwaysOn, Availability group and SQL alias


I had a story at my company where I had to migrate the SharePoint databases to a fresh AlwaysOn Availability Group (AOAG) SQL Instance. This move wasn’t an issue until we found out that our backup script (which is performing a “Backup-SPFarm” cmdlet) failed to re-provision the User Profile¬†Synchronization (UPS) Service.


The issue all came down from the¬†fact that the UPS database was now configured with the AlwaysOn feature, therefore, any operations to the DB couldn’t occur anymore.

The error message in the ULS was :

SqlError: ‘The operation cannot be performed on database “UserProfile-Sync-DB” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.’¬†¬†¬† Source: ‘.Net SqlClient Data Provider’ Number: 1468 State: 1 Class: 16 Procedure: ” LineNumber: 5 Server: ‘AG Listener Name’

When the move to the new AlwaysOn SQL server happened, the operation went pretty smoothly since we are using SQL alias. We only updated our current alias name to redirect to the new SQL instance.

Updated configuration :

After some research, we found out that this is a “internet” known issue and can also occur on the Usage and Health database when performing patching of SharePoint farms.

Hence, we decided to modify the configuration and implement a better integration between SharePoint and AlwaysOn by using the SharePoint Database Availability Group cmdets. It will give us the flexibility to manage the databases in the AOAG directly from SharePoint.

In this initial setup, SharePoint isn’t aware of the AlwaysOn service running beneath¬†it. It only sees the SQL Alias and has no way to know which SQL server is running behind the SQL Alias ; it is a regular connection string.

By explicitly declaring to SharePoint that the databases are hosted by a HADR system, SharePoint Admins keep some visibility and control over the AOAG.

Availability Group Listener (AGL) and port attribution

When your AGL is configured to use the¬†default port (1433) and you don’t use SQL alias, you will surely have no issue when configuring your environment.
The troubles arises when you use SQL alias or have a custom port defined for the AGL, let’s say 25066 for the example.

  • When using a SQL alias (redirecting to the AGL), SharePoint will¬†fail retrieving the “Availability Group listeners”.
  • When¬†using a custom port for the AGL, SharePoint will check the database server by using¬†a trimmed version of the data source.
SQL Alias

You can use SQL Alias with Availability Group Listener as long as the SQL alias uses the same DNS entry as the AGL.

Availability Group Custom Port

You must use a SQL Alias to bypass the SharePoint port validation.

Solution (for both scenarios)

In both cases, you must use a SQL alias that mirrors the AGL dns name. For example, if your AGL is “AGListener” on port 25066.

Some in-depth details :

I was able to pinpoint this issue by decompiling the SharePoint assembly and verify the logic behind the powershell cmdlets. I found the root issue in the “UpdateDataSource” method of the Database object.
When trimming the datasource from its port, it then sends the port-less server string to the method “ChangeDatabaseInstance” which¬†itself calls “ValidateDatabaseServer”.
At this point, it won’t be able to validate the connection to the SQL server because¬†of the modified server string.

Cheers !

PS:¬†I wasn’t able to find anything on the web regarding this particular issue except 1 slide that confirmed my troubleshooting.
Ref: РSlide 30

Red X in PowerPivot Management Dashboard

We were in a situation were the “Workbook Activity – Chart” of the PowerPivot Management Dashboard was showing a red X. Our authentication provider is Kerberos so we applied the according SPN to our accounts. We also modified the web.config file of PowerPivot web service to support Kerberos delegation. We were also aware that ADOMD.NET in required on the server running the Central Administration site if you don’t want to have that specific red X. Our farm is under SharePoint 2013 and when you install the spPowerPivot.msi package from SQL Server 2012 it is installing ADOMD.NET package. We decided to install it manually to make sure it’s not an issue with the install but the error was still present. We went through the ULS logs and nothing was showing us that an issue is happening except this entry:

“System.Runtime.InteropServices.COMException: The file you are attempting to save or retrieve has been blocked from this Web site by the server administrators”

Again, when looking at the logs, we validated that the Central Administration site was configured in the “Trusted File Location” of the Excel Service. We decided then to look at the Event Viewer on the server and were hoping something interesting could be present. We were seeing an error but were thinking it is not related since our farm was not still live and our web applications were in NTLM to not break our actual production under SharePoint 2010.


We decided to refresh the page and noticed that the refresh triggered the error again. We then look directly in IIS for the authentication providers of the Windows authentication and found out that only NTLM was present. We added Negotiate and the issue was fixed. The issue was coming from a configuration that didn’t get applied when we changed the authentication provider of the Central Administration site. It was correctly applied in another environment so we probably faced a bug when we did our change. At least, everything is now running well.

ULS Viewer crashes when viewing SharePoint 2013 logs

Hello there,

A quick tip for those who encountered the ULS viewer crash with SharePoint 2013 as my colleagues and me.


  • SharePoint 2013
  • ULS viewer


When opening a SharePoint log containing “verbose” logging with ULS viewer, this one crashes after processing all the data. This makes the tool unusable at some extent.
This issue doesn’t appear if you don’t set the logging level to at least “Verbose“.


Although I don’t know what is going on in the background, the quick fix for this is to disable in the option the feature “correlation tree”.

  1. Open Uls Viewer
  2. Go in menu “Tools/Options…”
  3. Uncheck the option “Enable correlation tree”









Happy troubleshooting !

PowerPivot thumbnails requires Windows Server 2012 GUI


We found out a small issue with PowerPivot 2012 and SharePoint 2013 this week.

You may be aware that the PowerPivot for SharePoint is able to create snapshots/thumbnail previews of your document which is then used to display a nice little image in the carrousel view.
This fancy little feature is generated under the hood by an executable located in the bin folder of the SharePoint installation folder (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\bin).

While it works well under¬†common context, it will fail in a particular¬†scenario.¬†I won’t give you all the different ways to troubleshoot this as Internet already as a ton of article around it but here’s one that is very useful :

Our scenario is as follow :

  • The server hosting SharePoint and Powerpivot is running Windows 2012
  • Windows Server 2012 is¬†configured using MSI (Minimal Server Interface)

In this particular case, the “GallerySnapshot.exe” will crashes when¬†processing the document.





If you look in the “info” file generated, you quickly find out that there is nothing relevant to help you. Here’s the content of the log file :
“<SnapshotCaptureLog serverUrl=”http://<webapp name>” workbookUrl=”http://<site url>/SharePoint/PpowerPivot/Book1.xlsx” fileNameBase=”thumbnail” snapshotCount=”26″ timeout=”600″ />”. That’s it ! Nothing else.

In the “GallerySnapshot.exe has stopped working” error window details, we can see a small hint of what’s going on : “Problem Signature 04: System.Windows.Forms”.

I performed the same exercise on a different server with the same result. I then intalled the windows feature Server Graphical Shell (Install-WindowsFeature Server-Gui-Shell) and restarted the server.

Once rebooted, running the GallerySnapshot.exe again with the same arguments went successfully.

To sum-up, if you require the file preview¬†in a PowerPivot Gallery, you will necessarily have to configure¬†your servers with at least the Graphical User Interface. Let’s hope Microsoft will remove this pre-requisite in a future¬†cumulative update or service pack but until then, forget Windows Server 2012 MSI !

Sad, sad, sad.