You need to move the fancybox script reference to the botttom of the body tag. You can do this in a master page or you can add it using the script/style widget and set it to end of the body tag.
The reason it is not working is that Sitefinity will add jquery to the site. It is added toward the top of the body tag and your reference to fancybox is above this. So the jquery instance that is provided by Sitefinity is basically causing the fancybox initialization to be erased. There are a few posts on the forum which talk about this. I have just started to put my references to any plugin at the bottom of the body tag.
Speaking of unusable and totally useless - does anyone know if Sitefinity allows you to modify the rendering of the Fancybox modal dialog resulting when you click image gallery thumbnails? Out of the box, the markup seems untested because the image description field blows out to 100% width of the entire screen, even if the image is 800px centered in the modal layer.
Or is this markup decompiled into 0s and 1s within the Sitefinity project? Sorry, not a compiled language developer here, I'm a designer for the web and it drives me batty that this one small piece of the presentation layer is inaccessible except via some compiled language IDE.