harbar.net component based software & platform hygiene

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:

image

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:

image

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.

.

Feedback

Gravatar

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

Hi,

Please put a readme.txt file which describes which describes how to run the 3 provided scripts.

I am still having problem in running these above 2 scripts.

Please correct me in the steps.

1. Change all the names and path in the scripts
2. Run SharePoint PowerShell Console as Farm Account
3. Go to the directory where above 2 PS Scripts resides
4. Execute 'SECOND' Script which is going to call the 'FIRST' script
5. A Windows Credential window poped-up and I provided the Password of the Farm Account
6. HERE I AM EXPECTING THAT EVERYTHING SHOULD WORK AND UPSA will be created and BECAUSE OF Read-Host Command; the Second window stayed showing the details of my User Profile Service
BUT UNFORTUNATELY THE SECOND SP PowerShell Window CLOSED (YES CLOSED) without any message first SP PowerShell Window finished its processing and nothing was created.

The scripts I ran in SP PowerShell Console run as Farm Account. Please note as My User Profile Service is already started; I commented out that part

# UPA specifics
#$upaInstanceName = "User Profile Service"

# Path of UPA Creation PowerShell
$ScriptFile = "D:\SharePoint_Install\User Profile Service Script\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


The above Script is supposed to open another SP PowerShell Console Window ad execute UPACreation.ps1 script (code below)

Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue

# App Pools
$saAppPoolName = "(PROD) UPSA"

# UPA specifics
$upaName = "(PROD) User Profile Service Application 1"
$upaProfileDBName = "Prod_UPSA_Profile_DB_1"
$upaSocialDBName = "Prod_UPSA_Social_DB_1"
$upaSyncDBName = "Prod_UPSA_Sync_DB_1"

$upa = New-SPProfileServiceApplication -Name $upaName -ApplicationPool $saAppPoolName -ProfileDBName $upaProfileDBName -SocialDBName $upaSocialDBName -ProfileSyncDBName $upaSyncDBName


Write-Host "Creating $upaName Application & Proxy..."
New-SPProfileServiceApplicationProxy -Name "$upaName Proxy" -ServiceApplication $upa -DefaultProxyGroup


# Check it worked
#Get-SPServiceApplication | ? {$_.Name -eq "(PROD) User Profile Service Application 1"}

Read-Host


Please help me in this regard

Techie

2/12/2013 10:05 AM | Techie
Gravatar

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

Techie:

if you are being prompted for credentials then the Farm Account is NOT a local machine administrator and the scripts will NOT work.

You should receive a UAC prompt, not a credential one.

Ensure the Farm Account is a local admin (that's needed for UAC elevation) before running these scripts

s.

2/14/2013 11:34 PM | harbars

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 1 and 6 and type the answer here: