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;
}
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=DBMSSOCNorNetwork=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)?
sql-server-2016 connections network windows-10
asked May 17 '18 at 12:07
GSergGSerg
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.
|
show 4 more comments
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=DBMSSOCNorNetwork=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)?
sql-server-2016 connections network windows-10
asked May 17 '18 at 12:07
GSergGSerg
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.
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
@MarcinGminskiipconfigshows 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 nosys.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
|
show 4 more comments
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=DBMSSOCNorNetwork=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)?
sql-server-2016 connections network windows-10
asked May 17 '18 at 12:07
GSergGSerg
9651023
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=DBMSSOCNorNetwork=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)?
sql-server-2016 connections network windows-10
sql-server-2016 connections network windows-10
asked May 17 '18 at 12:07
GSergGSerg
9651023
asked May 17 '18 at 12:07
GSergGSerg
9651023
edited May 17 '18 at 15:53
GSerg
asked May 17 '18 at 12:07
GSergGSerg
9651023
asked May 17 '18 at 12:07
GSergGSerg
9651023
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
@MarcinGminskiipconfigshows 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 nosys.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
|
show 4 more comments
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
@MarcinGminskiipconfigshows 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 nosys.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
|
show 4 more comments
1 Answer
1
active
oldest
votes
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.
answered May 20 '18 at 17:30
André MelanciaAndré Melancia
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
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
answered May 20 '18 at 17:30
André MelanciaAndré Melancia
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
add a comment |
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.
answered May 20 '18 at 17:30
André MelanciaAndré Melancia
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
add a comment |
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.
answered May 20 '18 at 17:30
André MelanciaAndré Melancia
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.
answered May 20 '18 at 17:30
André MelanciaAndré Melancia
1
answered May 20 '18 at 17:30
André MelanciaAndré Melancia
1
answered May 20 '18 at 17:30
André MelanciaAndré Melancia
1
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
add a comment |
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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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
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.– 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