Authentication is likely per-hostname. So be sure that since you have example.com aliased to www.example.com, all authentication requests and responses are within the www- domain. Also, redirect all requests for the non-www hostname to the www- hostname to prevent problems caused by non-canonical hostnames in incoming links to your authenticated page(s).
The first time I visit, the pop-up window asks me user name and password for AuthName "Secure Area", which is right. But then another one asks me for AuthName something else, which I have no idea with.
The other odd thing I notice is that above apache conf works fine for another Drupal installation that hosted on the same server.
I highly suspect it is because of something with Drupal but I cannot figure out yet.
And what was that module doing -- redirecting to a different www/non-www hostname after the authentication, as I postulated?
Again, you should put a redirect in place to make sure that requests for the non-canonical hostname get redirected to the canonical hostname. But only after making sure that nothing on your own site ever links to the non-canonical hostname. That is, the purpose of the redirect is mainly to prevent *other sites* from linking incorrectly to your site, and sending visitors through the "two-login" loop that you encountered...
The likelihood of someone (another webmaster) linking to the wrong hostname goes down if they themselves get redirected to the correct hostname when visiting your site. It doesn't eliminate the problem completely, but it helps a lot.