Sad to say, these titles are no longer accessible, unless you manage to find some far-flung server at the end of the universe where no one's bothered to kill Sartharion at all.
The classic game
Classic World of Warcraft wasn't known for an abundance of PVE-related titles. While it did have a wide array of battleground titles that reappeared later in Cataclysm, the one PVE title you could get from classic is pretty much the definition of inaccessible.
Difficulty: I think we might politely describe it as insane.
To this day, Scarab Lord is among the rarest and most prestigious titles in the game. It's also among the few titles specifically referenced by NPCs, who can be overheard discussing both the Scarab Lord and the Hands of A'dal in awed tones as they defend Wintergarde Keep against an onslaught of undead. That's right, folks: This is among the very few titles that NPCs think is worth some street cred.
It's the least they could do, given the maddening grind that getting the Scepter entailed. You can see the now-vanished quest line in detail here at Wowpedia, and those of us who've done it will suppress a shudder looking back. If grinding up thousands of bug parts didn't drive you to the nuthouse, you'd probably lose it during your millionth consecutive run of Blackwing Lair in the fruitless pursuit of Elementium Ingots, or maybe at the 50th chimaerok kill without a tenderloin drop. Adding to the frustration was that the average player doing all of this probably never had a shot at the title at all. The scarcity of the various materials guaranteed that raiding guilds racing to beat each other typically completed one or two Scepters at most.
Which is not to say the quests were without their pleasures -- they were a wonderful bit of lore from start to finish, and Azuregos deservedly became one of the most beloved characters in the game -- but the underlying design behind them was a pretty bad deal for the dozens of players who toiled behind the scenes so someone else could get the title.
The Burning Crusade
For most of BC, PVE titles were still pretty thin on the ground until the Wrath content patch. The two below were actually late additions to reward players who had done attunement chains that Blizzard decided to remove.
The Serpentshrine Cavern and Tempest Keep attunement requirements lasted for a surprisingly brief period in hindsight. While both were necessary to access the tier 5 raids when BC went live, Blizzard decided to remove them as an entrance requirement about 6 months later.
Why? The average raiding guild had to "key" each new recruit that came down the pipeline before the player could enter a tier 5 raid at all. Entering SSC required The Cudgel of Kar'desh, obliging a guild to return to Karazhan and Gruul's Lair for Nightbane and Gruul kills. Entering TK required a much longer quest chain culminating in three heroic challenges (Trial of the Naaru: Mercy being the most difficult by far) and a Magtheridon kill. Pre-nerf Magtheridon, in a sign of further things to come in BC, was a coordination nightmare that took a year off your raid leader's life each time you did it.
Hand of A'dal was like the older, bigger, angrier brother of Champion of the Naaru. While portions of the Black Temple attunement chain could be solo'd, or at least took you to the easier bosses within Tempest Keep and Mount Hyjal, access to the latter could only be granted by killing Lady Vashj and Kael'thas Sunstrider for theirvials. If your raid leader hadn't run screaming off to Bolivia after keying people for tier 5, he/she would've snapped while recruiting unkeyed raiders for tier 6. Blizzard's removal of the attunement requirements could reasonably be considered a humanitarian gesture.
Hand of A'dal was one of the most popular picks on the "most reassuring title to see on a player" article. So does it deserve the accolades it's gotten? I think so. Vashj and Kael'thas were among the nastiest bosses the game had ever seen, and required an intense amount of raid coordination and situational awareness from players. While it would be a stretch to say that anyone sporting Hand of A'dal is necessarily an amazing player, odds are still good that you won't catch them eating glue in a corner on the average raid night.
Wrath of the Lich King
Titles really took off with Wrath and haven't stopped since. Most of the "vanished" titles in WoW come from this period, when Blizzard experimented with different models for granting dungeon and raid titles, and finally decided against granting them for realm-first kills.
Rewarded by: A Tribute to Dedicated Insanity, which required players to complete Trial of the Grand Crusader on 10-man without any gear from the 25-man version.
Difficulty: Medium to high.
Wrath had an uneasy relationship between 10-man and 25-man raids. With separate lockouts, it was extremely common for raiders to do both in the average week and cherry-pick the best weapons, gear, and trinkets. For strict 10-man raiders, it was a little annoying to watch 25-man players enjoy a significant advantage in 10-man with the additional damage and survivability granted by the higher ilevels of 25-man gear.
That was the context for both Argent Defender and its tier 8 equivalent, Herald of the Titans. Want to prove you're a rad dude with 10-man content? Fine, said Blizzard. Take off your 25-man gear and we'll talk. Actually, making sure that no one in the raid was wearing anything beyond a certain ilevel (245 for Defender, 226 for Herald, with a few exceptions) was just as much a nuisance as the raid itself.
Strangely, Herald of the Titans still hasn't been removed, although your character still has to be at level 80 in order to get it.
Algalon probably wasn't the toughest fight in Ulduar (that dubious honor would have to go to Mimiron's Firefighter or a Yogg kill with few to no Keepers active), but it wasn't far behind. The difficulty, as with the Black Temple attunement, was compounded by his inaccessibility. You couldn't get your grubby little hands on the Celestial Planetarium Key without killing all four Keepers and the Iron Council on hard mode, and -- particularly with Freya and Mimiron -- that meant you were in for a world of pain.
On the plus side, any guild that managed to do it had already knocked off several requirements for Glory of the Ulduar Raider, but that was cold comfort when your "reward" for gaining entrance to the Planetarium was a touchy fight with an equally touchy Algalon, and the title itself was only yours if you managed to do it faster than anyone else. Algalon was a nightmare at the end of a parade of nightmares.
Rewarded by: Successful completion of Ulduar-10 or Ulduar-25 respectively without allowing any raid member to die on a boss fight.
Difficulty: Medium to high depending on the raid's gear.
This was the ideological descendant of Immortal/Undying in tier 7, but fortunately not part of the tier 8 meta. Ideally, you left this until after your raid was already geared and nobody needed anything pressing from the "hard modes." Algalon, Vezax, and Mimiron were probably the toughest bosses to hurdle, with a combination of instagib mechanics (hope you're not lagging!) and, in Vezax's case, the healers' inability to spare much extra healing.
Rewarded by: A realm-first kill of Kel'Thuzad in Naxxramas-25.
Difficulty: Variable. Guilds that had experience with classic Naxxramas didn't find much in their way.
This was one of three "Realm First!" achievements that debuted with Wrath's tier 7 (the others being the first Malygos and Sartharion kills). Blizzard quickly decided that awarding titles for normal realm-first kills wasn't a good idea, as it tended to reward people who blew through leveling content and pulled any fresh 80 into a raid that they could find.
Conqueror was a more involved undertaking than its Malygos and Sartharion equivalents, as there were 14 bosses and an awful lot of trash standing between you and Kel'Thuzad. However, any guild that already had experience with Naxxramas from either the classic game or nostalgia raiding during BC wouldn't find significantly different mechanics to trip them up.
The title was inspired by the famous couplet from H.P. Lovecraft's The Call of Cthulhu, much like Yogg-Saron himself was:
That is not dead which can eternal lie, And with strange aeons even death may die.
All that stood between you and this brilliant title was one of the most infuriating encounters Blizzard has ever programmed. It took the world's best guilds about four months to do it, and even toward the end of Wrath, there were servers that still didn't have a Yogg+0 kill.
Rewarded by: A realm-first kill of Anub'arak in Trial of the Grand Crusader-25 with all 50 attempts remaining.
Difficulty: Extremely high.
Anub'arak in Azjol-Nerub had been something of a pushover. Anub'arak in Trial of the Grand Crusader, not so much so. Healers spent weeks trying to perfect a technique that combined studied neglect and lightning-fast reflexes for the third phase, while the raid leader worried incessantly about how to ration the only 6 Frost Spheres you were going to get. Tanks had the comparatively minor (ha!) problems of not getting one-shot by adds or stunned and then squashed by Anub. DPS chugged potions and prayed to the RNG gods about whittling Anub's health down little by little while the bastard bug healed himself for massive amounts through the raid's own health pools.
Not only did you have to beat everyone else on the server to a kill, but you also had to do it right the first time. Any blown attempt, and you'd be staring at "49 Attempts Remaining" on your screen with the knowledge of another wasted week.
Malygos was arguably the boss with the most difficult initial learning curve when Wrath began, but was much simpler once your raid figured how to handle the final phase (or weaseled its way through it with a helpful macro). More difficult was convincing two of your guildies to drop their mains and level brand-new death knights at breakneck speed in order to have two Death Grips to yank Power Sparks to the raid. If you had that, you were gold -- and your only competition would be, well, every other raiding guild on the server.
Screw it. Just make everybody get to 80 as fast as possible, tell them to head for Sartharion, and go for --
Frankly, Sartharion wasn't very tough if you killed his drakes, and once you had a sense of the correct way to position him, he was a single-phase and relatively forgiving fight. In some ways, Obsidian Slayer was more about which raiding guild could get the most people to 80 fastest, which is the reason that Blizzard decided to nix similar realm-first normal kills in the future.
It's kind of a shame, because I still think Obsidian Slayer is one of the more badass titles the game has ever produced.