Over the last two days I have had a hard time setting up User Profile Sync on SharePoint 2010. I chose not to run the wizard or use the UI to install the service but instead chose to use this PowerShell script to setup the Service Application’s and Proxies and then start the User Profile sync service. Now this is where things went bad…
Let me take you through the steps I went through so hopefully you can avoid these problems.
- I ran the Prereq installer and the SharePoint 2010 setup to lay down the binaries.
- Took a snapshot (yea I do everything on a VM and after you read this blog post it will become very clear why Windows 7 installs just will not cut it)
- I ran PSConfig
- Took a snapshot
- Ran this PowerShell script.
- Attempted to start the User Profile Synchronization Service, and I waited…
- While I waited I checked the Application event logs and found numerous warning messages source from the “ILM Web Service Configuration” with descriptions similar to “ILM Database could not be created: Error sent to Windows Event Log running:” and including various details such as: “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 ‘CONTOSOFarmAccount.GetObjectCurrent’.”
- What was really strange about this error was the object name CONTOSOFarmAccount.GetObjectCurrent so when I navigated into my SyncDB I found something very odd, notice the stored procedure names in the image below. Now for the object in question here it was actually a view but the same problem none the less.
- I am using the CONTOSOFarmAccount as my Farm account which is also the account I used to start the Profile Sync service. Using Process Monitor I discovered the SQL scripts which generate this DB are coming from %programFiles%Microsoft Office Servers14.0Sql When I search through them I found the objects_views.sql which is what created my offending view(s) from the error message I provided previously.
Something very important to note about these scripts, in the CREATE VIEW statements the name of the view is given without specifying the schema. This means SQL server will use the default schema for the user running this command.
- So what is the default schema for CONTOSOFarmAccount?
- At this point I am pretty well hosed so I rollback to the snapshot I took just before running the PowerShell script (#4 in this list) and I reran the Script again.
- Now at this point I am going to do something that will cast you in the realm of ‘”The Unsupported” so under no circumstance do I recommend you do what I am about to do on a production farm or a farm where you may want to receive any kind of support from Microsoft.
So dude, why are you showing us this at all then? I am showing this merely as education. This is clearly a bug and something I hope to see fixed in the next CU.So at this point I changed the default schema for CONTOSOFarmAccount to “dbo”.
- I then started the Profile Sync service again and waited again…
- This time the DB provisioned perfectly with no errors on the Profile Sync server was “running” – how sad that I jumped out of my chair when I noticed the service went from the dreaded “starting” to “running”; but it happened anyhow.
- I then reset the schema back to the default setting of CONTOSOFarmAccount (note this is specific to my installation and yours may be different).
- Ran IISReset (for SharePoint you cannot do this too many times)
- Opend the User Profile SA, added a connection to my local AD and synced successfully!
Now is this issue specific to my single-server setup, I don’t think so but I will wait to hear from you guys on that part. What I do think is a contributor is using PowerShell to setup the SAs. So does this mean my PowerShell is broken? Well maybe, though I don’t think its too bad I will point out at the time of this post there is little to no PowerShell documentation on setting up each Service Application that I could find Binging around the Inter-web, but again I will wait to hear your feedback.
I have raised this issue to the product team and waiting to hear how we are going to handle this one going forward. I will keep you guys posted.