Plenty of people who play D&D 5e like to use that game's Sleight of Hand skill to determine whether a character can pick a lock. It's a popular house rule, advocated by at least one popular website and implemented in Baldur's Gate 3.
It's also wrong.
To see why, let's examine the rules. From the SRD, lest Hasbro's highly trained attack lawyers take umbrage:
Sleight of Hand. Whenever you attempt an act of legerdemain or manual trickery, such as planting something on someone else or concealing an object on your person, make a Dexterity (Sleight of Hand) check. The GM might also call for a Dexterity (Sleight of Hand) check to determine whether you can lift a coin purse off another person or slip something out of another person's pocket.
You know what that doesn't mention? Picking locks. Where is that mentioned? Under Tools:
Thieves' Tools. This set of tools includes a small file, a set of lock picks, a small mirror mounted on a metal handle, a set of narrow-bladed scissors, and a pair of pliers. Proficiency with these tools lets you add your proficiency bonus to any ability checks you make to disarm traps or open locks.
Notice, it doesn't actually say you have to use the tools to gain the proficiency bonus. This is important!
Meanwhile, in the Equipment section, there are such lines as [...]a creature proficient with thieves' tools can pick this lock[...]. No mention whatsoever of the Sleight of Hand skill. That, to me, is pretty conclusive. The 5e designers intended for proficiency with Thieves' Tools to be used for picking locks. Not proficiency in Sleight of Hand.
And why would it be the latter? A brief consultation of the Wikipedia page for sleight of hand will tell you what the skill covers. Manipulating objects cleverly with in order to entertain or deceive. It's true that someone with good manual dexterity is probably better than the average bear at picking locks. But the crossover between that and card tricks is really quite small.
In short, then, there is no reason - rules-as-written or logical - to think that the Sleight of Hand skill is applicable to picking locks.
So, how should it be done?
By making a Dexterity check, of course. In 5e, a player doesn't make a Thieves' Tools check, or a Sleight of Hand check. They make a Dexterity check, with bonuses as appropriate. The question is, then, what bonuses are appropriate?
Like any good old-school Referee, I tend towards an interpretation that the rules are suggestions, and shouldn't be construed as saying a character can't attempt an action. Any reasonable attempt at picking a lock ought to be given a chance at success. Which is where the Dice Goblin's suggestions about Time, Gear and Skill come in. And also the rules from Xanathar's Guide to Everything which state that if a character has a skill and the relevant tool, they make the check at advantage.
You see, 5e's 'Artisan's Tools' proficiencies are actually skill proficiencies. For some reason, the designers chose to have two skill systems. No, I don't understand it either.
What that means in practice? If a character wants to try and pick a lock, and has dreamt up a plausible way of doing so, think about Time, Gear and Skill.
If they have all three on their side - they have Thieves Tools, are trained to use them, and aren't in a hurry, they just open the lock. No check required, time just passes. If they're rushing, don't have the proper tools, and only a vague idea how to open a lock, then it's staying locked. Sorry, but that's life.
In between?
If they have plenty of time to mess around, the proper tools, or the proper training - but only one of the three - then it's a straight Dexterity check against the lock's DC. If they know how to use Thieves Tools, they get to add their proficiency bonus. Otherwise, they'd better hope they're lucky, or it's an easy lock.
If they have two of the three, they get to make the check at advantage. This is the properly-equipped thief who's hurrying, or who has plenty of time but lost their tools somehow and is improvising with a hairpin. Though they do still need to justify where they found the hairpin! Or the Fighter who figures they might as well bring some Thieves Tools to mess around with while the wizard is waving their hands around and chanting.
Given that good game design is that a character doing something they're good at should succeed about two-thirds of the time - this actually checks out. A standard lock in 5e requires a DC 15 Dexterity check to open. A first-level Rogue who's built for it probably has a +7 to Dexterity checks to open a lock, and needs to roll an 8 or higher to succeed, even when they're improvising and in a hurry. That's as close to two-thirds as rolling 1d20 allows. If they've got their tools, or have plenty of time, they'll almost always succeed - which is the class fantasy!
You can apply this approach to challenges involving other types of Artisan's Tools, of course. That's an exercise for the reader.
Why do people do this?
This come up because people don't understand Dungeons and Dragons. Both how to design the game, and how to run the game.
On the design side, lots of people think it's a good idea to have something important to 'their story' - and that's a problem in its own right! - behind a lock. If the players need to get through the door, open the chest, or whatever, then they have to be able to open the lock. If nobody has the right tools and training.... well, that's your carefully crafted railroad come to a crashing halt. End of adventure, the princess ate the dragon, the prophecy was not fulfilled, whatever. So, you need to find A Way to get the door unlocked. And, in amongst the 5e skill system, the thing that looks least unlike 'open a lock' is Sleight of Hand, apparently.
Secondly, at the table, the modern play culture doesn't actually like challenge all that much. The characters are viewed as fantasy superheroes who can solve any problem. And a simple, mundane lock... that's not something that should really pose a serious problem to such characters. So, when faced with it, they look at their character sheet in a panic, and once again - will Sleight of Hand work? The long-suffering referee has been trained to say yes to everything, since lots of GM advice can't tell the difference between a D&D table and an improv troupe. So, of course, they say it will.
But D&D isn't meant to be about fantasy superheroes who can solve everything. And there's not meant to be one way to solve a problem. If the door is locked, you find a solution. Which might not be as elegant as picking the lock. The Barbarian can knock it off its hinges. The Wizard can cast knock. The trained carpenter can cut through it. The Dwarf can get their clan to send some labourers to tunnel through the rock.
And, if nobody can figure it out? That door is staying locked. And whatever's on the other side of it, the players don't get it. Sorry. If it was important, there should have been another way to get it.