UPDATE: Azure can now talk to Git on TFService, making this post obsolete.

At the time of writing (August, 2013) you can connect a Windows Azure web site to the Team Foundation Service (TFService) for automated deployments, but only if your source is under Team Foundation Version Control (TFVC).
If you’re using a git repository then you're currently out in the cold. The Azure team hasn't yet added support for it, though I'm sure it'll show up at some point. In the meantime, you can always vote for it on the Azure User Voice site.
All is not lost however as you can still automatically deploy the web site by using Web Deploy as part of the build process.
For this example, we'll deploy a very simple site built on top of Nancy with NuGet package restore enabled.
1. Firstly, create a site in Azure and then download the publishing profile.
image
2. Import the PublishSettings file into your web project.
image
3. Validate the connection and click close.  (You can check your deployment by doing a manual deployment if you wish)
4. Manually open the PublishSettings file you downloaded and make a note of the userName and userPWD values. You'll use those in a minute.
image
4. In Team Explorer switch to the Builds hub and click the New Build Definition link.
5. Give your build a name, select a Trigger and confirm that the Source Settings point to the appropriate git branch.
6. In the Process settings, use the Default Template and in the Advanced | MSBuild arguments property add the following:
/p:DeployOnBuild=true /p:PublishProfile=<your profile> /p:AllowUntrustedCertificate=true /p:UserName=<your user name> /p:Password=<your pwd> /p:VisualStudioVersion=11.0
In my case this would be:
/p:DeployOnBuild=true /p:PublishProfile="ADemoSite - Web Deploy" /p:AllowUntrustedCertificate=true /p:UserName=$ADemoSite /p:Password=83LukXHlmqEg75xxxxxx  /p:VisualStudioVersion=11.0
Note that since the profile name contains spaces, I’ve wrapped it in quotes.
image
7. Save the build definition and queue a new build.
8. Once the build completes you should be able to navigate to your site and see your newly deployed site.
image
And we’re done!

Notes

This is not the most secure of approaches for deploying a site. The password is stored in plain text in the build definition which means it’s shared amongst all the developers on the team. If you use this method, be aware of the risk.
If you forget the /p:VisualStudioVersion=11.0 argument the build will succeed but your site won't be deployed. Leaving the flag off causes the VS2010 deployment tool to be used, not the VS2012 one so instead of deploying the site to Azure the build instead creates a deployment package that you would then have to run manually.