Avoiding the Default Schema issue when creating the User Profile Service Application using Windows PowerShell

Print | posted on Saturday, October 30, 2010 12:38 PM

As you may already be aware there is a bug when creating the User Profile Service Application (UPA) using Windows PowerShell. This bug prevents the provisioning of the User Profile Synchronization service instance (UPS).

In a nutshell, when using Windows PowerShell to create a UPA the Default Schema of the Farm Account on the Sync DB is set incorrectly, and this will lead to an error during provisioning of the UPS later. The following error will be logged to the Application Event Log:

“IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[StringSchemaCollection]') DROP XML SCHEMA COLLECTION dbo.StringSchemaCollection: Specified collection 'StringSchemaCollection' cannot be dropped because it is used by object 'CONTOSO\FarmAccount.GetObjectCurrent'.”

The problem here is that when we create a UPA using Central Administration, a dbo User is created using the Farm Account and it’s Default Schema is set to dbo, as you can see below:

However if we use Windows PowerShell to create a UPA, and are not running the PowerShell as the Farm Account, the dbo User will be created using the account we run the PowerShell as. The Farm Account is also added, but the Default Schema is set to the Farm Account, instead of dbo:

This is what leads to the inability to drop objects and the provisioning of UPS either via Central Administration or PowerShell cannot proceed.

Now, as I mentioned this is a bug. No doubt about that. However it’s a bug with a workaround.

The workaround is to the run the PowerShell cmdlet for creating the UPA as the Farm Account! We would also need this PowerShell session to be run under UAC elevation of course. By logging on as the Farm Account to run this cmdlet, we are essentially replicating the behaviour of Central Administration, which of course runs under the Farm Account.

Yup, I know what you’re thinking! “You must be crazy!!”. Of course we should NEVER be logging on as the Farm Account to do anything. There are some worst practices than logging in interactively as the Farm Account, but not many. It’s a solid, “NO” to that.

Another way around this is to manually fix up the default schema of the Farm Account on the Sync DB using SQL Server Management Studio or indeed PowerShell as I have previously detailed. The trouble with this approach, is that making direct modifications to the database in this manner is not supported.

Therefore we are faced with a conundrum:

1. Log on as the Farm Account and under UAC elevation run the Windows PowerShell cmdlet to create the UPA. One of the top five worst practices for deploying and operating SharePoint.
2. Fix the Default Schema for the Farm Account manually. An unsupported change.

Option 1 is frankly untenable as if we are looking at some automated build script we have to run the whole thing as the Farm Account, which is very very silly. Furthermore we have to grant rights to the Farm Account to do this which are not required until we provision the UPS service instance anyway. Just because it works, doesn’t mean we should. It’s just bad. End of story.

Option 2 is a problem of course, because it’s unsupported. Now we might argue that we know what we are doing, and therefore this “little” change is OK. But the bottom line is that making direct database changes like this should not be encouraged. If it’s OK for this, customers will assume it’s also OK for other “little” things.

This led me to investigate another approach. I thought, there must be a way to run PowerShell as another user. The idea being that within whatever script we are running, for the part where we need to create the UPA, run that as the Farm Account, and then go back to the regular identity for the rest of the script. I had a play around and discovered there are numerous ways to do this. I came up with two and my good buddy Gary Lapointe came up with three others. The best one we found is to make use of the Start-Job cmdlet, which allows us to pass in a Credential.

Now we have a third way. And this way is a) fully supported and b) works. It’s far from ideal and if it was up to me this bug would be fixed. but in the meantime, this is how you should be creating the UPA from Windows PowerShell to avoid the default schema issue:

$sb = {       	
Add-PSSnapin Microsoft.SharePoint.PowerShell          
$saAppPool = Get-SPServiceApplicationPool "SharePoint Web Services Default"
$upa = New-SPProfileServiceApplication -Name "User Profile Service Application" `
                                       -ApplicationPool $saAppPool `
                                       -ProfileDBName "UPA1_Profile" `
                                       -SocialDBName "UPA1_Social" `
                                       -ProfileSyncDBName "UPA1_Sync" `
                                       -ErrorAction SilentlyContinue -ErrorVariable er } 

$cred = Get-Credential "sharepoint\spfarm" 
$job = Start-Job -Credential $cred -ScriptBlock $sb | Wait-Job 

It’s not the best solution (that would be a fix for this bug), but its the best we have in lieu of a fix, which avoids the spectre of supportability. But remember, the farm account must have rights on the machine to do this – we need them anyway for provisioning UPS later, so it shouldn’t be an issue.

A couple shout outs to Todd Carter (PFE extraordinaire) for validating my thinking here, and Gary Lapointe (automation wizard) for looking at the various options for running script as a different user. If you run into either of these chaps at some event you should be buying them beer.

[update]

As covered in my SPC session on UPA in Anaheim, 2011, the above script is great if you are in a non-UAC environment. However it will fail if UAC is enabled. Sadly there is no way to use Start-Job and elevate permissions. Therefore the following script is required (which works with UAC) but will require user intervention (to accept elevation).

First we create a script which includes the neccessary call to New-SPProfileServiceApplicationProxy. Nothing fancy here, but we must include adding the SharePoint snapin, as this will be called in a new process, and I also stick a Read-Host at the end so I can keep the window this opens on screen for a few moments (until I hit enter)

Add-PSSnapin Microsoft.SharePoint.PowerShell

# App Pool
$saAppPoolName = "SharePoint Web Services Default"

# UPA specifics
$upaName = "User Profile Service Application"
$upaProfileDBName = "ContosoFarm_Profile"
$upaSocialDBName = "ContosoFarm_Social"
$upaSyncDBName = "ContosoFarm_Sync"


# Grab the Appplication Pool for Service Application Endpoint
$saAppPool = Get-SPServiceApplicationPool $saAppPoolName

<# Creates UPA Service Application & Proxy, and User Profile Service Instance
     If omitted, -ProfileSyncDBServer, -SocialDBServer & -ProfileDBServer are the SharePoint Default DB Server
     If omitted, -SyncInstanceMachine is the local machine 
#>
Write-Host "Creating $upaName Application & Proxy..."
$upa = New-SPProfileServiceApplication -Name $upaName -ApplicationPool $saAppPoolName -ProfileDBName $upaProfileDBName -SocialDBName $upaSocialDBName -ProfileSyncDBName $upaSyncDBName
New-SPProfileServiceApplicationProxy -Name "$upaName Proxy" -ServiceApplication $upa -DefaultProxyGroup

# Check it worked
Get-SPServiceApplication | ? {$_.TypeName -eq "User Profile Service Application"}

Read-Host

Then we start up another process under the Farm Account credentials (after prompting for it's password), which then calls Start-Process again to elevate permissions under UAC.

# UPA specifics
$upaInstanceName = "User Profile Service"

# Path of UPA Creation PowerShell    
$ScriptFile = "C:\UPACreation.ps1"

Write-Host "Starting the $upaInstanceName Instance..."
Get-SPServiceInstance | where-object {$_.TypeName -eq $upaInstanceName} | Start-SPServiceInstance

# Get the Farm Account Creds
$farmAcct = (Get-SPFarm).DefaultServiceAccount
$cred = Get-Credential $farmAcct.Name

# Create a new process with UAC elevation
Start-Process $PSHOME\powershell.exe -Credential $cred -ArgumentList "-Command Start-Process $PSHOME\powershell.exe -ArgumentList `"'$scriptfile'`" -Verb Runas" -Wait

Obvioulsy you will need to update the names and the path to the first script file to suit your environment.

There you have, a way to work around the default schema issue without having to interactively log on as the farm account, which of course is really daft!

You can download this approach (along with a script for starting the UPS service instance) here.

.