What sour beers would be a good starting point for someone that enjoys pale ales and too strong of a flavor profile may turn them off at first?
I'd start an introduction to sour, or wild ales with the more approachable Lambics, like Lindemans Framboise Lambic. With a beer like that, the sour notes from the wild yeast are offset by the sweetness of the fruit and the pleasant texture from the fizz of the carbonation, making it an approachable drink not only for one not used to sour ales, but for one not used to beer at all.
Another option (for a North American) might be a beer categorized as an American Wild Ale, such as Russian River's Consecration, or Sierra Nevada Brux which will have the sour character but also be conscientious of the flavors favorable to the American palate.
I would highly recommend Rodenbach as a starting point. It is relatively inexpensive, surprisingly available for it's style, and damn tasty. It is a Flanders Red style, brewed with sour cherries, of which there are many similar brands ranging from candy-sweet to very sour. Rodenbach is the perfect balance of the two IMO. If you like the style, graduate to the Grand Cru, which adds more age, funk and sour.
After these I'd check out Flanders Brown ales (similar though less cherry), and Geuzes, such as Almanac's Golden Gate Gose. If you're feeling adventurous and spendy, make your way through Almanac's unique "Farm to Barrel" series of wild fruit beers, as well as The Bruery's Oude Tarte and possibly Sour in the Rye (warning, this one will melt your tastebuds).
You might also want to try a berliner weisse. They can be pretty sour, but are still very drinkable (at least in my opinion). Some breweries have these on tap, and offer a choice of flavored syrup as a sweetener (e.g., raspberry, strawberry, peach).
In terms of bottled ones to look for, Dogfish head has Festina Peche. My current favorite is Cruiser, a recent release by the Ithaca Beer Co. available in 6 packs and pretty reasonably priced. Berliner Weisse are usually available this time of year, so check your local beer store for some other seasonals.
In general, G(u)euze Beer is pretty sour. More on that here. Since I don't like Geuze beer, I don't know much about it. However, I once tasted 7 different Geuze beers from a region in Belgium around Brussels and those were really sour. I wouldn't recommend those.