You haven't gotten a knowledgeable answer on specific rose varieties. So here's some info on what to look for in various catalog descriptions. For tea and similar uses, you want to look for varieties described as having large hips. For rose water or syrup, you want to look for varieties that are listed as intensely fragrant. They probably will not be the same varieties. But there should be climbers that fit both categories.

Best I can do....

Rosa rugosa (Beach Rose) has large good tasting hips, and is very fragrant. However, it's not a climber, and the bushes only get to about between 6 and 8 feet tall.

There are some climber hybrids of R. Rugosa, such as Conrad F. Meyer and Nova Zembla, which have strong scents, but I don't know how the fruit is.