God punished Moses and Aaron because they dishonored Him before the people of Israel. How did they do this? First, they did not obey God's instruction. Rather than speaking to the rock, they struck the rock. And not once, but twice. Interestingly, in a similar incident at the beginning of their journey over forty years before, Moses was commanded at that time to strike the rock to bring water out for the people (cf. Exodus 17:6). But this time the instruction was different. So it was not his striking or not striking but his disobeying the command of God.
Furthermore, listen to the words Moses used: "Listen, you rebels, must we bring you water out of this rock?" They did not give credit to God but tried to take credit for it themselves. Since when did Moses and Aaron have power of themselves to do this? Joseph's example is instructive here. When Pharaoh said that he heard that Joseph could interpret dreams, Joseph wisely replied, "I cannot do it... but God will give Pharaoh the answer he desires." He gave glory to God. Moses and Aaron did not give the glory to God. Worse for Moses, this incident happened likely in the very year that they were to enter the Promised Land. So after forty years of faithfulness, he dishonored the Lord in the last moment.
This is instructive for us. How we end our days is more important than how we begin them. There are many other Scriptures that teach this principle more directly:
But when a righteous man turns away from his righteousness, commits iniquity and does according to all the abominations that a wicked man does, will he live? All his righteous deeds which he has done will not be remembered for his treachery which he has committed and his sin which he has committed; for them he will die. ... When a righteous man turns away from his righteousness, commits iniquity and dies because of it, for his iniquity which he has committed he will die. Again, when a wicked man turns away from his wickedness which he has committed and practices justice and righteousness, he will save his life. Because he considered and turned away from all his transgressions which he had committed, he shall surely live; he shall not die (Ezekiel 18:24-28).
Even if a righteous person lives righteously all their life but turns away at the last moment, notice that God says that He will not remember any of the righteous deeds they have done from the beginning. How one ends is more important than how one begins. But for those that may get wrong-headed about this, and try to find an excuse for sin, it should also be remembered that those who begin well are more likely to also end well.