The LimitHandler implementation for SQL Server does not support native SQL queries that are based on CTE syntax. Internally, the LimitHandler implementation takes your native SQL query and converts it to a CTE query anyway and therefore, you can just as easily express your query as follows to get it to work.

SELECT * FROM T WHERE C = :my_param

I'll leave this open as we may be able to better address support for native query CTEs with 6.0.