Pine Valley #5 immediately comes to mind. As frightening as the prospect of missing right is, I missed left and lost the ball in the ivy below the house. Missed right with my provisional, and was thrilled to card a one-putt triple. It could have been much worse. That hole was in my head for years before I played it. I had walked the course prior to ever setting foot on the course as a player and I felt like I could, at worst, scrape my ball around the place. But even then I wasn't sure how I could pull off #5.
I also find #9 at Yale to be very scary, but that is as attributable to my numerous and colorful failures on that hole as it is the difficulty of the hole itself. That said, it is an intimidating hole even if it is not the "most" intimidating.