This was addressed (in very considerable detail) in a series of interviews with the show's showrunners for the 'The Fifty-Year Mission' oral history book.
In brief, there was a desire to make the show hipper, more accessible for non-trekkies and to bring the whole thing closer to the modern day. There's a general willingness to accept that the theme was a mistake.
BRANNON BRAGA: Co-Creator and (Junior) Executive Producer.
Rick and I felt that a song would set the slightly more contemporary feeling we were going after with Enterprise. For the longest time, we had a temporary song we cut the main titles to, U2’s “Beautiful Day.” If we had used that—or could have afforded it—that would have been a great song. Those main titles with U2 are amazing. It’s hip and cool, whereas the song we ended up with is awful. I’m a big fan of Diane Warren, she’s a great songwriter, but this particular song and the way it was sung was tacky. I still cringe when I hear it and, by the way, I think the song had a lot to do with people’s adverse reaction to the show. If you look at the main titles themselves, it’s a really cool sequence. But the song is awful, just awful.
If you heard the theme to Next Generation, it’s instantly identifiable, even Voyager’s theme to a lesser degree. You know what it is, and you have a certain fondness for it. I don’t know a whole lot of people who have anything but a kind of ironic fondness for Enterprise’s theme song. It was a little bit out of my hands in terms of selection of the song and the singer. There wasn’t a lot I could do about it. I’m not really all that musically knowledgeable. I felt like Rick and Peter Lauritson, who was our post supervisor, were the music men, but there wasn’t a lot I could say that would have changed Rick’s mind. At the end of the day, Rick was my boss.
RICK BERMAN: Co-Creator and (Senior) Executive Producer.
This is another example of my being stubborn, right or wrong. I thought it would be nice to have a theme song. Nobody had ever done it before. I knew that I wanted the animation at the opening instead of just being the flying through space stuff that had existed on all the other Star Trek shows. But I wanted it to be sort of a compilation of the science and the people that led up to the space flight. Our visual effects people put together an amazing visual montage. Then we went to a very famous, contemporary composer named Diane Warren, who’s written huge hits. She went through a whole bunch of songs and we came up with this tune that she had written. The lyrics seemed perfect. Then she got all excited, there was a British singer named Russell Watson and he was a very hot performer. Kind of semioperatic and pop performer, and he agreed to sing it. It basically spoke to exactly what we were looking for—a dream of going out into the unknown and the whole idea of bringing one’s heart to what matters. We recorded the song and put it to the animation and everybody thought it was terrific. And the audience hated it.
In the second or third year, the network said to us, “Can you rewrite the song and could you make the song hipper?” We left the vocal on, but we did a completely different instrumental with a lot more electric guitars and things to make it a little more rock and roll. I don’t know if anybody was truly satisfied with that. I, for one, can tell you that I thought it was a great opening and I’m not alone in that. I don’t think I’m in the majority, but I’m not alone.
MIKE SUSSMAN: Executive Story Editor.
It gets back to the people running the franchise saying, “We’ve got to do something different. We’ve got to shake it up.” And kind of shaking it up in many of the wrong ways. Let’s say all the wrong ways. I don’t think it’s a bad idea to have a non–Jerry Goldsmith opening. When they showed us the opening graphics, it was set to U2’s “Beautiful Day,” which is an amazing song. Obviously we couldn’t afford that, it was all temp, but we ended up getting a Diane Warren song and she was a Star Trek fan. She gave it to them for next to nothing.
The Fifty-Year Mission: The Next 25 Years: From The Next Generation to J. J. Abrams: The Complete, Uncensored, and Unauthorized Oral History of Star Trek