After years of on-and-off searching I finally managed to discover what the song was that the TF guys used in the opening demo of their insanely popular Quake mod.
It was from a collection of songs known as the Carmina Burana by Carl Orff. The theme song is number 5: I Primo Vere - Ecce gratum. Apparently Primo Vere means "In Springtime" or "the first Spring". Ecce gratum roughly means "behold, the pleasant (Spring)". If you're interested you can view the lyrics (with translation -- I know my Latin is pretty rusty).
It was great to be able to finally hear the song in something of greater quality than the woefully encoded wave file included in the TF package. The song brings back a lot of memories and I've blogged the details here in the hope other people will be able to find the song and experience the same thing.
If you happen to have the right plugins installed, the song can be found at http://media.putfile.com/05-I-Primo-Vere---Ecce-gratum. If you don't have the right plugins, you might be able to listen to it by trawling the HTML source code ;)