I-War will permanently have my vote for the best game intro ever created.
Sure, there are more modern games with better-looking intros (I agree that Dawn of War's is awesome), and I don't want to take anything away from the artists who create them, but with the budgets that get thrown at games these days this is to be expected.
I-War wins because at a time when the typical good intro was no more than a minute or two of decent-quality rendering most likely accompanied by some fairly dodgy voice acting, it turned up and blew everything else out of the water with a staggering 14 minutes of jaw-on-floor eye candy, with no more than a couple of notable hiccups in the writing/acting departments.
And not only was it stunning to watch, but it was also relevant to the plot and, in the combat sequences, actually pretty faithful to the gameplay.
Also, it wasn't just the intro... I-War was generous with its FMV, and maintained the same high quality (if not the length :) throughout the game.
The gaming landscape has changed a lot since then. It saddens me to think that space simulators of I-War's ilk have all but died out, and the chances of ever seeing another production of that quality within the genre is next to zero.
And added appreciation for some others that have already been listed:
* Archimedean Dynasty (man, I haven't seen that in years... thanks, Lone3wolf)
* Thief: The Dark Project (Looking Glass' finest intro sequence)
* MechWarrior 2 (often imitated, but never with quite the same impact)
* Mafia (really well scored)
* Heart of Darkness (another jaw-dropper)