Why is the rogue the one who is supposed to shine whenever the party comes up against a lock? Just as the wizard can do alot more than cast knock, a rogue can do alot more than pick locks. Both characters have invested something into the ability to open locked things. One character does not deserve the spotlight any more than the other.
It depends on which edition of D&D you are coming from. In the early days of D&D(1e/2e), the thief sucked at fighting, their backstab ability was nearly impossible to pull off, there were no social skills. The reason you chose thief was because they had Find and Remove Traps and Move Silently. There was no way to makes scrolls or wands of Knock, and Wizards has so few spell slots, they weren't going to "waste" them on knock.
Since that time, the Thief/Rogue has traditionally been "the guy who hides, sneaks, climbs, finds traps and removes them" as his role in the game. Even in early 3.0 before a lot of splat books came out, Rogues felt similar to the way they were in 2e. Other classes COULD take the skills, but none of them would be good at it, so it was best to let the Rogue handle them.
And why is this one locked coffer the only chance the rogue has to shine? Surely in an intrigue-heavy adventure a rogue has plenty of opportunities to use his skills to great effect, whether it's using social skills, picking pockets, noticing clues, spying, etc.
The real problem with the 3e/4e skill system is that it creates specialists. Having a skill isn't useful unless you are the BEST in your group at that skill.
If there is a social situation, does it make sense to allow the Rogue with a 12 Cha and maxed ranks in Diplomacy to do it? Or the Bard with the 20 Cha and max ranks in Diplomacy to do it? You'll ask the Bard to talk and the Rogue will keep his mouth shut to avoid failing the check. It's worse yet if the Rogue decided not to max social skills.
There is a "skill hierarchy" in those editions for each skill where each class is rated higher or lower. Basically, if your class uses the skills associated stat for combat in any way, you are higher on the skill hierarchy for that skill. Rogues are mid-high tier on most skills in the game. However, they are only at the highest tier for Dex based skills. Generally, that means that the only time they will be called upon to make their skill rolls are when they are Dex based(mostly picking locks and sneaking). Open Locks is one of the few skills that very few other classes also get as a class skill, so it's almost the only thing that you can count as a "Rogue exclusive".
So, the only times Rogues get to truly shine is when they gets to use that skill, most of the time. It kind of sucks. So much so that almost no one wanted to be a Rogue in any of our games. We replaced the Rogue with a Wizard with one level of Rogue so that he could max ranks in Rogue skills and find magical traps. Since Search and Disable Device were both Int based, the Wizard was better at those skills anyways and a couple of scrolls of Knock negated the need for Open Locks.
I also dispute your assertion that the wizard just waltzes in and there are no consequences whatsoever. Using one of your previous few daily spells is a consequence! Any wizard that wastes a daily spell when there's a rogue in the party that could have picked the lock for free is a fool, IMO.
Virtually no consequences. By 9th level, most Wizards have 6 level 2 spell slots and more than enough money to acquire scrolls of knock for negligible cost in 3.5e. I kept at least one knock spell prepared every day as a Wizard, whether we had a Rogue or not. Why? You never know when you'd come across a lock that had too high a DC for the Rogue, or the Rogue would die part way through an adventure and you'd need to open a door. Or, the Rogue wouldn't show up for a session(and our DMs normally made them unable to help if they weren't there for a session).
By the time you are 9th level, 2nd level spells slots are nearly useless for combat. My Wizards that got this high normally had 90-100% of their level 2 slots available at the end of every day. "Wasting" a slot to open a door didn't seem like a waste at all. At least I felt like it was worthwhile to even HAVE level 2 spells.
There's nothing stopping the rogue from trying again if he fails, except time. On the other hand, there is plenty to keep a wizard from repeatedly casting Knock. There's the limitation on spell slots, or, if he uses the ritual version, there's the very long casting time and monetary cost to consider.
Spell slots have the same problem in many areas of the game: If you don't run into situations that require you cast more spells than you currently have prepared, then spell slots aren't a limitation.
If I have 3 out of my 6 2nd level spell slots prepared as Knock spells and I only encounter 2 locked doors, then it cost me absolutely nothing to unlock those doors.
But a Wand of Knock has 50 charges, scrolls are cheap. If you prepare 2 Knocks a day and own 1 Wand of Knock, you are likely able to replace a Rogue for an entire campaign.
The ritual version is a totally different story, it's generally useless.