I'm trying to pull some user profile information for my company's AD and FBA users from a SQL database, but I'm running into problems with the Profile Synchronization Service. It's failing to pull any data, and as far as I can tell it's not even sending queries to the database.

The truly confounding thing is that the External Content Type I've set up works, the AD connections I've set up work, and the whole process is running without throwing any Events or other errors that I can find. I'm at a loss as to how I should debug the process, there's no code, there's no errors, and each individual component seems to work.

If anyone can suggest what I could've done wrong I'd be incredibly grateful. Here's what I have currently:

1. I've created a simple test database table, with numbers in one column and letters in another.
2. I've created an External Content Type via Sharepoint Designer. It is connected to our database via BDC identity, and uses the number column as an identifier. The Read List and Read Item actions are set to return both columns.  Creating a list for this content type and setting up a read action both return data correctly.
3. I have created User Profile Properties for the Numbers and Letters columns, I have populated the Number property on two test accounts.
4. I have created a Synchronization Connection targeting the External Content Type. It passes the Numbers property as its identifier, and is set for 1:1 mapping. The Letters property has been linked to this Synchronization Connection. There are no other connections currently running.
5. Running a Full Sync does not update the Letters Property in the test accounts as it should. I do not see any incoming connections on the SQL server, but no errors or events are thrown. Watching the Synchronization Service Manager (which I am rather unfamiliar with) reveals that the sync is reporting success, but the DS_FULLIMPORT only does one add: CN=__SPS-Dummy. The Sync similarly has one disconnector, and the MOSS Export doesn't send anything.

I've rebuilt, restarted, handed out a large amount of permissions, and generally tried any even-slightly-related  fix that I can find. Do any of you have any suggestions for fixes, or ways to get my hand on useful debug information?

Hi Eric,

A simple number column cannot be used as the identifier for the connection. When you specify an identifier in ECT, there must be one existing user property which has the same value as the identifier column, otherwise, SharePoint User Profile service would not know how to populate additional properties for users.

Here's a video about the process for your reference:

https://www.microsoft.com/resources/technet/en-us/office/media/video/video.html?cid=stc&from=mscomSTC&VideoID=ede7d855-035c-47a9-9492-1c2a23614a4a

Meanwhile, please ensure your SharePoint farm account has at least read permission for the external SQL database.

Thanks,
Reken Liu