I like the product Descale It, which you buy at Leslie’s Pool Supplies. It’s made for cleaning hard water scale off of pool tiles, but it works well on tile and glass in your home.
It’s some form of acid, so like justanothermike says, you have to be careful not to get it on you. I spray it on and let it stand for five or ten minutes, and then go at the spots with steel wool. It works every time.
I’ve found cola works well on water spots on glass. Spray heavily, let sit for a few minutes, scrub a bit with a scratch-free scrubber, and rinse. Bonus: you won’t have to worry about breathing in fumes or damaging your skin.
Is the ingredient in cola that cleans glass the same one that destroyed a tooth in a glass in grade school science? Calcium is calcium, right?
Would diet cola work? That’s all I have.
It sounds like you might have an etching problem and not a stain one. If water stains are left untreated for a long period of time, the minerals will chemically react with the glass to cause corrosion, meaning the stain has “eaten” through the glass surface to cause microscopic pits and scratches and will get embedded in there. Since the stains are under the surface of the glass, no amount of scrubbing, (even with elbow grease), is going to remove those stains.
Depending on how deep the stain is etched in, you might be able to use a polishing compound to get it out. If it’s too deep for the compound, you’ll need to do some wet sanding to really grind it out. Make sure you or whoever (ideally a professional) is doing the sanding knows what they’re doing because you don’t want to create an uneven or bumpy surface to cause optical refraction.
Diet cola is what I used. It worked fine.