Good call. That setup process made configuring Sound Blaster IRQ settings a joy.
I'm sorta surprised Out of This World (aka Another World) has not been mentioned yet.
OOTW intro youtube link

Left 4 Dead.
Love the game, or loathe it, nothing gets you amped for zombie genocide like this intro.

Some of my favourites are:
- Already Mentioned, so no vid -
Dawn of War
Vagrant Story
Syndicate - Prefer the music on the amiga version to the sounds of the PC
Beyond Good and Evil
Freespace
Fallout
Soul Blade
- Aaaand -
[url=www.youtube.com/watch?v=Fvha_2hAzq0]Turrican 2[/url] - Just for the music, I'm surprised nobody mentioned this, YOU SHOULD ALL BE GROUNDED FROM GOG FOR A WEEK, ^_^
[url=www.youtube.com/watch?v=PiYuq6Ac3a0]Cannon Fodder[/url] - ... (hey this ones on gog and nobody..) THATS IT!! 2 WEEKS!!

Earthbound on the Super. First it gives you nightmares, then it lets you know everything's gonna be alright. Incredible.
http://www.youtube.com/watch?v=g3dIWyvNoxM
Also, the first bit of Ninja Gaiden on the NES. Something about 8-bit cutscenes makes me wish people were still making these games.
http://www.youtube.com/watch?v=_rkaiKYEkDQ

That intro had the most impact of any I've seen, makikng me want to buy the game which I usually don't do based on an intro. I was looking to see if anyone posted it.
World of Warcraft deserves special mention for its intro when you make a character and get the flyby narrative to the character in the city.

I really really like the one from soul reaver..

I know why nobody has mentioned it, because nobody bought it, but the intro to Outcast by Infogrames is easily the best intro! Took the whole of CD 1! It's intelligent, well scripted (the vortex mixed into the ice swirling in the glass, for example) and is so 'real'! Like a movie!
Check it out at:
http://www.youtube.com/watch?v=3lMsrqS2LDE&feature=PlayList&p=B37BDECDBF61CA86&index=0&playnext=1
I-War would come in second for me - another long, intelligent, movie-like intro!
EDIT: Notice Outcast was mentioned once before - sorry! My link is the full English intro with the CGI and game engine parts of the intro,
Just remember another great intro I would put up there with Outcast and ahead of I-War. The great intro to a 1999 PC game called Urban Chaos - another underrated classic game no one bought!
http://www.youtube.com/watch?v=3HfuBQPx0VU

The Outcast and Urban Chaos intros are definitely superb.
I don't really think Urban Chaos is under-rated unjustly, though. The game is quite ambitious, but it's also really flawed, and I didn't enjoy playing it nearly enough to consider it a classic.

Not exactly an intro but this is the new video that just came out for EVE onlines new expansion: EVE : Dominion
http://www.youtube.com/watch?v=7_DQfA9N7po
It's goddamned awesome and, although you may not think so if you havent played the game (or at least played *this* part of the game) is surprisingly representative of how battles play out.
Espeicially love the bit where the reinforcements show up and say 'wow thats alot of reds in local'. Said that so many times myself.

Did no one mention the tram ride through Black Mesa yet? Although it's a little long and drawn out before letting you into the action, the first time I booted up Half-life, I was just amazed at how pretty and dynamic everything that was going on around me was.
And although not necessarily the best or even very good, this one from Sin Episodes: Emergence is entertaining too. Wait for the hot tub scene at the end. If only I was 14 again...

Hellgate: London's opening CG movie was quite memorable and well-presented, to say the least.

For current games with current levels of writing talent and technology, I'm going to go with Bioshock. Whether the intro was real or not, it lays out enough exposition for you to be lied to. (The statute of limitations is up on that one-we can talk about it.)
Ah, Black Mesa Inbound, it's good to see you again my old friend. I play through Half Life maybe once a year and the beginning is probably my favorite bit.
Speaking of that, has anyone else read Marc Laidlaw's extended opening for Half Life 2? It was supposed to be in the original version with the Air Exchange and what not, I believe it was in Raising the Bar. It had the G-Man explain everything. Flowers sprout up around his feet and die, the sky changes from clouds to storms-it was pretty incredible, and I would have loved to have seen it.

I did not, but if it's what do you say it is, I can see why Valve didn't use it. I mean, it doesn't fit with G-Man at all, he tends to give you only basic information on ... Anything