Water used for brewing must be high-quality. A common adage is if your water tastes good, and does not have chlorine in it, it will make good beer. This is true for most brewers. If your house tap water tastes good, and does not contain chlorine or chloromine, you will be fine. If your water tastes good, but has chloromine in it, you must either use a campden tablet, or buy water from a store. If your water does not taste good, do not use it.
Common off-flavors from bad water can include a metallic, medicinal, salty, or astringent character in the finished beer. Medicinal flavors will stem from chlorine or chloromines present in the water, which react with yeast during fermentation. Metallic, salty, or astringent characters usually will stem from the water being too hard (too many minerals), or if the specific mineral content is off in some way (ex. too much gypsum). If these flavors are developed, and you are brewing with tap water, you may consider buying water from the store.
When buying water from the store, spring water is the best, if you are not planning on adding additional minerals. Spring water contains minute amounts of minerals that yeast need (that cannot be found in malt) to go through a healthy fermentation. If you are planning on making specific mineral additions to your water profile, distilled water is better. Distilled water does not contain any trace minerals, and creates the blank slate for your mineral additions.
Keep in mind, making custom water profiles requires good knowledge of what you are doing, and should only be done if brewing all-grain beers. Mineral content has a much bigger impact on the mash, and can help emphasize specific flavors and aromas in the beer.
For a detailed explanation of brewing water, Water by John Palmer and Colin Kaminski is an excellent resource.