SQL Server 2005 allows stored procedures and functions to be exposed as web services direct from the database without any need to be running IIS. To do this, SQL must be installed on Windows Server 2003 to take advantage of the kernel mode http.sys. (Hmmmmm - I see Windows XP Sp2 also has kernel mode http.sys- something to try later on).

They are very easy to setup - which then leads to the obvious next question which many ISV have asked me "When would you use them over ASP.NET web services". The answer is a "it depends" answer but as a rule of thumb...

SQL Server 2005 web services are designed primarily for internal consumption - specifically to enable cross-platform data access. If you have public web services, you would typically want to host them on a dedicated app server that can handle the demand without reducing the performance of the underlying database. Further, whilst some of WSE 2.0 is making its way into SQL web services - some won't. So if these features appeal - go ASP.NET. 
I know several ISVs have found some push back from IT departments when they require IIS is installed as part of their deployement - so this new feature may be especially helpful in those cases if all you required IIS for was web services.
There is a nice Q&A which touches on this at http://msdn.microsoft.com/SQL/2005/2005Webcasts/WebServicesQandA.aspx and I recommend the webcast at http://msdn.microsoft.com/SQL/2005/2005Webcasts/