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.

3 Answers
3

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.

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.