There were two offerings as far as "SunGrid" was concerned - either the public-facing 'retail' iteration powered by N1GE which was launched as network.com ("$1 per CPU per hour!") and also a B2B commercial version which offered a lot more flexibility, i.e run your Linux distribution on Sun's hardware. The latter was built at scale using AMD Opteron-based SunFire V20z's for compute, for example.
The former certainly suffered from platform constraints and a fairly rigid 'stack', but the latter was far more flexible and various teams within Sun worked closely with several potential customers on the proof of concept. Unfortunately this fell apart thanks mainly due to the commercials and some concerns with how the platform was configured within the context of security and ensuring appropriate segregation of data.
Ah, the SunFire. It's hard to compete with the rest of the world when you design a custom internal bus for your x86 product.
I like that the last sentence you wrote could easily describe today; many customers still care very much about the security context and the data segregation.
Sun faced multiple challenges, largely being a legacy product with legacy offerings in a world where price point was key, their service commanded a premium, and much existing infrastructure wasn't positioned to properly capitalize on it. That's just off the top of my head, though I'd be interested in seeing what other factors accounted for the failure.
Oracle has the kiss of death for far too much in my book. I'd consider any offering it presented with extreme reluctance.