It's technically piracy so I won't link to the site, but there is a patched version that includes DirectX fixes that allows the game to run fine on modern versions of DirectX and Windows. It doesn't work well with multiple monitors and you may have to tweak settings to avoid the dreaded mouse lag problem, but overall it works surprisingly well. I was in the same boat and had a strange inclination to play it after watching an episode of TNG, and scratched the itch after finding that.
The game really is fun, but it gets kind of monotonous after a while, can be very micro heavy, and yeah, the AI cheats like there's no tomorrow. The game is actually markedly harder if you start at low tech levels since the computer effectively gets a bigger jump on you. Within the first 20 turns or so I remember being at war with the Klingons because the AI isn't smart enough to play well and instead randomly demands money from you until you can't pay up, then gets mad and declares war. Anyway, they assaulted an outpost I had with 6 or 7 destroyers, when I had to stretch my economy to the limit to afford 2 miranda class destroyers. With the outpost's help I was able to actually repel the attack because the AI is also pretty dumb with its fleet tactics, but if I'd been a little less lucky in combat the game would have ended right there.
I never stopped producing warships in every system that could after that, and as of now, on turn 420 or so, I have I believe 15 galaxy class ships, 10 ambassador class ships, about 30 sovereign class ships and about 10 defiant class ships. It's actually bad enough that I was borderline going negative income from fleet upkeep, but hey, it still wasn't enough to deal with the Borg. I originally thought they must have spawned in Romulan territory and spread to me after clearing it, but after reloading the game I'm pretty sure the cubes were literally spawning randomly in my systems. Either that or they can move about 20 squares per turn, which I don't think they can. I believe they move 3 squares per turn normally.
Anyway, another small nitpick - winning as the Federation is way harder than it should be. They get (reasonably) upset when you bomb or subjugate other empires' or species' planets, but that's the only way to win the game. So... it really draws out when you have to wait 10 turns for morale to recover before you blow away the next unlucky group of aliens. I tried some tactics I read about where I just used intelligence to sabotage everything until systems rebelled and then invited them into the Federation, but even with 15K intel over 100 turns or so, that didn't seem to be working. Never saw a single rebellion.