Sync-DbaLoginPassword - Support for sync SQL Login passwords between instances

[ConstantineK]

Is this a feature OR bug:

Feature - Basically a useful feature would be to transmit the current passwords to the user accounts on a remote box for matching users.

eg

Sync-SqlLoginPermissions -Source $a -Dest $b -UpdatePassOnly (or whatever makes sense)

This is specifically targeting AG secondaries, but makes sense for mirroring scenarios and the like.

[amtwo]

This is particularly useful when there are multiple AGs, and you may have an AG active on both sides, and thus the login being used on both sides. The trick would be identifying the newer password to sync in the correct direction.

[potatoqualitee]

I think this should be a new command Sync-DbaLoginPassword then the ppl would just have to run a couple things

[ConstantineK]

That's a good idea @ctrlbold.

@amtwo do we have any insight into checking this for AD stuff? I see LOGINPROPERTY works with SQL auth, but not AD.

This built-in function returns information about the password policy settings of a SQL Server login. [...] The values of the [...] PasswordLastSetTime properties are available on all supported configurations of SQL Server [...]

I imagine we could ask the domain controller directly for this information (though I am not sure of the perms required, we do some other AD querying in the rest of the project.)

Also the note on "on all supported configurations" likely means we will need to test this functionality on SQL Server 2000/5 (at least) to verify this works, though obviously with an AG focus that wont matter as much 🐤

[niphlod]

No need to compare passwords on Windows logins...that's a bit part of the whole deal (managing user properties OUTSIDE of mssql) ;-)

Il 12 apr 2017 3:36 PM, "Constantine" [email protected] ha scritto:

That's a good idea @ctrlbold https://github.com/ctrlbold.

@amtwo https://github.com/amtwo do we have any insight into checking this for AD stuff? I see LOGINPROPERTY https://docs.microsoft.com/en-us/sql/t-sql/functions/loginproperty-transact-sql works with SQL auth, but not AD.

This built-in function returns information about the password policy settings of a SQL Server login. [...] The values of the [...] PasswordLastSetTime properties are available on all supported configurations of SQL Server [...]

I imagine we could ask the domain controller directly for this information (though I am not sure of the perms required, we do some other AD querying in the rest of the project.)

Also the note on "on all supported configurations" likely means we will need to test this functionality on SQL Server 2000/5 (at least) to verify this works, though obviously with an AG focus that wont matter as much 🐤

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/sqlcollaborative/dbatools/issues/1032#issuecomment-293578526, or mute the thread https://github.com/notifications/unsubscribe-auth/AAHdB6W8KhZU_viAI21gm8uJQUN-xoE-ks5rvNNbgaJpZM4M5ZwD .

[ConstantineK]

You're right niphlod, I am being silly without coffee.

[niphlod]

summing up, the activity needs to take place only for SQL Logins (maybe skip entirely the disabled ones)

if MSSQL is -ge 2008, use LOGINPROPERTY('PasswordLastSetTime') to retrieve it.

Else, see updatedate on sys.syslogins (this one lies a bit (e.g. gets resetted even for other changed properties)) but if the main goal is syncing and the two have the same, we're all good. If they're not, updating its password won't be that big of a deal.

[andreasjordan]

Just thinking about how often you want to run this? Daily? Hourly? Shouldn't a server level trigger be the better option?

[github-actions[bot]]

🚧🚨 This issue is being marked as stale due to 90 days of inactivity. If you would like this issue to remain open:

• Verify the issue/bug is reproduced in the latest version of the module
• Verify the environmental info provided is still accurate
• Add any additional steps you followed to reproduce if necessary 🚨🚧 ⌛️ This issue will be closed in 30 days ⌛️

[potatoqualitee]

Noo i do not like this bot.

[wsmelton]

This is particularly useful when there are multiple AGs, and you may have an AG active on both sides, and thus the login being used on both sides. The trick would be identifying the newer password to sync in the correct direction.

We won't have control over this in an efficient manner because logically we can't decide which replica is the source of truth. If this command is running, and someone decided to change the password on a secondary, we cannot make that choice for the user to say well the password on that secondary is source of truth and just go set that on all replicas.

This control would have to be on the user to set and manage the expectation accordingly when they implement the process, just like with Copy-DbaLogin (which also overwrites the password).

I'll see if I can come up with a first draft on this one as I have an idea how it could be done easily.

[wsmelton]

This command will only cover copying a given password over for a login to one-to-many destinations. It can't cover AG syncing in the same command as that would be out of scope.

Either Sync-DbaAvailabilityGroup can be updated to perform the password sync or a secondary Sync-DbaAgLoginPassword can be created that handles all the replica lookups and connections. Then any logic that is desired for dealing with source of truth can be placed into that command.