In "Publish Workbook" dialog there's a place to embed credentials for each datasource (if it needs any).
It's possible that this particular datasource didn't have correct option selected or credentials changed (and became invalid) since publishing.
To fix this you can either republish workbook or edit that particular connection and embed the password (if security policy allow of course):
Thanks for the reply.
However, in order to get my scenario working there was much more needed as I had selected 'Embedded password in connection'.
I had to download the data source from the app server, re-submit it to the app server, and then I re-submitted my workbook. This finally got things worked as expected.
It was probably "or credentials changed (and became invalid) since publishing" part.
Glad you got it working!