I'd argue that rock could work in Star Trek, but there are some fundamentally different creative conceits that Trek would need.
For one thing, in general, Star Trek would need to be acted and filmed in a way that's less overtly stately, less reserved, less sanitized. Rock is inherently a visceral sort of music, and modern Trek -- basically anything from the Berman era -- tends to be averse to that which is visceral. If Trek is more emotional, rock can work.
And that's the key, really -- making Trek more emotional, less stoic. That's why it really worked to have the Beastie Boys in ST09 -- because J.J.'s new adaptation is much more visceral, isn't as afraid of human emotion, isn't as sanitized and self-consciously stately, as Berman-era Star Trek. It's much more human, that way, and rock'n'roll fits better as a result.
And if you do that, that opens the doors to a lot of good, time-tested rock'n'roll that can be used effectively. The Rolling Stones, Elton John, Eric Clapton, Led Zepplin, Janis Joplin, Jefferson Airplane, Mowtown, Jimmy Hendrix, Nirvana, Pink Floyd, Bob Dylan, Queen, Crosby Stills Nash and Young, the Who, Aretha Franklin, the Police, Sting, Bruce Springsteen, and the Beatles -- especially the Beatles -- are all wonderful artists whose work covers a wide emotional and intellectual range. They can all be called upon in an episode or film for use as emotive, commentary-laiden music, or even as music within a scene if you have a character who's familiar with the mid-to-late 20th Century a la Tom Paris. More modern artists like Alanis Morrissette or Sarah McLachlan could also be used, though it would be important to use them much more sparingly -- if it's an artist who hasn't been thoroughly established as a genuine classic like the ones above, I'd suggest that it's generally a bad idea to use a recently popular song, and that it would be better to use a lesser-known song than one that's been a big hit in the last several years.
And, of course, it would really all depend on what's right for a given scene. You might have a scene where it's really much better to compose an original orchestral work. Buffy the Vampire Slayer did this sort of balancing act brilliantly -- one episode might feature an artist who's been very popular but use a song of theirs that wasn't uber-famous ("Goodbye to You" in "Tabula Rasa"); one episode might feature a classic rock song (The Sundays's cover of the Rolling Stones's "Wild Horses"). Other episodes, though, might use original orchestral compositions -- "Sacrifice" at the finale of "The Gift," for instance, or the "Suite" from "Restless," or "Close Your Eyes" from "Becoming, Part II." It really just depended on what was right for the scene.
And there's really no reason you can't do that on Star Trek, too. Sure, it's set in the future -- but rock music is really just as established as any other kind of music now; it's going to survive, and it's going to produce works that will endure and be considered timeless classics, too. People will still be listening to rock music in 2366. The key is not to over-do it, and not to do it in a way that grounds the episode in whatever decade it was produced in. That's why, for instance, Ronald D. Moore was able to use "All Along the Watchtower" in Battlestar Galactica -- it's such a classic song that's been around so long that as long as you use a cover version, its use does not ground the episode using it in either the 1960s or in the 2000s.