The sin of Moshe hitting the rock and not speaking to it, is mentioned in the Parsha. There are many interpretations as to why he was punished so severely that he was not allowed to enter Eretz Yisrael.
The simple explanation is that Moshe did not listen to Hashem's instructions and could have sanctified Hashem's name, but did not. Desecrating the name of G-d is very severe.
The Rambam says that the main sin was that Moshe allowed his anger to overtake him. One who gets angry is as if he has no faith in G-d. Many say that conquering anger is possibly the most difficult negative personality trait to overcome.
The Kli Yakar says that at the beginning of the forty years in the desert, Moshe was commanded to hit the rock because the people were a tough generation as they were used to being slaves. However, the generation were a "pampered" generation where all of their needs were taken care of. Moshe showed by his behavior that he could not lead the people into Israel. He needed to adjust to the new personality of the people by being kinder and more gentle.
All of these reasons give us something to think about in applying to today. We must sanctify Hashem's name and conquer our anger. And our leaders must understand the nature of the people in order to lead them. Shabbat Shalom