There seems to be a difference in the ssh-agent of Leopard and that of Snow Leopard, with the latter remembering passwords indefinitely, while Leopard actually respected your option of not saving in the keychain and therefore not remembering the password for your SSH secret key.

Best workaround I’ve found so far is to just plain kill ssh-agent every minute. That way I can guarantee I can leave my Mac where ever and know that if someone tries to connect to any of my remote machines, they’ll have to type in the password for the SSH secret key, and at the same time I’m not messing around with Apple’s plist files to change the start options for ssh-agent and trip something down the line during an update or something.

Workaround
Open Terminal.app (or your choice of terminal application)
Type: crontab -e
Enter this on a newline:
* * * * * killall ssh-agent >/dev/null 2>&1
Save and come next minute Snow Leopard will start behaving the way you intended and were used to in Leopard, which also happens to be the right way for most people I know.