Gotta agree with Michael McCloud... the Tank and Witch in L4D both inspire fear, for me. Not because I'm scared of them because they're horrible zombies, but because they are a lot more powerful than me. They're faster, stronger, and if I have to fight them, I'm probably going to die. That's not too common these days in the games I play, which are either singleplayer or PvP multiplayer. The whole atmosphere in Left 4 Dead helped a lot. The... tension. You don't get that in games, ever, really. So yeah, Tank and Witch.
Now, games that scared me? Super Metroid. Going into Maridia, the underwater section, when I was about 8? Freaked me out. Underwater is not a fun place. And Bioshock was pretty good. Nice creepy atmosphere the whole way, but the bit that got me was the room full of those 'statues', which were actually people who'd been killed, posed, and quickly papier-mâchéd. But that happened to them so quickly that they still had time to bleed into the paper, so they were bleeding statues. Oh, and the room had splicers in it. That was kinda freaky.
Enemies don't really inspire a lot of fear for me, 'cause I know the point is that I can beat them. And if I can destroy them, how can I be scared of them? Even if they beat me a couple times 'fore I get them, I still knew from the outset that I was supposed to waste 'em.