October, 2011 | Peter Heydon ~ Personal Space

Updating SharePoint User Properties in PowerShell

I recently experienced an issue whereby I lost a whole heap of data from the user profile service. The UPS was the data source (custom property), and not available in active directory nor anywhere else. The circumstances leading to the data loss is a separate story, but needless to say it was relatively straight forward to recover the data and re-enter it against the user profiles.

Data Recovery

Luckily I had a relatively recent copy of the Profile database, and the first thing to do was restore this against my test environment. After a quick inspection there were three key tables:

UserProfile – stores information about the user
PropertyList – list of all the properties, including custom ones
UserProfileValue – where the actual data is stored

In this case, the custom property was called DeskNumber. I’ve formatted the SELECT statement for use in importing the data:

USE [Restore_ProfileDB]
 
--NB there is a deliberate typo in the next line, replace "@" with "a"...sorry, seems to be a wordpress bug...
SELECT '"' + up.NTName + '" = "' + c@st(upv.PropertyVal AS v@rchar(20))+ '";'
FROM UserProfile up
INNER JOIN UserProfileValue upv ON upv.recordid = up.RecordID
INNER JOIN PropertyList pl ON pl.PropertyID = upv.PropertyID
WHERE pl.PropertyName LIKE '%DeskNumber%' AND upv.PropertyVal IS NOT NULL

Did I mention that you would only do this exercise on a test environment? 🙂

Here are some sample results:

Data Import

As we should not use SQL to populate the data, PowerShell comes to our rescue. The results from SQL constructs the data in key-value pairs for use in a hash table. Simple copy and paste the results from SQL into the script to populate $userData.

Then run the script, taking care of using the correct:

MySite location
User profile property
Set of users

Add-PSSnapin Microsoft.SharePoint.Powershell
 
$userData = @{
"domain\aas" = "18.61";
"domain\gle" = "M.24";
#...(repeat for each user)
"domain\sch" = "M.25";
}
 
$mySiteHostSite = Get-SPSite "http://mysites"
$mySiteHostWeb = $mySiteHostSite.OpenWeb()
$context = Get-SPServiceContext $mySiteHostSite
$profileManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)
 
foreach ($d in $userData.Keys) {
    $up = $profileManager.GetUserProfile($d)
    $up["DeskNumber"].value = $userData[$d]
    $up.Commit()
    }

This worked great for the custom properties for which we needed to recover. It’s pretty dumb, it doesn’t cater for disabled/deleted accounts so may throw the odd error or two on those. I could have gone for a completely integrated script that did the SQL query too, but this was a once-off exercise and it was quicker to just copy and paste the data over.

Special thanks to Phil Childs @ Get-SPScripts.com, and his script for uploading profile pictures to SharePoint using PowerShell which I loosely used as my guide for putting the latter half together.

Another case of the User Profile Synchronization Service not starting

I had a single server farm where the User Profile Application and Synchronization services were working fine. I was scaling out the environment for a client, and needed to relocate the UPA/UPS services to the new application server.

I proceeded to reprovision the services, and the UPA started fine. However the UPS service wouldn’t start. It wouldn’t get stuck starting, it would try for awhile then fail. A quick look at the ULS showed this error:

Exception trying to write the management agent stack size for the Moss MA. System.UnauthorizedAccessException: Access to the registry key ‘HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\
FIMSynchronizationService\Parameters\PerMAInstance\
MOSS-419f823a-161b-4250-9253-29b98762547d’ is denied.
at Microsoft.Win32.RegistryKey.Win32Error(Int32 errorCode, String str)
at Microsoft.Win32.RegistryKey.CreateSubKey(String subkey, RegistryKeyPermissionCheck permissionCheck, RegistrySecurity registrySecurity)
at Microsoft.Office.Server.Administration.UserProfileApplication.SetupSynchronizationService(ProfileSynchronizationServiceInstance profileSyncInstance)

This was very weird, I had logged in with the farm account that was set as a local administrator, this should have worked. I found in this forum article that I should have rebooted for the correct security tokens to be set. I had only granted administrative privileges before logging in, and once I bounced the server the UPS started fine.

UPS problems post SharePoint 2010 SP1 and June 2011 CU Refresh

Today on a client’s test environment I installed SharePoint 2010 Service Pack 1, and the June 2011 Cumulative Update Refresh. The process went fairly well, and completed our test plan with success. The test plan failed to include any of the service applications, and when I went to try and do a user profile sync, it failed.

I checked the service applications, and noticed that the User Profile Synchronization Service had stopped. I tried starting it up, but after awhile it just stopped again. I peeked at the event viewer which revealed this error:

Cannot open the FIM Synchronization Service database because the database schema version in existing database does not match the required version.

This seemed quite odd, there were no errors or warnings generated from the updates, it appeared that one of the UPS databases didn’t update properly. I had a look in the upgrade log and found this:

[OWSTIMER] [SPUpgradeSession] [DEBUG] [10/12/2011 12:31:22 PM]: NeedsUpgrade [SynchronizationDatabase Name=SP_SyncDB] returned: False.

I imagined that this meant that the SyncDB didn’t need to be upgraded, which seemed peculiar. I did some research online and most recommendations were to remove and re-provision the user profile service. I wasn’t really keen on that idea.

I then came across this forum entry and tuccitown’s response gave me the idea – that the account I was using for the UPS, the spfarm account, didn’t have permissions. I then added the spfarm account to the local administrators group, restarted the server (for good measure, a restart of all services probably would have also worked) and I was able to start the user profile synchronization service.

Interestingly, I had a look at the ULS logs after the service was provisioned, and saw this:

UpgradeMetaverseAndManagementAgent(): Indexing MV distionguishedName. It may take a long time.

So it appears that this service post-update tries to upgrade its components, and because it didn’t have permissions it failed. And yes, it really said distionguisedName.

A second problem arose shortly after. I attempted an incremental sync, and was instantly greeted by an error, and in the ULS it showed:

System.IO.FileLoadException: The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

I suspected that as the user profile sync service had just upgraded, the binaries must be out of whack. Another reboot and I was able to kick off an incremental sync.

To wrap things up, I removed the spfarm account from the local administrators group, gave it one more reboot and everything continued to work!

SSRS Connection Failure

I had setup a test environment with SQL Server Reporting Services in integrated mode with SharePoint 2010. My initial tests had confirmed everything had been setup correctly, including Kerberos. I had even setup a few test reports to a SQL database and everything was fine. I then rolled in a copy of production data and the reports just didn’t want to work.

I attempted to modify the data connection which was looking at a SharePoint list, but wouldn’t connect successfully, instead coming up with the error The report server has encountered a configuration error.

I checked the SSRS logs (default location: C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles) and saw this in the text:

Call to TestConnectForDataSourceDefinitionAction().

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Logon attempt for user ‘tst.sprsunattended.svc‘ failed., Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Log on failed. Ensure the user name and password are correct. —> System.ComponentModel.Win32Exception: Logon failure: unknown user name or bad password

I initially thought this was permissions related, so in an effort to give this account full access to the web application, I discovered something peculiar:

The short account name had been truncated to the first 20 characters! I had seen this before in other scenarios, and this is a short explanation on TechNet:

“The SAM-Account-Name attribute (also known as the pre–Windows 2000 user logon name) is limited to 256 characters in the schema. However, for the purpose of backward compatibility the limit is 20 characters.”

While I had checked the SharePoint service accounts, I didn’t pay too much attention to those for SSRS. I’m just thinking to Buy Windows 7 Ultimate.
Anyways tho, i updated the Execution Account details in the Reporting Services Configuration Manager to the 20 character account name and pressed Apply.

Without a service restart or an IIS reset, I then proceeded back to the data source and retested the connection, this time with success!