There are applications that can do this such as:
CipherWorks by SubrosaSoft and Ghost Sphere.
However they are overkill and needlessly complex for most situations.
If you search these forums I think you will find that most of the recommendations are to use the Encrypted Disk Image feature that is built into Disk Utility.
Creating an Encrypted Disk Image will probably meet your needs because it requires a password to be entered before any of the data stored on it can be accessed. It also provides a fairly good measure of additional security by actually encrypting the data rather than just hiding it.
It won't automatically ask for the password when you connect the external drive, but it will when you attempt to open the disk image.
As long as you are using MacOS X 10.3 or above you can create a 'Sparseimage" which will be much easier to set-up and can be read by any Mac running 10.3 or above (after entering the password of course)
See this Apple document for more specific info about setting-up a Disk Image
http://docs.info.apple.com/article.html?artnum=107333