Curse Zone, to me, is the scariest song of any Castlevania game.
I remember the intro to Simon's Quest scaring me as a kid. We lived in an old house, and I had to play it upstairs alone. The night music from that game was kind of scary, too, and helped with the day/night contrast a lot.