If a game frightens me, its more often because I can't get the controls to work (e.g. Alone in the Dark II) so keep getting killed off in horrific and frustrating ways.
Some of the early FPS games had strong horror elements though - Doom, Half Life, etc.
I suspect the actual scariest was probably either The Seventh Guest or The Eleventh Hour (the latter had more frustrating than frightening moments, while the horror of the first one built up slowly until you solve the mystery, while 11H did try to keep the horror side going the whole time.).