inkie noted that Moses is an eternally saved man, written in the Lamb's Book of Life even though he did not get to enter geographic Israel.
Yes, Moses sinned. His corporeal penalty was to not enter the promised land with the rest of the nation. Very sad.
But why did God think this one sin was such a big deal? Because this one sin messed up a beautiful typeology that God was putting into the history book. The first time the Jews needed water (water being a "symbol" or "type" for being cleansed from sin) it required that the "rock" be struck--Jesus was struck for our sins.
and all drank the same spiritual drink, for they were drinking from a spiritual rock which followed them; and the rock was Christ. (1 Corinthians 10:4)
The next time the Jews needed water (cleansing from sin) Moses angrily struck the rock instead of speaking to it. The symbolism that would have been demonstrated is that the Rock / Christ only needed to be struck / killed one time for all forgiveness and cleansing from sins. After His resurrection it only requires that one pray to the Rock / Christ to receive that complete forgiveness.
If we confess our sins, He is faithful and righteous to forgive us our sins and to cleanse us from all unrighteousness. (1 John 1:9)
As a result of our prayer, Jesus cleanses us from every single unrighteousness. Every one. No grudge, no need to earn forgiveness. No fear of it being held against us later. Forgiven just for praying.
If you accept the symbolism you can see why it was such a big deal that Moses messed this up. If you reject the typology/symbolism, then it is a perplexing and meaningless story.