Working from the oral history in The Five Year Mission: The next 25 years, this is a fascinating deep dive that answers the question “How did a recycled cover of a 1998 song written for Rod Stewart, ‘Where My Heart Will Take Me’ aka ‘Faith of the Heart’ become the title music for Enterprise?”
Also, after resisting melodic scoring in all the 90s shows, it turns out this was the music Rick Berman liked?!!
“…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."
And it seems the song does have its own subniche of supporters who share Berman’s view. (But not I.)
The problem with Enterprise's theme is that it undermines the fundamental principles the series had established in every other show. The song along with its imagery may feel like it's fitting for "mankind stepping into the larger galaxy", but it does so at great expense. Everything about that opening is anthropocentric. It's all about humanity and Earth. The show is every bit as broad as its predecessors, but the opening seems to feel like the poster child for HFY fiction. It's jarring.