You don't need a special setup or a special camera to take good pictures. I have used everything from a Nikon D90 DSLR with a photo box and photo tank to just taking random shots of my tanks with a cheap nikon coolpix point and shoot.
You'll get a lot more detail with a DSLR or a camera with a macro lens, but it's not a necessity.
A few things to do to help:
Good lighting, put some good light over the tank, even if it takes a clip on light. Use a daylight (6500K) or higher bulb (up to 10000k or so), lower Kelvin ratings usually mean yellower/warmer colors which don't look as good (IMO
) in pictures.
Cheaper point and shoot cameras tend to have a hard time with the flash, I think it may be the position of the flash on the camera, but it often ends up with a glare off the glass or the subject itself. Keeping a good strong light source over tank will let you take pictures without a flash.
The other thing to do is pick a subject that is still. Practice shooting pictures of plants or stationary objects. Figure out how far away you have to get, or how much it'll let you zoom in until the autofocus doesn't work anymore. That's your sweet spot, getting it any closer is difficult or even impossible on cheap P&S cameras.
Camera settings: I'm not a camera pro, or even really into photography in general, so I don't know a lot about the various settings apart from just applying whatever macro features the camera may have.
Shoot from an angle: Try to shoot pictures with a slight angle to the tank, rather than straight on, this will help reduce the glass reflection.
Here's some photos I took recently with a newly acquired, low end nikon coolpix P&S.
And here are some that my girlfriend took with her Nikon D90 DSLR.