User profiles Sync DB doesn't have Tables or Stored Procedures

Issue:

While you are trying to import the user profiles in SharePoint 2010, you will get lots of errors. Particularly, we will face some of the issues like below:

1. Forefront Identity Manager Service may not be started.

2. Forefront Identity Manager Synchronization service may not be started.

3. If you see the "eventvwr", you will get some of the errors like below:

Error 1:

The Forefront Identity Manager Service cannot connect to the SQL Database Server.

The SQL Server could not be contacted. The connection failure may be due to a network failure, firewall configuration error, or other connection issue. Additionally, the SQL Server connection information could be configured incorrectly.

Verify that the SQL Server is reachable from the Forefront Identity Manager Service computer. Ensure that SQL Server is running, that the network connection is active, and that the firewall is configured properly. Last, verify the connection information has been configured properly. This configuration is stored in the Windows Registry.

Error 2:

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

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Microsoft.ResourceManagement.Data.DataAccess.RegisterService(String hostName)

Error 3:

The Forefront Identity Manager Service was not able to initialize a timer necessary for supporting the execution of workflows.The Forefront Identity Manager Service could not bind to its endpoints. This failure prevents clients from communicating with the Web services.

Error 4:

A most likely cause for the failure is another service, possibly another instance of Forefront Identity Manager Service, has already bound to the endpoint. Another, less likely cause, is that the account under which the service runs does not have permission to bind to endpoints.

Ensure that no other processes have bound to that endpoint and that the service account has permission to bind endpoints. Further, check the application configuration file to ensure the Forefront Identity Manager Service is binding to the correct endpoints.

Solution:

I have spent lots of time to fix this issue. Hmmmm... The solution is in SQL server side. In SQL Server database, you cannot find any "tables" or "Stored Procedures" in "Sync DB"

I have given the solution in single screenshot. In the below screenshot, set the "db_owner" schema for "dbo".

If you set the "dbo" as "db_owner", you can able to start the "Forefront Identity Manager Service". so that you will be able to start the profile import process.

User profiles Sync DB doesn't have Tables or Stored Procedures

Issue:

While you are trying to import the user profiles in SharePoint 2010, you will get lots of errors. Particularly, we will face some of the issues like below:

1. Forefront Identity Manager Service may not be started.

2. Forefront Identity Manager Synchronization service may not be started.

3. If you see the "eventvwr", you will get some of the errors like below:

Error 1:

The Forefront Identity Manager Service cannot connect to the SQL Database Server.

The SQL Server could not be contacted. The connection failure may be due to a network failure, firewall configuration error, or other connection issue. Additionally, the SQL Server connection information could be configured incorrectly.

Verify that the SQL Server is reachable from the Forefront Identity Manager Service computer. Ensure that SQL Server is running, that the network connection is active, and that the firewall is configured properly. Last, verify the connection information has been configured properly. This configuration is stored in the Windows Registry.

Error 2:

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

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Microsoft.ResourceManagement.Data.DataAccess.RegisterService(String hostName)

Error 3:

The Forefront Identity Manager Service was not able to initialize a timer necessary for supporting the execution of workflows.The Forefront Identity Manager Service could not bind to its endpoints. This failure prevents clients from communicating with the Web services.

Error 4:

A most likely cause for the failure is another service, possibly another instance of Forefront Identity Manager Service, has already bound to the endpoint. Another, less likely cause, is that the account under which the service runs does not have permission to bind to endpoints.

Ensure that no other processes have bound to that endpoint and that the service account has permission to bind endpoints. Further, check the application configuration file to ensure the Forefront Identity Manager Service is binding to the correct endpoints.

Solution:

I have spent lots of time to fix this issue. Hmmmm... The solution is in SQL server side. In SQL Server database, you cannot find any "tables" or "Stored Procedures" in "Sync DB"

I have given the solution in single screenshot. In the below screenshot, set the "db_owner" schema for "dbo".

If you set the "dbo" as "db_owner", you can able to start the "Forefront Identity Manager Service". so that you will be able to start the profile import process.

Thats it...

How to configure User Profile Service with Powershell

http://www.smallcitydesign.com/problems/how-to-configure-user-profile-service-with-powershell/
$sb = {
Add-PSSnapin Microsoft.SharePoint.PowerShell
$DBServer = "SharePoint_Alias"
$ProfileDBName = "SP2010_ProfileDB"
$SocialDBName = "SP2010_SocialDB"
$SyncDBName = "SP2010_SyncDB"
$saAppPool = Get-SPServiceApplicationPool "SharePoint User Profiles"
$UPSAppName = "SP2010 User Profile Service Application"
$upa = New-SPProfileServiceApplication -Name "$UPSAppName" -ApplicationPool $saAppPool -ProfileDBName "$ProfileDBName" -ProfileDBServer $DBServer -SocialDBName "$SocialDBName" -SocialDBServer $dbServer -ProfileSyncDBName "$SyncDBName" -ProfileSyncDBServer $dbServer
$proxy = New-SPProfileServiceApplicationProxy -name "SP2010 User Profile Service Application Proxy" -MySiteManagedPath "sps" -DefaultProxyGroup -ServiceApplication $UPA
}

$FarmUsername = "DOMAIN\ACCOUNT-SP-setup"
$serverName1 = "myServerName1"
$FarmPassword = 'Passw0rd!'
$FarmPassword = (ConvertTo-SecureString -String "$FarmPassword" -AsPlainText -force)
$cred = New-Object System.Management.Automation.PsCredential $FarmUsername,$FarmPassword
Invoke-Command -ComputerName $serverName1 -ScriptBlock $sb -Credential $cred

Configure User Profile Synchronization Service

https://www.nothingbutsharepoint.com/sites/devwiki/SP2010Admin/Pages/15%20-%20Configure%20User%20Profile%20Synchronization%20Service.aspx

There are further here on the SharePoint blog as there are issues with following least priviledge install e.g. having separate Active Directory Domain User Accounts for each role in farm. To get as far as I did I used Active Directory Domain Administrator user account for everything. Full credit goes to @givenscj for his help getting through this!

There are some IE8 bugs in the current UI...so push F12

Click "Browser Mode: IE8 Compat View" and select "Internet Explorer 7".

Click "start" on "User Profile Synchronization Service"

Click "OK"


Click on "monitoring" on left hand side and then click on "Review Job Definitions"

Click on "Running Jobs" on left hand side

You should now see ProfileSynchronizationSetupJob running

Keep refreshing until it disappears and then you'll notice it'll disappear. Click on "Job History" on left hand side, you should see the job with status of Successful.

In Windows Services you should now see Forefront Services both started and running under your administrator account.

Click on "Application Management" then click on "Manage service applications"

Scroll down and click on "User Profile Service Application"

Click on "Configure Synchronization Connections"

NOTE: you may see the below error, if you do simply do an IISRESET

Click Create New Connection

Fill in details

Click "Populate Containers" button then expand tree and select "Users" OU tree node and then click "OK".
NOTE: Clicking "Select All" does not work in this build!

You should now see a connection in this list.
NOTE: if you get to next screen and nothing shows, please ensure both Forefront Windows Services are started.

Go back to "User Profile Service Application" page from "Manage Service Applications"

Click "Start Profile Synchronization"

Select "Start Full Synchronization" then click "OK".

NOTE: this will take a few minutes as a job is scheduled to do this.

Now if you click "Manage User Profiles" you'll see the User Profiles coming through

In Beta 2 it is normal to see these warning errors in the Event Log.

Also note that you cannot edit the connections because of a JavaScript error.

For more information on the User Profile imports go to:
C:\Program Files\Microsoft Office Servers\14.0\Synchronization Service\UIShell\ and run MSIISClient.exe


If you get this error:
"Cannot navigate to the requested page while User Profile Synchronization is running. Please wait for the current Sychronization run to finish."
You will need to restart the machine (you can most likely just restart the services too)

FIM – Forefront Identity Manager Failed to Connect to SQL Server

FIM – Forefront Identity Manager Failed to Connect to SQL Server


ISSUE
=====
SharePoint 2010 connects to a SQL Instance
In SharePoint 2010 FIM is used for importing and synchronising user profiles by the User Profile Service Application. Starting the User Profile Synchronising Service (from Central Admin – System Settings – Mange Service on Server) Fail’s. Reviewing the Event log you receive the following error message:

Log Name: Application
Source: FIMSynchronizationService
Date: 13/07/2010 15:19:37
Event ID: 6324
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: .
Description:
The server encountered an unexpected error and stopped.


Resolution
=========

Somehow the SQL Instance information for the FIM Synchronising Service is missing

To resolve the issue add the SQLInstance name to the following Reg key:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\services\FIMSynchronizationService\Parameters]
"SQLInstance"=""

1- How to configure User Profile Service with Powershell

Unable to modify DCOM Config properties for a single application in Component Services

User profiles Sync DB doesn't have Tables o...

SharePoint Solutions: Issue: User profiles Sync DB doesn't have Tables o...: "Issue: While you are trying to import the user profiles in SharePoint 2010, you will get lots of errors. Particularly, we will face some of ..."

The EXECUTE permission was denied on the object ‘proc_GetProductVersions’- Insufficient SQL database permissions for user

http://sajiviswam.wordpress.com/2011/05/24/the-execute-permission-was-denied-on-the-object-proc_getproductversions-insufficient-sql-database-permissions-for-user/

With SharePoint 2010 RTM production farm, the following error was being logged in application event logs with severity level Critical when users try to open some of the site administration pages। An example is Site Actions > More Options page।
Insufficient SQL database permissions for user ‘Name: SID: ImpersonationLevel: Impersonation’ in database ‘SharePoint_Config’ on SQL Server instance ‘’. Additional error information from SQL Server is included below.
The EXECUTE permission was denied on the object ‘proc_GetProductVersions’, database ‘SharePoint_Config’, schema ‘dbo’.
In order to resolve the issue, I provided Execute permission to the database role “WSS_Content_Application_Pools” into the stored procedure “proc_GetProductVersions”. I performed the following steps to do this.
1.In the database server, expand SharePoint Config database and naviage to Programmability/Stored Procedures/dbo.proc_GetProductVersions using SQL Server Management Studio.
2.Right click on the above stored procedure and select Properties.
3.On the popup screen, select Permissions on the left and click Search button.
4.On the new popup screen, click Search, select [WSS_Content_Application_Pools] database role and click OK.
5.Click OK again.
6.On the first popup screen, select the role, check Execute permission and click OK.
There is no need to restart any services for this change to take effect.

Error 7043 "Load control template file /_controltemplates/TaxonomyPicker.ascx failed"

Error 7043 "Load control template file /_controltemplates/TaxonomyPicker.ascx failed"

http://support.microsoft.com/kb/2481844
This is not causing any issues except for a wrong ULS log message a single time in a web application process life time, the exception is caught and that template file is skipped. This message should be treated as log noise and can be ignored.
To change this behavior:
1-Navigate to /14/TEMPLATE/ControlTemplates/TaxonomyPicker.ascx user control
2-Open the user control in a text editor and locate the first line
3-Find the character string , and replace with a comma ‘,’ (without quotes).
4-Save the user control
The above solution from Microsoft doesn't work for me.
The solution is to rename the file "TaxonomyPicker.ascx" to "TaxonomyPicker.ascx_bug"