A lot of houses were built with just the water pipe to connect the electric service to earth (including all the housed I have lived in). They work fine unless the water service is replaced with plastic.
The code now requires a "supplemental" electrode for *new* construction in case the water service pipe is replaced with plastic. In the past the supplemental electrode has usually been a ground rod. The rod can connect at a number of places to the ground electrode system. The rod should have a relatively short connection to the panel. It is providing a path to earth from the panel. Connection near a water faucet is not appropriate unless the connection would have a short metal path to the electric panel. By code, if connected to the water pipe, the rod must connect in the first 5 feet of pipe inside the building. That may not be the best place to attach it. The code does not require adding a rod for what you are doing (your inspector might).
You can't use the *earth* as part of the path to trip the breaker. If the outlets are connected within 5 feet of the metal water supply entering the house there is a metal path for 'ground' current to trip the breaker (assuming the electric service is connected to the water pipe, which it should be). If the metal water pipe is maintained, connecting elsewhere on the water pipe gives you a metal path and you said that is permitted by your inspector (but is no longer allowed by the NEC).
A neutral-ground wire connection is part of a properly installed service installation. You should have it now - I did not question its existence.
A junction box can be used to splice the added ground wires.
For the receptacle 'ground' wire connection, I would connect to the grounding electrode system where it runs close to your outlets. That includes the heavy wire from the panel to the water pipe.
When adding a ground rod to a system with metal water service pipe, the rod is "supplemental". It becomes the only electrode if the water service pipe is replaced with plastic. But the ground rod is to connect the *system* to earth and should be a short path to the panel. I am reading that the intent was to make the rod "insurance" for the added receptacle ground wires in case the interior water pipe path was interrupted in the future. IMHO that is not appropriate.