There is a great article here by Alistair Aitchison detailing how to get the SQL Server spatial types working with Azure roles. Even with the aid of that article it took a little bit of trial and error to get it right.
The main issue I found was that when adding SQLServerSpatial.dll from C:\Windows\System32 the Add Exisiting Item dialogue was picking up the SQLServerSpatial.dll file from the SysWOW64 folder.
You can tell the difference by the file size, the SysWOW64 file is around 230k whilst the System32 file is over 400k. Make sure you add the larger file. I eventually had to copy the file to another location and add from there to get the correct file added.
I also found that I didn't need to add the msvcp and msvcr dlls - this is probably because the standard VM running an Azure Cloud Service now comes with these available.
Hope this saves someone the few hours it took me to get this working!