SPTimer job and BCS External list

Question

Business requirement - synchronize a table from external my sql database into SharePoint list.

What I have done. I have an external MySql database which as LOB data. I have created a BCS application which would read the data from the external database into Share Point using External Content types and list.

To keep data in sync, I tried to create a timer job which would access the external list and update another list in sharepoint when a item is added, modified or deleted. However, when doing so, it errors out with the message "ReadList" cannot access the mySql hosts. I tried assigning permissions to the account accessing the BCS service, but in vain. Does any one have a code sample?

Also, is there any other way I could synchronize the data from external database into SharePoint list? I would need to have the SharePoint list to sync automatically without any user input.

Answer 1

I suspect you have a permissioning problem, either at the DB end or the BCS entity level. Things to check would be the permissions applied to the BCS entites & ensure the user that the TimerJob is running as has permissions to them correctly. Also check your security setup in the BCS model for connecting to the DB. Is the ID being passed through? are you using SSO or are you supplying credentials to connect to the DB in the model? Try logging at the DB end to see if connections are trying to be made, but are being rejected. It might tell you what account is trying to connect.

Another option would be to talk to the DB directly from your Timer Job. But the BCS route is nicer and gives you more options for integration with that data elsewhere in SharePoint.

Answer 2

The timer Service runs under the farm account. To check which user that is, have a look at the services running on the server (start -> run - > services.msc), and check the identity specified for the SharePoint 2010 Timer in the log on as column. Try giving that user read access to your MySQL DB.

Answer 3

You could use Microsoft Sync Framework: http://msdn.microsoft.com/en-us/sync/bb736753 This is built to hold data in sync.

If you use this then it is not SharePoint that has control, but the sync service that would move data between the MySQL database and the SharePoint List.