The most curious thing about Moshe Rabeinu's sin is that we are never actually told in the Torah what it was. Yes there is the repeated condemnation "because you failed to sanctify My name" over and over whenever the incident is recalled and but what exactly did Moshe Rabeinu, a"h, do to earn such a harsh punishment as being denied an entry visa into Israel?
The second most curious thing is that Aharon HaKohen, a"h, is also punished for the same reason but it is even more mysterious as to why. After all, all he did was stand next to Moshe when the latter struck the rock instead of speaking to it. Was he supposed to anticipate his brother's sudden change in plans and grab the stick from him before he could hit the rock?
I would like to suggest that there are two aspects that lead to the "failure to sanctify".
The first is Moshe's use of the staff to hit the rock. As many commentators famously note, Moshe was told to speak to the rock, not to hit it. They therefore conclude that hitting the rock was the sin and explain, a la Rashi, that if the inanimate and unintelligent rock obeyed Moshe's command on behalf of God, how much more so should the onlooking b'nei Yisrael. However, this begs the question as to why Moshe was told to speak to the rock instead of striking it as he had done 40 years earlier when b'nei Yisrael had just come out of Egypt.
The Rambam, in his famously controversial opinion on the purpose of the sacrificial rite in Judaism, notes that the main reason God instituted sacrifices was because this was the standard method of worship "da gods" at that time in history. According to this opinion, prayer was the ultimate goal God was aiming for but He wanted to move our ancestors slowly towards that position instead of telling them that they suddenly had to abandon the main method of worship they had always known and were familiar with.
Now Rambam has certainly been criticized by many later sages, principally the Ramban (I'm waiting to see what happens when those two meet up at tichiyas hameisim). Nevertheless the concept is intriguing and is supporting by biblical archeology. The Mishkan our ancestors built strongly resembles Egyptian mobile shrines used by their army when it went on expeditions. In other words, it wasn't this strange new structure the world had never seen before but in many ways it was familiar to b'nei Yisrael. Consistent with the Rambam's approach this is exactly how God would have wanted to start things off for the new nation as it was adjusting to its novel form of worshipping the invisible Creator of the Universe instead of a physical idol.
What's more, at various points in the journey from Egypt to Israel various items and symbols gain tremendous importance to b'nei Yisrael. There is, for example, the copper serpent from our parasha this week which healed the snake-bitten grumblers. Last week we read about the incense and saw how it could both destroy and save life depending on how it was used. Above all of these items, however, is Moshe's staff which brought many of the plagues in Egypt, split the sea, and did many other miracles according to the Midrash. One could easily see how b'nei Yisrael might believe that the staff had some power of its own, given by the Divine of course, but still containing some kind of magical power. If the point was to wean the Jews of their dependence on the physical and attune them to the non-material spiritual then they had to come to understand that it was not the staff that performed the miracles, that it was in fact completely unnecessary in the end. Only God's will and the fufillment of that will mattered.
Thus Moshe Rabeinu was asked to carry the staff but to speak to the rock. The people would see the staff, expect that it was strike the rock and that water would come forth. How great a sign would it be when Moshe would simply command "In the name of God bring forth water" and the water would flow? The lesson, that a humble heart and a desire to directly communicate with Him, would move the people one step forward in the Divine plan. By striking the rock, this was thwarted, hence Moshe's punishment. What was needed was a leader that was prepared to bring the b'nei Yisrael forward and he was still stuck with them at the first stage.
As for Aharon, the midrash tells us a fascinating story that ties into other recent events. According to one opinion of Chazal's, the incident with the lack of water occurred immediately after the death of Miriam, a"h. So immediately that Moshe and Aharon were sitting shivah when the water ran out and the people came to complain about their thirst. However, when Aharon saw the crowd approaching their tent he completely misread the crowd. He assumed they were coming to conduct a shivah call! It was up to Moshe to correct him and properly assess the situation.
We see that as b'nei Yisrael drew closer to Israel that the model of leadership was going to change. As opposed to Moshe who "did it all" including briefly serving as Kohen Gadol, the new style of leadership was going to be a dual one, a cooperation between the civil and religious branches of government. Yehoshua was the civil leader but he had to work in concert with whomever was Kohen Gadol. If Aharon couldn't read a crowd, how could he continue to function in a leadership role, especially one that required him to be an intermediary between the people and God?
It's not easy to be leader and the demands on such a person change all the time depending on who's doing the following and the circumstances they find themselves in. Today's great ruler may not be the right one for tomorrow. In the Divine scheme of things the plan must move forward and so the right leader must be in place at all times regardless of the prior's tenure or feelings. In this parshah we can see how God's desire for our ancestors to enter Israel in the right way under the right people played out.