I'd personally be a bit more conservative with the underwriting and make sure to see what the carrying costs could potential get to especially if this is a bigger renovation and not just cosmetic. 

Since your friend covers utilities, this offsets some of your cost.

very simple go to the county planning department office show them the map and ask them if surface drilling is allowed in their zoning code..

You'll have to understand most of these investors are going to counteroffer.

I dont' think this is any different than wholesaling- it's a chicken or the egg thing.

  :)   Or marry a full-time Realtor.Are you going to run a syndication or just give them money to invest?

However, with the frequent incidents and the persistent odor, even after cleaning, I’m beginning to wonder if this has become the norm for STRs in urban areas.Any advice or tips on how to prevent this would be greatly appreciated!

There are plenty of other owners on this forum. 

(Current cost to bring in water line is around $140k or a well starts around 40k with no guarantee of hitting water) What made you interested in investing in this type of deal?

Every baby agent is going to sell you on a vision on their market and you are going to take the bait.