Regarding username /password error.Thanks very much. I spent some time last night to go through the steps. Got most working but today I can't seem to refresh/update as I always get user name/password error when I try to refresh data in power pivot table. Not sure why as it seems to work yesterday. Also got warning about 32 bit version of Excel 2010 which is the one I'm using.
As on the same PC you were able update yesterday, I have to assume that your PC is setup correctly and you have SQL Server Native Client installed.
Second most common reason for this error - when you are running some firewall on your PC, strange thing is that in such case I have to assume you had firewall yesterday and it worked.
I had users reported same issue, and then reported back that they had this issue when they run VPN on their PC. Maybe that is your case?
If you still experience this issue, please let me know, I'll post what you should do next.