I have a similar issue. However, I cannot seem to resolve it. Here the scenario:
- AT&T Gigabit Internet served up through Wi-fi and ethernet in a new house
- We have non-managed Netgear switches (Gigabit) with one at the modem; another in the living room (with security system, Apple TV 4K, Samsung TV); another switch upstairs connected to two Macs (iMac and MacBook) along with an Xbox Series X
- Everything works with wired ethernet except for the 2 Macs (details below)
iMac 27 - 3.4 Ghz Quad-Core Intel Core i5
MacOS Venture 13.6.1
MacBook Air - Apple M2
MacOS Sonoma Version 14.1.1
Both computers connect to WiFi with no issues. However, when I turn off Wi-Fi I get the exact same result: no internet access and the following indication when opening "Network Settings":

I cannot seem to make any changes that affect this. It remains yellow. It is exactly the same on both computers.
This wired LAN is the first priority followed by wi-fi, etc...
The Firewall is on, for the MacBook Air it is not possible to turn off by me (it's a work computer) and both actually worked fine at our last house with ethernet.
I'm at a complete loss as to what the issue is. I'd like to blame the OS, but I have two different systems, but the results are the same. I don't know how to fix the "self-assigned" ID issue.