Now that I think about it, in the same vein as the infiltration of the Cathedral, my favorite "quest" that stands out in my mind, for Fallout 2, was the infiltration of the Oil Rig.
Unfortunately, during the my first run of FO2, I considered the game SO MUCH harder than FO1 (and it was, of course) that I spoiled myself slightly by reading a guide for assistance. Shit, I even think it might have been a much younger variant of Per's Ultimate Guide! But I digress... When I got to Navarro, I followed the step-by-step suggestins of deceiving Chris and collecting the Advanced Power Armor first thing's first. The place scared the shit out of me because I'd run into Enclave Patrols before, by this time, but I was wandering amongst Enclave soldiers and they weren't bothering me at all. So, fast forwards a couple days when I finally reached the Oil Rig, and despite realizing this was the end of my epic journey and coming prepared, once I got there, I just felt terrified, and wholly under-equipped. When I walked into the entrance hall, all of the turrets suddenly "activating" (nightmares of them ripping my party to shreds at the Sierra Army Depot still vividly in my mind) just sent chills through me. Every new hallway I walked down I was terrified of all the soldiers surrounding me. I'd played the game up till this point largely with firearms and then energy weapons (since I was under the mistaken impression that weapons still scaled like they had in FO1), so I was only resorting to covert methods because of how terrified of and outclassed by the Enclave I felt. Once I found myself in rooms with the combat bots, I was sweating at the prospect that they'd see through my guise. When I realized that stealing the Executive Key off of the President was impossible (and totally unaware of being able to overdose him with Super Stims), I was shitting bricks at the thought that I'd have to shoot him to take it, and then face down dozens of very, VERY scary soldiers who'd rush in.
Every step I took in the Oil Rig just filled me with absolutely memorable dread, and to top it all off, it ended with the single most epic boss battle of any game, with my all-time favorite Fallout villain, fucking Frank fucking Horrigan. That was an intense end to an amazing game, and that "quest" is easily the most remarkable one that stands out in my mind for FO2. Sure, the irritating early quests probably COME to mind first, but defending the Brahmin from the pack of wild dogs (while definitely IMPOSSIBLE to forget that nightmarish errand) just doesn't "stand out" like the Oil Rig infiltration does, to me.