Timeline for Connection's MAC address randomly changes on Windows 10, then stops
Current License: CC BY-SA 4.0
23 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 12, 2022 at 16:06 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Sep 12, 2021 at 3:08 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| May 13, 2021 at 9:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Dec 28, 2020 at 14:03 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Aug 26, 2020 at 0:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Apr 24, 2020 at 15:00 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Dec 25, 2019 at 9:03 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Aug 26, 2019 at 7:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Apr 14, 2019 at 5:01 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Dec 12, 2018 at 2:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| May 20, 2018 at 17:30 | answer | added | André Melancia | timeline score: -1 | |
| May 18, 2018 at 8:00 | comment | added | GSerg |
@MarcinGminski getmac /s on the server returns only the correct MAC of the client. arp -a also shows only the correct MACs. Executing getmac /s from inside the connection via cmdshell only shows the correct MAC. Executing arp -a from inside the connection via cmdshell only shows the correct MAC. At no point the values from net_address seem to appear anywhere on the system utilities output. sys.dm_exec_connections.client_net_address seems to be correct at all times.
|
|
| May 17, 2018 at 23:18 | comment | added | Aaron Bertrand |
Well sysprocesses is deprecated so I can't imagine it would ever have a valid value while dm_exec_connections has something invalid. I suspect the opposite is far, far, far more likely to be true, and that becomes likelier with every new release.
|
|
| May 17, 2018 at 22:34 | comment | added | GSerg |
@AaronBertrand No, I was talking about just the SQL Server portion of it that you might know about in advance. E.g. that client_net_address is always null under condition X, or that it may be invalid when the corresponding master.dbo.sysprocesses.net_address is valid.
|
|
| May 17, 2018 at 21:54 | comment | added | Aaron Bertrand | Can I guarantee that some future version or feature in Windows will not randomize how a client's IP address appears to a server? Or whether this can be used if you change client libraries / drivers or change the set of network protocols enabled for the instance? Or if all clients won't appear the same to the server if you change your network firewall or introduce a proxy or load balancer in the middle? Of course not. | |
| May 17, 2018 at 20:47 | comment | added | Marcin Gminski | @GSerg I was more thinking to check that the Windows Server that hosts SQL Server registers correct MACs of incoming connections from Win10 clients. To do this you can either do ARP -a or getmac /s, nothing to do with ipconfig. This would be my first attempt to establish where the root cause could be. Also worth checking what MACs are being registered in DHCP and Gateways to rule out Windows 10 randomising it somehow. | |
| May 17, 2018 at 20:26 | comment | added | GSerg |
@AaronBertrand Yes, that is what we are looking in at the moment. The code was originally written for SQL Server 2000 where there was no sys.dm_exec_connections. Can we expect it to be as stable as MAC address was for 15 years? Any known gotchas?
|
|
| May 17, 2018 at 20:24 | comment | added | GSerg |
@MarcinGminski ipconfig shows a MAC address for the network adapter. This address does not appear to be changing. In the "Advanced" properties of the adapter, the "Network address" is "Not present". If I switch it to a fixed value and provide a static MAC address (e.g. the one that ipconfig shows), that does not do anything. A powershell or trigger solution would be no good because the very point is that at any moment any stored procedure must be able to look up some additional data for the connection based on its network address (which in this case is a device id). It worked for 15 years.
|
|
| May 17, 2018 at 19:50 | comment | added | Aaron Bertrand | If you're able to force TCP/IP why not just use client_net_address from sys.dm_exec_connections instead of MAC address? | |
| May 17, 2018 at 19:19 | comment | added | Marcin Gminski | Can you see correct client’s MAC address at the OS level? i.e via getmac command? Thinking out loud (more of a temporary hack than a proper solution) You could utilise powershell to run “getmac /s IP” via agent job for each connected IP every minute or so or even via xp_cmdshell in a logon trigger. Interesting problem to solve. | |
| May 17, 2018 at 15:53 | history | edited | GSerg | CC BY-SA 4.0 |
added 306 characters in body
|
| May 17, 2018 at 12:22 | history | edited | GSerg | CC BY-SA 4.0 |
added 1 character in body
|
| May 17, 2018 at 12:07 | history | asked | GSerg | CC BY-SA 4.0 |