I saved my cakewalk home studio midi file onto a USB stick. I then downloaded Sonar X3, and opened it in there. However, it won't play. How do I make the sound work? or do I need to download something different? 

MIDI files still work the way they always have.
You need to send the MIDI data to a soft synth to get sound.

All versions of Calkewalk going way back can save your song as a project file=CWP.
This will save everything about the song including which soft synth you used. Example the TTS-1. 
If all your versions of Cakewalk are on the same computer they share all the same soft syths and plug ins. 
So the song if saved from Home Studio will sound almost the same when opened on all newer versions. I still have my Home studio files from 2007 and I opened one the other day. Sonar X3 will just warn you that it might not sound exactly the same as some plug ins will have changed a bit. 
 
You can save straight up MIDI songs ( no audio)  as a MID. file and open it on any DAW.  Or even play it in Win Amp or Media player. Windows music players use the MS Wavetable synth. But all DAW programs need you to run a soft synth to be able to play MIDI tracks.  In Sonar you use the "insert soft synth" and away you go. TTS-1 is the GM player. Most others are not multi players. 

In my version of Sonar, you have to get up Options -> MIDI Devices and checkbox what midi output (and input) you are using. Besides marking the output for my midi Output 1, I have the other option I could use which is Microsoft GS Wavetable Synth which then uses the General Midi softwave table (GS sounds) included from Microsoft in the OS. And then each output of each midi track is where you would choose what instrument you want to use which was determined first in Options -> Instruments then marking those first after choosing the Midi Device.
 

In X3 if no MIDI output device is defined, opening a MIDI file should automatically add TTS-1 and play the file through it. This is new to X3.