Because reasons (tm), we want to identify the client's MAC address on SQL Server side (local network, not about security, enables a very convenient feature in way transparent for applications).
We do that by querying
master.dbo.sysprocesses. All clients are set up to use TCP/IP connections, the address value has always been correct, and all connections from the same computer have always had the same
In Windows 10 clients, however, every single connection gets its own
net_address, and they do not repeat. The only pattern we can see about these addresses is that the last two digits remain the same within approximately 6 seconds. So if another connection gets assigned with
EBC1E384F4C2, all subsequent connections' addresses will also end with
C2 until the 6 seconds elapse, then the suffix changes for another 6 seconds.
We have tried in various combinations:
- running cliconfg.exe and making sure only TCP/IP is enabled, and that there is an alias for the server name that is also set to use TCP/IP
- disabling and enabling back connection pooling in the ODBC Data Sources
- adding and removing
Network=DBMSSOCNto and from the connection properties, depending on the connection type
- setting and removing a fixed MAC address in the network adapter properties
- removing and adding back TCP/IP v6 support from and to the network connection properties
- reading about MAC randomization in Windows 10 which claims it only applies to Wi-Fi connections, and we only have wired connections
Nothing seemed to have any effect.
The only thing that did seem to have effect is:
- You toggle the connection pooling status in ODBC Data Sources (does not matter if it was on or off, what matters is that you flip the status to the opposite), but only provided that
- The system has been running for at least about 12 minutes after startup. If you flip the pooling status earlier than that, it will seemingly not have any effect.
The moment you do that, the addresses stop changing, but they do not revert to the actual MAC address of the network adapter, instead they freeze at whatever values they had when the freeze happened, and from then on each application will receive the same MAC for all its subsequent connections, but that MAC will be different among different application on the same device. If you close and run an application again, it will receive its frozen MAC address again as if you never closed it.
What is going on? How do we stop it from happening so that TCP/IP connections properly use the network adapter's MAC as opposed to a random address?
Alternatively, is there a better way to reliably identify and distinguish client devices (not users) on a local network from SQL Server (e.g.
hostname is no good because it depends on the connection string)?
ipconfigshows 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.
sys.dm_exec_connections. Can we expect it to be as stable as MAC address was for 15 years? Any known gotchas?