Sins of a Solar Empire has no campaign mode nor does it have a story beyond the initial intro explaining why everyone's trying to kill each other. It's alot of fun for the first couple games, but there just isn't enough variety in the gameplay to keep it interesting over the long term. At least for me.
Original War has a cool story and usual gameplay. Every soldier is an actual character with their own abilities and they can level up. Of course if they die they're gone and you've got one less soldier. Also, aside from several major ones most of the soldiers in the game don't actually have any personality. That's just because there are dozens though.
Men of War and it's predecessors are pretty awesome gamplaywise. The campaigns are great even if their stories aren't anything spectacular.
Total Annihilation is an awesome awesome game with awesome awesome music. Best Music in any game ever! Also while the story isn't very overbearing, it's enough to give direction to the campaign missions, which are pretty good.
Homeworld is great as has already been said.
Metal Fatigue has a cool story and good campaign/gameplay, though good look finding it anywhere. It also has a tendency to CTD at infrequently but seemingly at random.
Total Annihilation: Kingdoms apparently has a good story, but I never went very far into it. I got bored of it not being anything like TA.
Battle Realms is kinda cool if you can find it.
Conquest: The Frontier Wars doesn't really run well on my computer for some reason, but it's got an interesting story for as far as I got. It did RTS with 4X elements long before SoaSE too.
Mech Commander and it's sequel are good, though they're more like Men of War in that you can't build units, just get reinforcements from time to time. Not even sure those really count as RTS games. MC games have live action cut scenes, and the acting isn't too shabby.
Also good, are several other ones already mentioned.