I have created a console application that retrieves data from SQL Server 2008 into a SP 2010 portal. The account that it uses is a personal account that is part of an AD group that has been configured on SQL to have read only access to the data. As long as I execute it in the console application, it works fine. However, I created a timer job with the same SQL queries and deployed it to the server. I can execute the job and step through the code. No error is returned, but no records are retrieved from the SQL table using the identical code from the console application. I have checked to make sure that the farm account has been added to the AD group that is configured for read-only access to the db. I've set up a task to run the job and it's working OK, but I'm thinking that's probably not ideal. I've also set the trust level for the web app to "Full". Please let me know if there is some inherent limitation in using SQL components in SharePoint timer jobs.
Yea, all timer jobs run under your service account's identity, which may not be the same as the farm account.
I've had success adding an owstimer.exe.config file to the sharepointroot\bin folder and configure enterprise library on there. That way I could easily output trace and other debug information.
You didn't specifically mention it but I would be very wary of hitting any of the SharePoint Databases directly - even as read only as this is not supported.
I am aware there is one exception to this in SharePoint 2010 around usage logs, but I can't find any official documentation to clarify this.
I followed a very similar path in 2007. In fact, that led to my first timer job. I would concentrate on trying to validate the identity the timer job is running under and find a way to write out some diagnostic info.
It may also be worth trying to temporarily open up the sql permissions to validate if it is a permissions/identity issue or something else that might be preventing the results from being processed.