SQL Server logins “un-sync” when Availability Group fails over
I have 2 SQL Servers in an Availability Group. The logins are manually synced – same SID, same Password Hash, etc. on both replicas – and the user can log in via the listener to the primary replica. (Note: password policy & expiration policy are on.)
Later, the AG fails over to the other replica, and the user now gets a login failure. The new Primary’s sys.sql_logins table shows a different Password Hash and a different Modify_Date: the date / time of the login failure! When the original Password Hash is re-migrated to the new Primary, the user can again log in successfully.
Can anyone tell me why the login’s password hash “spontaneously” changes after failover at login time, and how to avoid this happening so the users can log in after a failover?
My hunch is that it has something to do with the password/expiration policy, but that's a requirement of our auditors so can't be turned off.
sql-server availability-groups
edited Mar 16 '16 at 17:27
LowlyDBA
7,26752643
asked Mar 16 '16 at 17:22
RichRich
261
bumped to the homepage by Community♦ 3 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 1 more comment
I have 2 SQL Servers in an Availability Group. The logins are manually synced – same SID, same Password Hash, etc. on both replicas – and the user can log in via the listener to the primary replica. (Note: password policy & expiration policy are on.)
Later, the AG fails over to the other replica, and the user now gets a login failure. The new Primary’s sys.sql_logins table shows a different Password Hash and a different Modify_Date: the date / time of the login failure! When the original Password Hash is re-migrated to the new Primary, the user can again log in successfully.
Can anyone tell me why the login’s password hash “spontaneously” changes after failover at login time, and how to avoid this happening so the users can log in after a failover?
My hunch is that it has something to do with the password/expiration policy, but that's a requirement of our auditors so can't be turned off.
sql-server availability-groups
edited Mar 16 '16 at 17:27
LowlyDBA
7,26752643
asked Mar 16 '16 at 17:22
RichRich
261
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
When the password is changed, the hash is changed as well.
– Kin
Mar 16 '16 at 17:27
I haven't tried this, but you could use a DDL trigger perhaps to change the password on both servers. And also look into whatever process is syncing them, sounds like something is not right there.
– Aaron Bertrand♦
Mar 16 '16 at 17:31
2
The hash is always going to be different on different servers, even with precisely the same plain-text password (unless the login was created using the hashed version of the password). Use thePWDCOMPAREfunction to compare passwords.
– Max Vernon
Mar 16 '16 at 18:11
1
When the instance fails over, and these logins no longer work, check theLOGINPROPERTY(<loginname>, 'IsExpired')to see if the login has expired.
– Max Vernon
Mar 16 '16 at 18:17
Which version of SQL Server is this?
– Max Vernon
Mar 16 '16 at 18:18
|
show 1 more comment
I have 2 SQL Servers in an Availability Group. The logins are manually synced – same SID, same Password Hash, etc. on both replicas – and the user can log in via the listener to the primary replica. (Note: password policy & expiration policy are on.)
Later, the AG fails over to the other replica, and the user now gets a login failure. The new Primary’s sys.sql_logins table shows a different Password Hash and a different Modify_Date: the date / time of the login failure! When the original Password Hash is re-migrated to the new Primary, the user can again log in successfully.
Can anyone tell me why the login’s password hash “spontaneously” changes after failover at login time, and how to avoid this happening so the users can log in after a failover?
My hunch is that it has something to do with the password/expiration policy, but that's a requirement of our auditors so can't be turned off.
sql-server availability-groups
edited Mar 16 '16 at 17:27
LowlyDBA
7,26752643
asked Mar 16 '16 at 17:22
RichRich
261
I have 2 SQL Servers in an Availability Group. The logins are manually synced – same SID, same Password Hash, etc. on both replicas – and the user can log in via the listener to the primary replica. (Note: password policy & expiration policy are on.)
Later, the AG fails over to the other replica, and the user now gets a login failure. The new Primary’s sys.sql_logins table shows a different Password Hash and a different Modify_Date: the date / time of the login failure! When the original Password Hash is re-migrated to the new Primary, the user can again log in successfully.
Can anyone tell me why the login’s password hash “spontaneously” changes after failover at login time, and how to avoid this happening so the users can log in after a failover?
My hunch is that it has something to do with the password/expiration policy, but that's a requirement of our auditors so can't be turned off.
sql-server availability-groups
sql-server availability-groups
edited Mar 16 '16 at 17:27
LowlyDBA
7,26752643
asked Mar 16 '16 at 17:22
RichRich
261
edited Mar 16 '16 at 17:27
LowlyDBA
7,26752643
asked Mar 16 '16 at 17:22
RichRich
261
edited Mar 16 '16 at 17:27
LowlyDBA
7,26752643
edited Mar 16 '16 at 17:27
LowlyDBA
7,26752643
edited Mar 16 '16 at 17:27
LowlyDBA
7,26752643
7,26752643
asked Mar 16 '16 at 17:22
RichRich
261
asked Mar 16 '16 at 17:22
RichRich
261
asked Mar 16 '16 at 17:22
RichRich
261
261
bumped to the homepage by Community♦ 3 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♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
When the password is changed, the hash is changed as well.
– Kin
Mar 16 '16 at 17:27
I haven't tried this, but you could use a DDL trigger perhaps to change the password on both servers. And also look into whatever process is syncing them, sounds like something is not right there.
– Aaron Bertrand♦
Mar 16 '16 at 17:31
2
The hash is always going to be different on different servers, even with precisely the same plain-text password (unless the login was created using the hashed version of the password). Use thePWDCOMPAREfunction to compare passwords.
– Max Vernon
Mar 16 '16 at 18:11
1
When the instance fails over, and these logins no longer work, check theLOGINPROPERTY(<loginname>, 'IsExpired')to see if the login has expired.
– Max Vernon
Mar 16 '16 at 18:17
Which version of SQL Server is this?
– Max Vernon
Mar 16 '16 at 18:18
|
show 1 more comment
When the password is changed, the hash is changed as well.
– Kin
Mar 16 '16 at 17:27
I haven't tried this, but you could use a DDL trigger perhaps to change the password on both servers. And also look into whatever process is syncing them, sounds like something is not right there.
– Aaron Bertrand♦
Mar 16 '16 at 17:31
2
The hash is always going to be different on different servers, even with precisely the same plain-text password (unless the login was created using the hashed version of the password). Use thePWDCOMPAREfunction to compare passwords.
– Max Vernon
Mar 16 '16 at 18:11
1
When the instance fails over, and these logins no longer work, check theLOGINPROPERTY(<loginname>, 'IsExpired')to see if the login has expired.
– Max Vernon
Mar 16 '16 at 18:17
Which version of SQL Server is this?
– Max Vernon
Mar 16 '16 at 18:18
When the password is changed, the hash is changed as well.
– Kin
Mar 16 '16 at 17:27
When the password is changed, the hash is changed as well.
– Kin
Mar 16 '16 at 17:27
I haven't tried this, but you could use a DDL trigger perhaps to change the password on both servers. And also look into whatever process is syncing them, sounds like something is not right there.
– Aaron Bertrand♦
Mar 16 '16 at 17:31
I haven't tried this, but you could use a DDL trigger perhaps to change the password on both servers. And also look into whatever process is syncing them, sounds like something is not right there.
– Aaron Bertrand♦
Mar 16 '16 at 17:31
2
2
The hash is always going to be different on different servers, even with precisely the same plain-text password (unless the login was created using the hashed version of the password). Use the
PWDCOMPARE function to compare passwords.– Max Vernon
Mar 16 '16 at 18:11
The hash is always going to be different on different servers, even with precisely the same plain-text password (unless the login was created using the hashed version of the password). Use the
PWDCOMPARE function to compare passwords.– Max Vernon
Mar 16 '16 at 18:11
1
1
When the instance fails over, and these logins no longer work, check the
LOGINPROPERTY(<loginname>, 'IsExpired') to see if the login has expired.– Max Vernon
Mar 16 '16 at 18:17
When the instance fails over, and these logins no longer work, check the
LOGINPROPERTY(<loginname>, 'IsExpired') to see if the login has expired.– Max Vernon
Mar 16 '16 at 18:17
Which version of SQL Server is this?
– Max Vernon
Mar 16 '16 at 18:18
Which version of SQL Server is this?
– Max Vernon
Mar 16 '16 at 18:18
|
show 1 more comment
1 Answer
1
active
oldest
votes
I don't know if this is still an issue for you, or if you've found a solution, but in case you haven't, a very powerful set of tools, and one specific to your issue, can be obtained from: https://dbatools.io. Specific to your issue, use "Copy-SqlLogin" and configure it as a scheduled task on each server (primary and all secondaries) in whatever configuration that will work for your needs. I used this solution at the last place I worked and scheduled it to run every hour, and never had an issue with logins after implementation.
If, for some reason, you cannot utilize dbatools, you will need to use sp_help_revlogin, devise some method to pick out the logins you want to "replicate" (store in a table??) and then execute the IF NOT EXISTS...CREATE LOGIN... on all relevant instances, which will have to be hard-coded. So, this second method is only slightly better than manual intervention. However, assuming DBAs are the only ones who can create logins, it would be easy enough to make a procedure for all DBAs to always add a new login to this process whenever a new one is created. One thing to keep in mind, wherever the login is obtained from, the User level permissions will be carried over to all replicas, so make sure the "source" login/user is configured correctly before implementing any sync solution.
IMHO, logins and SQL Agent jobs are the only thing lacking from AlwaysOn, and perhaps in a future release, Microsoft will add some automatic functionality--especially for logins, since it really breaks the whole purpose of automatic failover if the logins aren't sync'd.
answered Aug 3 '17 at 14:31
SQL_HackerSQL_Hacker
362113
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%2f132421%2fsql-server-logins-un-sync-when-availability-group-fails-over%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
I don't know if this is still an issue for you, or if you've found a solution, but in case you haven't, a very powerful set of tools, and one specific to your issue, can be obtained from: https://dbatools.io. Specific to your issue, use "Copy-SqlLogin" and configure it as a scheduled task on each server (primary and all secondaries) in whatever configuration that will work for your needs. I used this solution at the last place I worked and scheduled it to run every hour, and never had an issue with logins after implementation.
If, for some reason, you cannot utilize dbatools, you will need to use sp_help_revlogin, devise some method to pick out the logins you want to "replicate" (store in a table??) and then execute the IF NOT EXISTS...CREATE LOGIN... on all relevant instances, which will have to be hard-coded. So, this second method is only slightly better than manual intervention. However, assuming DBAs are the only ones who can create logins, it would be easy enough to make a procedure for all DBAs to always add a new login to this process whenever a new one is created. One thing to keep in mind, wherever the login is obtained from, the User level permissions will be carried over to all replicas, so make sure the "source" login/user is configured correctly before implementing any sync solution.
IMHO, logins and SQL Agent jobs are the only thing lacking from AlwaysOn, and perhaps in a future release, Microsoft will add some automatic functionality--especially for logins, since it really breaks the whole purpose of automatic failover if the logins aren't sync'd.
answered Aug 3 '17 at 14:31
SQL_HackerSQL_Hacker
362113
add a comment |
I don't know if this is still an issue for you, or if you've found a solution, but in case you haven't, a very powerful set of tools, and one specific to your issue, can be obtained from: https://dbatools.io. Specific to your issue, use "Copy-SqlLogin" and configure it as a scheduled task on each server (primary and all secondaries) in whatever configuration that will work for your needs. I used this solution at the last place I worked and scheduled it to run every hour, and never had an issue with logins after implementation.
If, for some reason, you cannot utilize dbatools, you will need to use sp_help_revlogin, devise some method to pick out the logins you want to "replicate" (store in a table??) and then execute the IF NOT EXISTS...CREATE LOGIN... on all relevant instances, which will have to be hard-coded. So, this second method is only slightly better than manual intervention. However, assuming DBAs are the only ones who can create logins, it would be easy enough to make a procedure for all DBAs to always add a new login to this process whenever a new one is created. One thing to keep in mind, wherever the login is obtained from, the User level permissions will be carried over to all replicas, so make sure the "source" login/user is configured correctly before implementing any sync solution.
IMHO, logins and SQL Agent jobs are the only thing lacking from AlwaysOn, and perhaps in a future release, Microsoft will add some automatic functionality--especially for logins, since it really breaks the whole purpose of automatic failover if the logins aren't sync'd.
answered Aug 3 '17 at 14:31
SQL_HackerSQL_Hacker
362113
add a comment |
I don't know if this is still an issue for you, or if you've found a solution, but in case you haven't, a very powerful set of tools, and one specific to your issue, can be obtained from: https://dbatools.io. Specific to your issue, use "Copy-SqlLogin" and configure it as a scheduled task on each server (primary and all secondaries) in whatever configuration that will work for your needs. I used this solution at the last place I worked and scheduled it to run every hour, and never had an issue with logins after implementation.
If, for some reason, you cannot utilize dbatools, you will need to use sp_help_revlogin, devise some method to pick out the logins you want to "replicate" (store in a table??) and then execute the IF NOT EXISTS...CREATE LOGIN... on all relevant instances, which will have to be hard-coded. So, this second method is only slightly better than manual intervention. However, assuming DBAs are the only ones who can create logins, it would be easy enough to make a procedure for all DBAs to always add a new login to this process whenever a new one is created. One thing to keep in mind, wherever the login is obtained from, the User level permissions will be carried over to all replicas, so make sure the "source" login/user is configured correctly before implementing any sync solution.
IMHO, logins and SQL Agent jobs are the only thing lacking from AlwaysOn, and perhaps in a future release, Microsoft will add some automatic functionality--especially for logins, since it really breaks the whole purpose of automatic failover if the logins aren't sync'd.
answered Aug 3 '17 at 14:31
SQL_HackerSQL_Hacker
362113
I don't know if this is still an issue for you, or if you've found a solution, but in case you haven't, a very powerful set of tools, and one specific to your issue, can be obtained from: https://dbatools.io. Specific to your issue, use "Copy-SqlLogin" and configure it as a scheduled task on each server (primary and all secondaries) in whatever configuration that will work for your needs. I used this solution at the last place I worked and scheduled it to run every hour, and never had an issue with logins after implementation.
If, for some reason, you cannot utilize dbatools, you will need to use sp_help_revlogin, devise some method to pick out the logins you want to "replicate" (store in a table??) and then execute the IF NOT EXISTS...CREATE LOGIN... on all relevant instances, which will have to be hard-coded. So, this second method is only slightly better than manual intervention. However, assuming DBAs are the only ones who can create logins, it would be easy enough to make a procedure for all DBAs to always add a new login to this process whenever a new one is created. One thing to keep in mind, wherever the login is obtained from, the User level permissions will be carried over to all replicas, so make sure the "source" login/user is configured correctly before implementing any sync solution.
IMHO, logins and SQL Agent jobs are the only thing lacking from AlwaysOn, and perhaps in a future release, Microsoft will add some automatic functionality--especially for logins, since it really breaks the whole purpose of automatic failover if the logins aren't sync'd.
answered Aug 3 '17 at 14:31
SQL_HackerSQL_Hacker
362113
answered Aug 3 '17 at 14:31
SQL_HackerSQL_Hacker
362113
answered Aug 3 '17 at 14:31
SQL_HackerSQL_Hacker
362113
answered Aug 3 '17 at 14:31
SQL_HackerSQL_Hacker
362113
362113
add a comment |
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%2f132421%2fsql-server-logins-un-sync-when-availability-group-fails-over%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
When the password is changed, the hash is changed as well.
– Kin
Mar 16 '16 at 17:27
I haven't tried this, but you could use a DDL trigger perhaps to change the password on both servers. And also look into whatever process is syncing them, sounds like something is not right there.
– Aaron Bertrand♦
Mar 16 '16 at 17:31
2
The hash is always going to be different on different servers, even with precisely the same plain-text password (unless the login was created using the hashed version of the password). Use the
PWDCOMPAREfunction to compare passwords.– Max Vernon
Mar 16 '16 at 18:11
1
When the instance fails over, and these logins no longer work, check the
LOGINPROPERTY(<loginname>, 'IsExpired')to see if the login has expired.– Max Vernon
Mar 16 '16 at 18:17
Which version of SQL Server is this?
– Max Vernon
Mar 16 '16 at 18:18