They do use tap water, in the vast majority of cases. Tap water is able to hook up to most soda fountains’ interior pumps, providing fresh cold water constantly throughout the day.
Since fresh cold water is required to make new carbonated water, soda fountains can take advantage of the accessibility of city tap water and its temperature control to maximum effect.