CRAM-MD5 requires that the server knows the actual password, not just some image of the password by a hash function. So if the server has to support HMAC-MD5, it has to store the password in plaintext. (The server can encrypt the password, but since it also has to know the encryption key, that doesn't help.)
CRAM-MD5 was designed to avoid having the password transit in cleartext. It gives some amount of protection against a passive attacker. If the attacker can eavesdrop on the communication but not inject messages, then all she gets is a challenge C and the value HMAC-MD5(P, C) where P is the password. There is no better method to find the password from this information by brute force. Brute force is however a concern with passwords: at least a slow function should be used, and exposing the hash should be avoided anyway and should be treated as a compromise.
Instead of a human-chosen, memorable password, the “password” (more precisely a shared secret) stored on the server could be a slow hash of the human password, as noted by Adnan. This would preclude brute-force attacks from the MD5 hash, so it would provide better security against passive attackers. (However, anybody who does this is using non-standard tools, and is presumably serious enough about security to use SSL.)
CRAM-MD5 only authenticates the authentication step and not the rest of the session. So an active attacker who is able to impersonate the client can let the authentication happen, then cut off the client (or send it modified data) and send his own commands in the session. Specifically, the attacker has to be able to receive TCP packets intended for the client. Alternatively, the attacker has to be able to receive TCP packets intended for the server, or send fake DNS responses that cause the client to talk to the attacker instead of the server; such an attacker acts as a relay between the client and the server during the authentication phase, then can keep talking to the server. None of these attacks involve a compromise of the password, by the way, other than through brute force as above.
Using CRAM-MD5 over SSL would solve this authentication problem. SSL provides a secure session (i.e. the participants can't change during the session), and authenticates the server to the client (assuming the user won't blindly accept an invalid certificate). CRAM-MD5 brings the third part of the authentication problem: authenticating the client with the server.
CRAM-MD5 over SSL is fine if the password is a long enough randomly-generated string — long enough to resist brute force. If the password is a randomly-generated one, there is no reason not to store it in plaintext on the server, it's just a key. CRAM-MD5 is bad if the password is one that a human can remember, because most human passwords can be cracked by brute force and are valuable beyond that one server as they are often reused.
If using SSL, there is little value in using CRAM-MD5 as opposed to having the client send the password over the SSL connection. There is a small advantage in that if the client inadvertently sends the password to a server impersonator, the password itself is not compromised immediately (only if cracked — though note that a server impersonator can send a constant challenge and therefore build a table (rainbow or other) of HMAC-MD5 values over plausible passwords). Where the password is a random string, this can be an advantage (the attacker can't use the value sent by client to impersonate it unless the server happens to send the same challenge as the attacker, which shouldn't happen). Where the password is a human one, there is no such advantage (as the password can be cracked anyway) and having to store the password in plaintext is a definite disadvantage.