I am not 100% sure what application is making the sound (first guess is Finder, second is Messages) but I am pretty sure that noise is made when your computer connects to the network. I do not know how to turn it off, but maybe this information will help you track down a solution. Please let us know if you are able to turn off the network connection sound.
Yes, it's definitely a network thing, as when the wi-fi is turned off, no sound is heard.
It must be some sort of background service that requires access to the internet, as I have tried each common application one by one, opening it, putting the computer to sleep, then waking it again. As I type, I can't get the sound to play any more. 'Messages' is running, according to the Activity Monitor, so I don't think it's that.
I guess I'll have to take a snapshot of what processes are running now, and again once the sound decides to play again!