There was a lot of moments in the 7th Guest that spooked me. I remember a ghost lady always beckoning me the wrong way in a maze, something happening in a bathroom and a kitchen, a lady getting killed or absorbed or something in a red room... something like that. Then again, I was also pretty young when I played this.
A AG that I have yet to play that seems pretty frightening would be Clock Tower for the SNES. Seems freaky.