SQL Server spatial functions implementation is in its current state not able to reproject or transform between different coordinate reference systems. The spatial data types are nothing more than a CLR (Coomon Language Runtime) data type implementation of the OGC Simple Feature Access specification plus some extended functions plus the spatial index. You can find its documentation for the geometry data type here for example.
The only way to bring reprojection capability to a SQL Server database is to write your own SQL Server user defined functions (the CLR way, which is another word for .NET). So basically every .NET library which is able to reproject can be integrated into SQL server by writing some glue code between the library and the SQL Server function call.
You can do this with ArcObjects .NET Edition or with dotSpatial for example. We have done this with dotSpatial in a Project, unfortunately our implementation is only able to reproject between two specific austrian CRS as this was the only Thing we needed to reach our Project Goal.
Hope this helps.
