We have been encourage to migrate from Oracle to SQL server for multiple reasons. In doing a gap analysis, most of what we do seems pretty transportable.
The one thing we seem to be missing is sde.st_transform. We keep our data natively in State Plane, but create spatial views to show the data in different coordinate systems, or to add Lat/Long coordinates, or to union datasets in different systems. This is an absolute necessity to be able to do live.
Is there any solution at all ?
Unfortunately, Microsoft does not allow transformations through their ST functions like we do for Oracle and PostgreSQL DBMS's.
The only workaround that is going to work here is to use 2 spatial columns in the table in SQL Server. While we do not support multiple spatial columns when working with tables or views in ArcGIS, Microsoft does. So you could add a second spatial column in the WKID that you need and then create a view or query layer using only one of those columns for use in ArcGIS. Outside of this though, I can't think of another way around it.