Man, Resident Evil is getting a lot of play in this thread. And it's gonna get more, because RE2 also scared the proverbial pants off me! My cousin was playing his Playstation, and being a budding gamer whose only PS1 experience was the Pizza Hut demo disk that the church had, I fervently wanted to watch. All I remember seeing was the moment when the gun shop owner gets swarmed by zombies that crash through the window. That image stuck in my mind for years. And of course, now it's really tame.
Clock Tower for the SNES. Couldn't even make it past the intro at first. I got my friend to play along with me and though it did frightened us throughout our play through we managed to beat it late one night. It also left us with horrible nightmares when we went to sleep and vowed never to play it again.
Library level in the first Harry Potter game had me spooked as hell when I got caught. Pretty sure that’s even lamer than OPs story.
Ravenholm was probably my next encounter with fear during gameplay. Those black headcrabs freaking terrified me, probably just because they were such assholes and that level was already pretty tense for me in terms of difficulty and atmosphere my first time through.