Some algorithms have design limitations.
k-means and Ward are both designed for squared Euclidean distance (= sum of variances).
Others require triangle inequality for correctness.
Again others (single-link) do not even need your distance to be non-negative... they can work with similarities or dissimilarities on any scale, as long as they know whether to prefer low or prefer high values.
However, don't just choose the distance by the algorithm. Instead, choose the algorithm by the distance, and the distance must match your task.
On any numerical data set you can compute squared Euclidean, and run k-means. But the results may be completely useless. So first, study your data set, in particular how to quantify similarity. Without a working similarity measure, any clustering algorithm will only work by chance.