"USB Storage damaged. It may need reformatting" error message in status bar
Solved it myself after hours of Googling.

I really only needed to run error checking on the internal storage (CheckDisk) in Windows. Since I couldn't get Windows to mount the drive normally, I tried booting into ClockworkMod recovery mode and then connect USB.

Ran the error checking (Right click on drive, Properties, Tools, Error-checking), rebooted phone and voilà! Life is beautiful once again.