Bear in mind that very few people thought a film adaptation of LOTR was actually going to be any good. It seems obvious now, but it was not seen a really viable project with how trilogies were made at the time (with many years between them) the amount of budget needed, and the effects challenges. The guys who made the movies deserve a lot of credit for doing something so well that it looks obvious when they are done, but little thinks like how to do the hobbits were seen as effects challenges with no real solution that wouldn't look stupid or be insanely expensive.

Connery would have been the biggest name in the film and getting attached to something that wound up looking like shit or making him look stupid would have been a bigger problem for him than anyone. Again, its obvious that they turned out great, but look at what Peter Jackson made before this and its not really as obvious a choice as one would think now (although I am personally a fan of his early work, I get why people might not be and why people maybe wouldn't hand him the biggest film franchise in history).

And just for a fun tidbit, at the time that Connery bailed on the offer the film was being produced by Weinstein, so I get maybe not wanting to work with that dirtbag. They almost lost Liv Tyler for the same reason and reportedly did lose them Ashley Judd and Mia Sorvino, who flat out refused to be attached to anything involving him.