I don't think any video game music has ever scared me before.
Recently, I played Metroid, and the music was a little unsettling in its simplicity. It is a lot different from other NES games.
Secret Area is a good choice, but Norfair Theme is more sinister than scary.
Here's the Witch's theme from Left 4 Dead.
Destroyah's theme from Godzilla Unleashed.
Opening to Hell from Doom II.
Metamorphosis from Metal Slug 2.
Also, can someone please move the thread to Gaming Discussion? I put it in Nintendo Discussion by mistake.