Connection's MAC address randomly changes on Windows 10, then stops





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







1















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)?










share|improve this question
















bumped to the homepage by Community 9 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 at 20:47


















1















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)?










share|improve this question
















bumped to the homepage by Community 9 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 at 20:47














1












1








1








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)?










share|improve this question
















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)?







sql-server-2016 connections network windows-10






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited May 17 '18 at 15:53







GSerg

















asked May 17 '18 at 12:07









GSergGSerg

9651023




9651023





bumped to the homepage by Community 9 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 9 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 at 20:47



















  • 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 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
    May 17 '18 at 20:47

















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
May 17 '18 at 19:19





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
May 17 '18 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
May 17 '18 at 19:50





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
May 17 '18 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
May 17 '18 at 20:24





@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
May 17 '18 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
May 17 '18 at 20:26





@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
May 17 '18 at 20:26




1




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
May 17 '18 at 20:47





@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
May 17 '18 at 20:47










1 Answer
1






active

oldest

votes


















0














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.






share|improve this answer
























  • 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
    May 20 '18 at 17:41












Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f207042%2fconnections-mac-address-randomly-changes-on-windows-10-then-stops%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














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.






share|improve this answer
























  • 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
    May 20 '18 at 17:41
















0














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.






share|improve this answer
























  • 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
    May 20 '18 at 17:41














0












0








0







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.






share|improve this answer













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.







share|improve this answer












share|improve this answer



share|improve this answer










answered May 20 '18 at 17:30









André MelanciaAndré Melancia

1




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
    May 20 '18 at 17:41



















  • 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
    May 20 '18 at 17:41

















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
May 20 '18 at 17:41





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
May 20 '18 at 17:41


















draft saved

draft discarded




















































Thanks for contributing an answer to Database Administrators Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f207042%2fconnections-mac-address-randomly-changes-on-windows-10-then-stops%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Liste der Baudenkmale in Friedland (Mecklenburg)

Single-Malt-Whisky

Czorneboh