That's because internally A**2 is stored as \pow{A}{2} and is thus alphabetically 'larger' than D.
You can get around it (sort of, it's not ideal) by doing
{\pow{A}{n?}, B, C, D}::SortOrder;
ex:= C B A**2 D;
sort_product(_);
which gives
A**2 B C D
The pattern \pow{A}{n?} matches any power of A.