Connection's MAC address randomly changes on Windows 10, then stops The 2019 Stack Overflow...
does high air pressure throw off wheel balance?
how can a perfect fourth interval be considered either consonant or dissonant?
Keeping a retro style to sci-fi spaceships?
Hiding Certain Lines on Table
Is it ethical to upload a automatically generated paper to a non peer-reviewed site as part of a larger research?
What do you call a plan that's an alternative plan in case your initial plan fails?
What information about me do stores get via my credit card?
Can a 1st-level character have an ability score above 18?
How many people can fit inside Mordenkainen's Magnificent Mansion?
When did F become S in typeography, and why?
Python - Fishing Simulator
How to stretch delimiters to envolve matrices inside of a kbordermatrix?
How does ice melt when immersed in water?
Format single node in tikzcd
Slither Like a Snake
Match Roman Numerals
How did the audience guess the pentatonic scale in Bobby McFerrin's presentation?
What force causes entropy to increase?
Four Colour Theorem
Typeface like Times New Roman but with "tied" percent sign
Wall plug outlet change
Make it rain characters
Why did all the guest students take carriages to the Yule Ball?
Can the prologue be the backstory of your main character?
Connection's MAC address randomly changes on Windows 10, then stops
The 2019 Stack Overflow Developer Survey Results Are In
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)SQL Server Configuration Manager 2012 - unable to set IP All = NoUnable to connect to SQL server 2008 R2 via computer nameHow to remote access Oracle 12c using SQL Developer?SQL Server 2016 Always On ReadOnly Routing ListProblem about listing network instances on SSMSCannot connect to SQL server through SSMS from other machine after IP changeCan `listen_addresses` system configuration setting in Postgres stop pre-authentication exploits?Disabling IPV6 on Always on availability groupCan't connect to MS SQL Server via IP address anymoreHow do I do an ODBC connection to ORACLE from SSIS/ Visual studio?
.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♦ 8 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♦ 8 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♦ 8 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♦ 8 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