Lonesome Road just because of the part where the first deathclaw that you fight spawns, it made some freaky kind of noise then turned out to be stronger than expected.
I found out on my last playthrough that you can avoid that encounter all together! Just go around the camper/trailer instead of going inside it, and the deathclaw ambush will not occur. The whole event is dependent on the player's curiosity.
Anyways, I found all the DLCs to be scary in some ways, so I don't know if I can say which was the scariest. Here are the "scary" parts of each:
Dead Money: Fighting Ghost people in the Villa for the first time was rather scary.
Honest Hearts: If you have a low perception stat, Yao Guai and Giant Cazadors tend to pop out of nowhere, and in the middle of the night during a rain storm, this can be a bit scary.
Old World Blues: Fighting a dozen Nightstalkers at night in that hexagon area was scary as [censored]. They kept running away then appearing out of nowhere from behind the pillars. The Cazador facility, the Nightstalker facility with the invisible Nightstalkers, and the crazed Securitron facility were all pretty scary as well.
Lonesome Road: The encounter Wombat mentioned nearly gave me a heart attack the first time I played Lonesome Road, and the Cave of Abaddon was pretty scary as well. Also, the invisible marked men could make for some frightening surprise attacks, and the way that the Marked Men would fire flares into the air added to the "scariness" of the atmosphere. And then there was Rawr...