When using the OpenGL renderer, transparency only works if the surface is illuminated by a light source. This is a requirement on both sides of the linedef, i.e. a window you can see through needs two light sources.
3D water surfaces are also intransparent, but when they are illuminated, instead of turning transparent they disappear completely in illuminated sectors.
When using the Direct3D renderer, the surfaces are always transparent.