On Snow Leopard, the default unzip tool won't ask for password but just report it can't unzip?
I don't think the built-in decompression utility can unzip password protected files.  However, The Unarchiver is the way to go for all things compression related on OS X anyway.