Well, the SQL Server data type is one of those "odd" CLR data types that SQL Server implements via .NET. I suspect that since it's non-standard in terms of ANSI SQL, Tableau doesn't implement it.
You just need to parse out the lat and lon yourself. For example, given this SQL Table:
We won't see the geography DT:
Parse the stuff yourself with Custom SQL, and you're happy:
...might be smarter to do this work in SQL Server itself with a View, but I'm being lazy. Sometimes Custom SQL isn't great for performance.