After I reset all the settings and re-created the environment, the problem went away. So the problem was perhaps very specific to local situation and not general to a linked server to AAG. I appreciate all those who commented.
What I don't understand is how the fail over is going to work
This is documented in a high level in Microsoft Docs.
when I connect to the listener everything is fine when Node1 is primary, when I do manual failover to Node2, I can't connect.
That depends on your connection string, DNS infrastructure, and availability group setup.
If it's a single subnet, there shouldn't be an issue. If it's multi-subnet, you'll want to instruct the client driver to resolve all ips in parallel using the MultiSubnetFailover=true keyword in older version of .net/java/php/etc. Make sure you're using an updated client driver version that supports said keyword.
There are additional windows clustering private property settings for net names that can influence how these things work, but it doesn't sound like you've made any manual adjustments to these (defaults used) and I won't muddy the waters with it but did want you to be aware that there are additional settings.
How will the listener switch to node2?
This happens at the WSFC level. A failure is detected or SQL Server initiates a failover which moves the resource group (and all contained resources) logically to the new node (where ever and which ever that may be). The resources are then attempted to come online. Part of the process to come on-line for a network name (listener) is to have at least one dependent IP address online and register with DNS among other items. This may or may not be the same IP Address based on how your networking is setup and the location of the new node owning the resources.
Should the listener have 2 IPs for both nodes?
The listener needs at a minimum an IP Address for each subnet it could possibly be online in. It may have 1, 2, 3, ... 7, etc., depending on your specific setup and infrastructure.
Best Answer
Turns out that yes the cname should be pointed at the listener IP.