Back in Exodus there was a shortage of water and Moses was told to take his staff and hit the rock and water flowed copiously for the Israelites. This time, years later, he was told to speak to the rock. Instead he hit it again. His punishment for disobeying God was that he (and Aaron) were denied entry into the Land of Israel.
God said, “You failed to sanctify me.” But how? There are many different explanations. Firstly, he was expressly told to speak, not hit. A higher level of miracle emphasizing the word over the action. Then he said, “Listen to me you rebels.” Such disparagement implies a loss of temper. But why if god had instructed him? He went on to say, “Shall we bring out water for you?” when in fact it was God not him.
Even so it does seem harsh after forty years bearing the brunt of this fractious people that Moses should be denied the only thing he asked God for. Perhaps we are meant to learn from this that even if we serve God completely, we still cannot expect guarantees that our wishes will be met. But I wonder if the real reason is not that God had decided anyway that the man who started the long journey should not be the one to complete it. The propensity for the Israelites to worship other symbols and gods was clear. That was why no one would know where Moses was buried so that he would not become an object of worship or pilgrimage.
I do not believe the Torah wanted us to do such things. Our propensity for using human substitutes is too common. We should be worshiping God alone. Even the greatest of men, Moses, was never the object of our worship.