TLDR; Yes - you have to add the my.trafficmanager.net to the site definitions of your website (considering that in your case you have added the actual bindings to the website site removing the catch-all rule for Sitecore. If that is not the case and you have the website site untouched - there might be a deeper problem with the actual resolution happening on the sites)

The reason for this actually lies inside the entire mechanism that Azure Traffic Manager works. And especially the part

The client connects to the application service endpoint directly, not through Traffic Manager. 

which is happening after the DNS resolution happened.

In real case scenario you are probably going to have your real domain name (i.e. www.example.com) pointed towards the ATM and it will be responsible for handling the actual traffic that is coming for your website. As ATM is not acting as a reverse proxy it will just redirect the request to the responsible node keeping the hostname that was requested.

In order for Sitecore to be able to resolve the website - it is going to use hostname that the node was requested with - which in your case is going to be my.traffimanager.net. Meaning that you have to bind it in your site definition.

TLDR; Yes - you have to add the my.trafficmanager.net to the site definitions of your website (considering that in your case you have added the actual bindings to the website site removing the catch-all rule for Sitecore. If that is not the case and you have the website site untouched - there might be a deeper problem with the actual resolution happening on the sites)

The reason for this actually lies inside the entire mechanism that Azure Traffic Manager works. And especially the part

The client connects to the application service endpoint directly, not through Traffic Manager. 

which is happening after the DNS resolution happened.

In real case scenario you are probably going to have your real domain name (i.e. www.example.com) pointed towards the ATM and it will be responsible for handling the actual traffic that is coming for your website. As ATM is not acting as a reverse proxy it will just redirect the request to the responsible node keeping the hostname that was requested.

In order for Sitecore to be able to resolve the website - it is going to use hostname that the node was requested with - which in your case is going to be my.traffimanager.net. Meaning that you have to bind it in your site definition.

So basically each website is going to get resolved by the hostname coming from the Azure Traffic Manager.

In case you have multi-website solution and you want to handle the traffic routing for multiple websites - you should use the Azure Traffic Manager profiles and make sure each site definition has the correct hostName correctly bound to match the one coming from ATM.

TLDR; Yes - you have to add the my.trafficmanager.net to the site definitions of your website (considering that in your case you have added the actual bindings to the website site removing the catch-all rule for Sitecore. If that is not the case and you have the website site untouched - there might be a deeper problem with the actual resolution happening on the sites)

The reason for this actually lies inside the entire mechanism that Azure Traffic Manager works. And especially the part

The client connects to the application service endpoint directly, not through Traffic Manager. 

which is happening after the DNS resolution happened.

In real case scenario you are probably going to have your real domain name (i.e. www.example.com) pointed towards the ATM and it will be responsible for handling the actual traffic that is coming for your website.

  As ATM is not acting as a reverse proxy it will just redirect the request to the responsible node keeping the hostname that was requested.

In order for Sitecore to be able to resolve the website - it is going to use hostname that the node was requested with - which in your case is going to be my.traffimanager.net. Meaning that you have to bind it in your site definition.

TLDR; Yes - you have to add the my.trafficmanager.net to the site definitions of your website (considering that in your case you have added the actual bindings to the website site removing the catch-all rule for Sitecore. If that is not the case and you have the website site untouched - there might be a deeper problem with the actual resolution happening on the sites)

The reason for this actually lies inside the entire mechanism that Azure Traffic Manager works. And especially the part

The client connects to the application service endpoint directly, not through Traffic Manager. 

which is happening after the DNS resolution happened.

In real case scenario you are probably going to have your real domain name (i.e. www.example.com) pointed towards the ATM and it will be responsible for handling the actual traffic that is coming for your website. As ATM is not acting as a reverse proxy it will just redirect the request to the responsible node keeping the hostname that was requested.

In order for Sitecore to be able to resolve the website - it is going to use hostname that the node was requested with - which in your case is going to be my.traffimanager.net. Meaning that you have to bind it in your site definition.