Why did water stored on premodern ships "go brackish" or "go bad" while at sea?

I think there are a few issues to consider.

Firstly, how pure is the water to start with? Even in their home ports, the water was often taken from local water sources that wouldn't be considered safely drinkable by modern standards

When Admiral Duncan complained in 1796 that the water delivered from Deptford was brackish, the Victualling board minuted that this could be attributed to the very dry summer, 'whereby Fresh Water has been prevented from coming down the river in the usual quantity and the Salt Water has in consequence flowed up much higher than is customary, as the greatest attention is paid by our officers in having the casks filled at the proper times of the Tide'. In November 1800, for instance, Edward Riou of the Amazon, according to the Victualling board minutes, 'complains of very muddy and thick state of the Water'; a month later William Bedford of the Leyden complained that it was unfit to drink and his surgeon had reported that it was 'much impregnated with salt'.

^{Sustaining the Fleet 1793-1815, Roger Knight & Martin Wilcox, p.60-61}

Or, in some cases, stored for months before being issued to a ship.

January 16th 1788
Grog was this day issued to the seamen, the beer being expended and as is pretty usual is attended with some riotousness. The water we have at this time is Thames water filled six months ago at Deptford in new unseasoned casks. The astringent principle of the oak and the iron rubbed off the cooper's tools have impregnated it so as to form a dilute sort of ink. Putrefaction has made it fetid and stinking. I would appear to be impregnated with inflammable or hepatic air, first from the taste, second smell, third discoloration of silver immersed into it.

^{Surgeon Leonard Gillespie's Journal, 1787-91
(from Shipboard Life and Organisation 1731-1815, NRS vol. 138)}

On service, the ships would top up their stores of water whenever they could.

Normally a ship carried water for only half the period of time it carried food and other stores. Thus an eighteenth century ship fitted and stored for foreign service would have provisions for six months, but water for only three. She was expected to replenish her water supply at any suitable creek or river, when the opportunity arose.

^{The Arming and Fitting of English Ships of War 1600-1815, Brian Lavery, p.210}

What was considered "suitable" was, I suspect, driven by circumstances. Desperation could easily mean that what was brought on board could be far from what we might consider drinkable today. Especially, if the ship was in the tropics and the only water source available was a stream through a mosquito-infested marsh.

Another consideration is how the water was stored. On-board ship, it would have been held in large casks stored in the bottom of the hold. Once these were empty, they could sit in the hold until they were refilled (at a "suitable source"), with this cycle possibly happening several times on a voyage. It's very unlikely that they would have had the time or equipment to sterilise the casks between refills, so anything growing inside the casks in the hold of the ship would taint any new water that filled them. Also bear in mind that the hold of a wooden warship would be almost constantly damp and home to vermin large and small (rats, mice, insects of all kinds...and sailors).

The bottom of each hold was ballasted, first with pigs of iron and then with a layer of shingle, deep enough for the bottom tier of casks to nestle in. The ideal was "bung up and bilge free" (the bung being in the widest part of the cask), but the shingle would invariably become wet and foul. Quite apart from the water continually making its way down the ship (and consider the state of the water that came off an anchor cable which had been lying on the bottom of a harbour where every ship's head vented into the water), there must have been many men who, although this was strictly forbidden, found shingle ballast more inviting than the seats of ease exposed to the weather.
...

Despite the 'bilge-free' ideal, that bottom tier of casks would be almost permanently lying in wet shingle. This must have led to early deterioration and would explain the constant plaint from commanders-in-chief on all stations of the difficulties of maintaining stocks of water- and wine-casks, this despite the rule that ships going on foreign service were to be supplied with new casks.

^{Feeding Nelson's Navy, The True Story of Food at Sea in the Georgian Era, Janet MacDonald, p.79-80}

These problems with casks are part of the reason that, towards the end of the Napoleonic Wars, the Royal Navy started to install iron water tanks on their vessels.

Since the bulk of the water casks were at the bottom of the hold and because on most ships the water (which could be 300-400 tons for the larger warships) formed part of the ballast, it had a significant effect on the trim & draught, and therefore the sailing qualities of the vessel. The solution to maintaining the ballast effect, as fresh water was consumed, could also have an effect on the future contents of the casks.

The draught problem could be extreme. Consume too much of the hold's contents and the ship will lighten and rise in the water, becoming, since she carries a lot of weight in her guns, top-heavy and at risk of capsizing in rough weather. However, there was plenty of handy stuff to correct the problem; seawater. Many logs report filling water casks with seawater when the fresh water was getting low. One wonders to what extent they then flushed out these casks after emptying the seawater, or whether they just drank it slightly brackish. It cannot have tasted any worse than some of the stuff they got from Deptford Creek...

^{Feeding Nelson's Navy, The True Story of Food at Sea in the Georgian Era, Janet MacDonald, p.79}