The level 'Playing Judas' from the original Free Space: The player's ship is a captured junkpile with the resilience of wet tissue paper, the enemy fighter patrols consists of Dragon-class fighters, and there's no backup. Despite this, however, the level's true crime is being so spirit-crushingly boring without any real payoff.
Playing Judas is actually the easiest level in the entire game if you know the one trick to it. As soon as you start the level, stop. Fly straight to any ships you are told to inspect, inspect them, then stop. Repeat. The patrol paths do not go anywhere near the flight path of the ships you are supposed to inspect. At the very end, you have to dodge a few fighters until you are allowed to leave, but that isn't too difficult. On the other hand, if you don't know the trick, then, yeah, definitely fits the trope.
And 'A Game of TAG' from Freespace 2. Your armament consists of some missiles that "tag" the enemy ships so a friendly corvette can shoot them down. The problem is that the TAG is un-guided, your primary weapons are an inefficient combination you can't change, your fighter is outdated and outclassed by everything the enemy has, and the corvette, in rigging itself up for the test, has become incapable of shooting at targets that aren't tagged. While the TAG works as advertised, you must often fly out of the corvette's firing range, and trying to hug the corvette won't work; too many enemy fighters and bombers will swarm the ships you're trying to defend, and you can't tag them all before they destroy everything. You have to break off and start engaging them before they get too close, and you have to do this with your terrible primary weapons.
'Proving Grounds', the very next level, was worse. The mission starts as a training exercise, with you defending a decommissioned cruiser from new stealth fighters that don't show up on your radar. Keeping the cruiser in one piece gets you the bonus objective, but it's very hard to do unless you spam EMP missiles at the cruiser itself, discouraging the stealth fighters from shooting at it. After this, real enemies jump in, and you must defend GTD Aquitane from several squadrons of bombers as well as SCv Tiamat, a Shivan corvette. The Shivan corvette has an incredibly tough hull, and the mission does not allow you to mount any weapons that can destroy it; while defending the Aquitane from bombers, you must keep the corvette in sensor range so the Aquitane, which can't see very well in the nebula, can keep firing its beams at it enough to destroy it. If you don't do this, the corvette escapes and you miss One Hundred Percent Completion. Oh, and if you took the EMP missiles to help you save the frigate during the exercise? Good luck fighting those bombers, which you really need real missiles for.
'Tenderizer' from the first game. You have to protect a destroyer from kamikaze fighters. Oh, and an Aten cruiser. You'd better have brought Stilletos.
'The Keepers Of Hell' from the fan-made Blue Planet add-on. You have to run a blockade of one Moloch corvette, two Lilith cruisers, and two Ravana destroyers. The ship you're protecting is powerful, but not that powerful. Worse, the Ravanas and the Liliths are on opposite ends of the battlespace from each other. The only way to win was to disable all the beams on the Liliths and Ravanas, while diverting all power to engines to squeeze every last bit of speed out of them to get to these ships. While being chased by guard fighters. And worrying about incoming wings of bombers. And even then you'd only have about 3% health left on your friendly ship at the end. One of the first things the designers did in the Updated Rerelease was re-balance the mission... it's still hard, but not completely unfair anymore.
Also from Blue Planet, 'Forced Entry' and 'Universal Truth'. It's almost the same as the above entry, only that your Terran fighter isn't as powerful as the Vishnan ones, and neither are the capships you are protecting.
'Enter the Dragon', the mission right before 'Playing Judas'. As the name implies, you have to capture a Dragon-class fighter. The opening is fairly easy, with just some weak fighters. However, it's likely that your wingmates will destroy the fighter instead of disabling it, and doing it yourself requires you to use a slow-firing disruptor cannon on a highly-maneuverable, razor-thin target.
Star Wars
X-Wing Alliance: This game had a level in which you need to escort a shuttle from its base to the hyperspace point. Said shuttle becomes the primary target for every single TIE fighter and bomber on patrol, which frequently shred it.
Also, the level where the Star Cruiser Liberty falls under attack by robotic controlled TIE Fighters. Half of which are suicide ships. And that fighter wing you're a part of? They either can't be bothered to help, or are no help at all. Only level I used the "Take Leave" button and skipped.
The level where you have to hijack the shuttle Tydirium. Once it's captured, the TIE Fighters will be over you like hornets. Heck, I stayed in the base, and it would've been destroyed if it wasn't invincible at 1% Hull. It was so bad it had to be patched up to be passable.
Star Wars: Rogue Squadron II: Rogue Leader. Razor Rendezvous. You have to fight TIEs off of the Redemption, your Nebulon-B carrier, disable a Star Destroyer, while evading it's massive ion cannons and turbolasers, AND the swarms of TIEs the SD has with it. With ZERO help from the dozen or so other Rebel fighters in the battle. And with the slow-firing, slow-moving B-Wing. Several other levels in the game are also Scrappy, most are Scrappy if you're trying to get Gold or * shudder* Best Ever medals.
Ironically, it's the shortest stage in the game, with players doing speedruns as fast as 35 seconds.
Battle of Endor is worse. They expect you to fight practically the entire thing by yourself. Protecting Home One AND disabling 2 Star Destroyers. Strike at the Core is also bad, what with Lando having a terrible habit of getting blown up within 5 seconds of the level starting...
Strike at the Core is a particularly interesting example as right the way through, it is incredibly dificult. First you have to protect Lando while dodging turbo lasers, then you have to protect/keep up with Lando, while making extremely tight turns inside the Death Star, and then you have to outrun the explosion, again while making all those tight turns...and yet if you pull it off, it becomes the most AWESOME PART OF THE ENTIRE GAME!!
I think we're forgetting the worst part of Battle of Endor. I don't know how many hours of my life I spent hearing the god awful "They're heading for the medical frigate!" "PLEASE HELP US." WHO CARES ABOUT THE GOD DAMNED MEDICAL FRIGATE? WHY IS IT EVEN THERE?
It can get infinitely worse if you encounter a rare bug where Lando won't stop shouting "We gottaBUYmoreTIME!" until the game freezes.
Imperial Academy Heist. The first 1/3 of the mission is a Stealth-Based Mission involving dodging or disabling sensors; flying too high (an easy mistake to do) or too close to a sensor, as well as destroying a sensor outright, results in mission failure.
The main objective of this level? Capture the shuttle Tydirium. You know, the shuttle Ace Azzameen captured in one of X-Wing Alliance's scrappy levels (see above). Apparently, the Empire stole it back and tried to come up with a hiding place even more annoying so that the Rebellion wouldn't have another go at nicking it.
Both Y-Wing levels in Rogue Squadron I, especially Raid on Sullust. If you can get a gold medal (or even beat the level without dying) without using the Naboo Starfighter, you deserve... well, a medal.
From the same game, Escape from Fest. You have to trip three AT-AT walkers and deal with Tank Droids that are all way too good at blowing up the stolen AT-PT walkers you're escorting. Getting a Gold Medal requires protecting all three AT-PT walkers. Oh, if you could just be playing in a Y-Wing for this one, but no. You have to do it in the freaking Snow Speeder, complete with Interface Screw and Camera Screw whilst tripping the walkers.
Other examples
For the most part Darkstar One is a forgiving game to people not having the fastest, most accurate trigger finger. Unfortunately at times you are taken out of your cockpit, and dumped into a turret mini-game, which compared to a regular space-battle is almost ridiculously hard. Espeically with the second one, which the official forum actually has service to play through that mission.
Made no better by the game ramping up the difficulty for every upgrade item you have picked up to that point. One of the upgrades you're likely to have maxed out on if you're a terrible shot is, y'know, turrets to do your shooting for you, so that not only aren't you a great shot, but you've managed to set up your ship so you don't have to be.
There's also the mission with cloaking torpedoes in Wing Commander III. Don't stray from that capital ship, or you'll miss the torpedo, and the mission becomes a failure.
The mine clusters? Sheesh! They are NOTHING compared to infamous Kurasawa-2 mission in WC1. Yes, the one with Ralari defense (you have about ten seconds to destroy 4 heavy cat fighters, or else the craft you're supposed to be protecting goes boom). Even players knowing the developers intentionally made it hard still spend many hours and much Save Scumming to beat the level.
The eighth mission of Starlancer. Your capital ship (the Reliant)is under attack, your wingmen are inept, you have to kill several waves of fighters, twelve torpedoes, and everything else that the mission throws at you. Your reward for failing in what basically is a superheroic effort? Relocation to a new base followed by execution.
Oh please, this was practically a running trope in this game, mission 8 is just the most egregious example, I'd say that the later level defending the Victorious and last level featuring the Borodin are the worst examples.
Some of the final missions, like the one where the Yamato goes toe-to-toe with the Pukov. Torpedoes incoming? Gotta kill them. Fighters attacking? Gotta kill them. Bombers on the way? Gotta kill them. Shield Generators and stuff gotta be disabled on the Pukov? All Up to You. Wingmen? 500 klicks off thataway, doing God-knows-what for absolutely no reason, since all the fighting is taking place in the little corridor between the Yamato and the Pukov. Save points between different phases of the half-hour-long mission? Ha: those are for wimps. ...Yeah.
X3: Reunion, while not having actual levels (the game is a sandbox-style trading/fighting sim), has a mission where you are taken away from your ship and given a fast, unarmed light fighter; you're then told to race two other people in fighters exactly the same as yours through a pointlessly long series of target markers. The whole thing is made very hard by the fact that the AI pilots seem very good at navigating the course, while you have to follow tiny blue arrows in the outermost part of your HUD just to visualize the targets. Of course, missing one target means you have to redo the whole thing, as the NPC pilots will leave you in the dust if you backtrack. Considering the course is way longer than necessary, this can very much cause your mouse/joystick to suddenly and violently take flight.
And if that isn't enough, this level was made even harder with one patch.
Any Escort Mission. See that page for the reasons why players avoid them like the plague.
In X3: Terran Conflict's Operation Final Fury plot, the last mission has you attack and destroy the last Kha'ak hive in known space. Well and good, except there's multiple Kha'ak capital ships and defense platforms, not to mention endlessly respawning fighters, which requires most players to fly an M2 destroyer to survive. And the sector's jump-in point is about 250 kilometers from the objective, and an M2 can't even top 100 meters per second. So after the initial fight with a defense platform at the jump point, you're left crawling laboriously across the sector with nothing breaking the monotony except occasional raids by fighters that can't even dent your shields and are promptly splattered by your flak mounts. Most players use this time to do admin work on their trade empire and/or grab a sandwich.
The Hub plot is a huge Guide Dang It: nobody In-Universe warns you that you'll have to build about fifty chip plants well in advance of the final stage and use multiple TL heavy transports as warehouses in order to complete the plot in less than a year real time. Fortunately this Scrappy Level has a reward that matches the effort: in addition to your newfound control over the game's Portal Network via the Hub itself, the factories you built for the plot will make stupid amounts of money afterwards.
In Star Trek: Klingon Academy, there is a mission where you are piloting a cloaked Bird of Prey, and you have to sneak up really close (<100 kellicams) to a large cruiser in an asteroid belt to listen in on a secret conference. The problem: there are four enemy vessels patrolling the system, and you have pilot your own vessel in weird patterns to avoid getting caught. Each enemy vessel is surrounded an imaginary sphere with a radius of 15,000 qelIQam that you absolutely, positively, must not enter. And did I mention those ships were moving? So you have to be constantly watching your radar and your distances indicator. Then, after listening in, you have to sneak out again! And guess what? Later on in the game, you have to do the exact same thing AGAIN, only in a nebula instead of an asteroid field! Joy!
Battlestar Galactica game for the PlayStation 2 (the series "hydrid" one), the 13th stage- it was near the end, but insanely more difficult. First, you are on an escort mission for one of your wingmen who is scanning an enemy capital ship with large amounts of high-class interceptors attacking. While doing this, dozens of enemy frigates are on an intercept course for your flagship, and apparently you're the only one around to help stop the nerve-gas carrying ramming frigates. While STILL escorting your wingman, you have to destroy five weak points around a large capital ship in under a minute... Now, you're done escorting and have 2 minutes to kill the Ace Pilot who killed your sheep, return to the Enemy Capital Ship, take down the shielded core in under 1 minutes- to get the message from Galactica Actual giving you 5 seconds to get clear.
Trying a reentry in Orbiter Spaceflight Simulator with the Space Shuttle is impossible to do correctly, because the hypersonic aerodynamics for the shuttle model are inaccurate. Considering that Orbiter is meant to be a serious simulator, it's a very frustrating predicament for many users.
Elite I - Buying a Docking Computer. Docking Computers require money. To make money, you have to fly to a load of different stations. You can only save inside a space station. To get to a space station, however, you have to fly through the whole system, often fighting off streams of bad guys (which is hard - if you don't even have a Docking Computer you're unlikely to have to have upgraded weapons, ECM, etc). At the end of each trip you have to fly around the space station, get a straight bead on the docking bay and while you're flying at it you have to continually adjust to the rotation of the damn thing. Failure to do so means you crash (which happens most of the time) and you're back 30 minutes to your last save point.