SharePoint 2013 User Profile Service requires SQL 2008 R2 Native Client

User Profile Service is one of the service that needs additional attention and exact steps to plan and configure it. If you miss any steps during the configuration, you may have to delete it and start all over again. The best information on line to take you through the process is still the one from SharePoint 2010 time:Rational Guide to implementing SharePoint Server 2010 User Profile Synchronization.

Even though you may follow his and other articles precisely., you may still run into few issues. One of them I have experienced recently and took me two weeks to figure it out.

The environment I am configuring is using SQL 2014 with Windows 2012 R2. When I configured User Profile Service and started first sync, everything ran correctly. Two weeks later, I found out that the service did not run any more. I tried few times, not working. I reviewed my steps, change servers and check databases, no progress. The problem I had was: after I created User Profile Service Application, I could start User Profile Service from CA, but not Sync Service. When I reviewed the Services console, both FIM and FIM Sync service were disabled.

I tried to manually start FIM service and got an error message as following:

.Net SqlClient Data Provider: System.Data.SqlClient.SqlException: Could not find stored procedure 'RegisterService'.

I also noticed that the Sync database did not have any table or stored procedures populated. I tried to find more information regarding why this database was not created correctly when Profile and Social databases were provisioned correctly? Some articles mentioned that the Farm Admin account (the one is running CA application pool if you have separated setup and farm admin accounts) should own the DBO schema. However, this is not the case to me.

After a while, I was told that perhaps I should check my SQL native client installation. From an article regarding User Profile Service with SQL 2012, I noticed that SQL 2008 R2 native client may be required even though I am using SQL 2014. I also recalled that I have uninstalled SQL 2008 R2 native client after the User Profile Service was provisioned successfully. 

I went to re-install SQL 2008 R2 native client to all of my SharePoint 2013 servers. Finally I could create this service successfully. During the last provision, I had chance to observe how the service and databases were populated. I will included them at the end of the blog. So, even though you are using SQL 2014, you still need SQL 2008 R2 native client to create and run User Profile Service. So, do un-install it like I did.

The service provision process is here:

1. When I created the service application, all three databases were created. Sync DB was empty. No tables nor stored s[procedures.
2. When I started User Profile service from CA, nothing really changed.
3.  When I started User Profile Sync Services from CA, things changed like this:

a.      FIM Synch service changed from disabled to automatic first.

b.      FIM Synch service started.

c.      Sync DB started having tables and stored procedures populated.

d.      FIM service changed from disabled to automatic

e.      FIM service started.