2

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 net_address from 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 net_address.

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 Library=DBMSSOCN or Network=DBMSSOCN to 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)?

Improve this question
9
  • 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.
    – Marcin Gminski
    Commented May 17, 2018 at 19:19
  • If you're able to force TCP/IP why not just use client_net_address from sys.dm_exec_connections instead of MAC address?
    – Aaron Bertrand
    Commented May 17, 2018 at 19:50
  • @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.
    – GSerg
    Commented May 17, 2018 at 20:24
  • @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?
    – GSerg
    Commented May 17, 2018 at 20:26
  • 1
    @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.
    – Marcin Gminski
    Commented May 17, 2018 at 20:47

1 Answer 1

Reset to default
-1

One of the things you have if you use IPv6 is something called Privacy Extensions. This changes the IP address every hour (or whatever implementation specific time). This guarantees anonymity of the MAC.

Improve this answer
1
  • Like said above, we have tried removing IPv6 support from the adapter. It does not have any effect. The MAC address changes with every request, not every hour. The IP address stays the same.
    – GSerg
    Commented May 20, 2018 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.