Ok. Well Hibernate's query language is HQL not SQL (have a read of the documentation). If you want to run proprietory SQL you will need to run it ass a sql query (check the JavaDocs for the Session class).
One thing to think about though: one of the reasons you use a tool like Hibernate is to avoid tighly coupling your data access code to a specific database, which is what you do when you introduce SQL Server specific syntax and keywords to your query. There is nothing in your query that you can't do in HQL, or using Hibernate's Criteria API.
subject: Hibernate 3 not able to recognize the "ON" word in sql