Moshe dies right before the Israelites invade the land of Canaan. The Torah makes it clear that this is not an accident; it's a punishment. What is it a punishment for? There are actually two versions in the Torah. The less familar one, stated in parshas Devarim, is that Moshe is being punished along with the generation of the desert for the sin of the spies. The one most of us are more familiar with is the sin of Mei Meriva, when Moshe hit the rock.
What exactly did Moshe do wrong in the story of Mei Meriva? There are actually multiple opinions about this, and the Torah does not say explicitly. Most of us assume that Moshe's sin was hitting the rock after Hashem had told him to speak to the rock; presumably the reason that this theory is so common is that it is endorsed by Rashi.
In this case, I find Rashi's position compelling. So let's assume that Rashi is right. Hashem told Moshe to talk to the rock, and Moshe disobeyed a direct order and hit the rock instead.
That raises a big question. What was Moshe's motive? Why didn't he just talk to the rock?
Another question -- not so big, but still a question -- why does Hashem say that Moshe (and Aharon) didn't believe in Him, and desecrated His name? I can understand how disobeying a direct order would lead to the punishment of not going into the land -- it's arguably not such a big punishment, Moshe lived 120 years -- but why would hitting the rock constitute 1. a desecration of Hashem's name and 2. a lack of faith?
To find out why Moshe might have hit the rock instead of talking to it, let's take a look at what he says to the Israelites right before he hits the rock. "Listen you rebels, from this rock, shall we bring forth water?"
Grammatically, this is a question. But what kind of question is it? It doesn't seem to be the kind of question where Moshe is actually asking for information. It sounds like a rhetorical question. Right?
Whenever there is a rhetorical question, there is an implied answer. What is the implied answer here?
One possibility is that Moshe is implicitly asking whether the Israelites want him to draw water from the rock. Maybe it could be rephrased as "Hey guys, want me to draw water from this rock?" in which case the implied answer would be "yes" -- Moshe knows that the Israelites want water, they've made that painfully clear.
But there's another possibility. Imagine that there's a natural disaster today that creates a water shortage in some area of the world. There's no running water, no water in the stores. Imagine a family where all the kids are whining and complaining to the father (or the mother) that they're thirsty and they want water. In a fit of frustration, the father picks up a broom and says, "What do you want from me? Do you think I can just hit the wall with a broom and water's gonna come out?" Maybe Moshe was saying something like that. "What do you want from me? Do you think I can just hit a rock and make water flow?"
The big difference between the story I just told and the story of Mei Meriva is that the father in the story has no magical powers and has no reason to believe that a miracle will happen. Moshe, on the other hand, was told by Hashem that if he talks to the rock, the rock will indeed bring forth water.
Which would explain why Moshe hit the rock and didn't talk to it -- he didn't want the rock to bring forth the water.
Which begs the question, of course -- why not? Why didn't he want the rock to bring forth the water?
In the story of Mei Meriva, the Israelites were asking for water. That's a reasonable request -- a very reasonable request. Water is one of our most basic needs. Without water, they might all have died. However, they didn't ask very nicely. They said "why did you take us out of Egypt" and other not-so-nice things. They started a riot. Without divine intervention, Moshe himself might have been killed.
This probably bothered Moshe very much. But somehow, it didn't seem to bother Hashem at all. Hashem didn't even condemn the Israelites' behavior. He just said "give them water" and He told Moshe how. At that point, maybe Moshe snapped. He didn't think they deserved water, at least not without a good talking-to.
If this interpretation is correct, it makes perfect sense that Hashem would say to Moshe (and Aharon, who was apparently in on Moshe's plan, though the Torah is coy on those details) that he failed to sanctify Hashem's name. In this instance, Hashem -- for whatever reason -- was focusing on performing the miracle of producing the water, which would be a spectacular show of power. Moshe tried -- and ultimately failed -- to get in the way of that.