I think you answered it when you said
My experience is that SharePoint only gets really ugly when an organization tries to push it in a direction it isn't designed for
Companies seem to tend to bring in the product expecting it to be a magic bullet that will quickly reduce their staff of developers. And of course when trying to do this it gets very frustrating. I agree with you, if you keep within its intended domain it works well.
BTW this certainly isn't unique to SharePoint. This happens with many if not all vended packages. Its apparently very hard for customers to stay within the bounds of the product.