It depends a lot on the time period and area you’re asking about, but in the era I study British ships carried water with them in casks (barrels) and were starting to experiment with dedicated water tanks aboard. Captains would replenish their water supply at streams or rivers whenever possible, and maps/charts would generally include descriptions of watering places.

Most of the fresh water aboard a ship of this era would be used to soak salt meat before cooking — I’ve got 90% stuck in my head for some reason. Sailors would be provided a butt of water with a scuttle knocked in it (a scuttled butt, dontchaknow) for drinking during the workday, and water would be mixed with rum to make grog for ships going far overseas. (Sailors got beer in home waters and wine in the Med.)