August 29, 2017 at 9:28 am
Hi
Does anyone have any scripts or tips for altering multiple sql authenticated logins on a sql instance. T-SQL or Power shell.
I am aware of this method below but perhaps someone knows of a better method.
SELECT 'ALTER LOGIN ' + name + ' WITH PASSWORD = ''99new_!'password'' MUST_CHANGE,
CHECK_EXPIRATION = ON' FROM sys.server_principals where WHERE type in ('S')
order by name;
Thanks in advance
August 29, 2017 at 3:01 pm
That's generally how it's done if you want to generate an alter login script for multiple logins. You would get syntax errors with what you posted. Was that just as an example of doing this with sys.server_principals?
Sue
August 30, 2017 at 7:13 am
Yeah Sorry Sue the above is just an example, please ignore the syntax.
I was wondering if there was an easier way in powershell or a way to auto assign different passwords to different accounts.
August 30, 2017 at 8:30 am
caz100 - Wednesday, August 30, 2017 7:13 AMYeah Sorry Sue the above is just an example, please ignore the syntax.I was wondering if there was an easier way in powershell or a way to auto assign different passwords to different accounts.
Thanks - I'll ignore the syntax 🙂
I don't think there is anyway to automatically assign different passwords to each of the different SQL Logins. You could do it in Powershell but it is not necessarily the Holy Grail it sometimes is made out to be. It has some great uses - I use it for different things - but using t-sql is a better way to do most things in SQL Server. I could be wrong but it really seems that the process you are using is likely the best path.
Here are a couple of Powershell examples of changing the password and properties for the logins - you would likely need to change those to create a collection of logins and then iterate though the logins. They all have hard coded passwords in their changes but you could write a function that generates different passwords and then assign those to the different logins while iterating through them:
SQL Server: Change the Login Password using PowerShell
SQL Server PowerShell SMO – Simple way to Change SQL User Passwords
Sue
August 30, 2017 at 12:21 pm
Thanks Sue I will stick with the sql, using a password generator for the passwords, with some excel concatenation to create a long list of password alterations and run it that way.
August 30, 2017 at 5:11 pm
I think you're picking the simplest approach by using SQL, but you can definitely define different passwords. Here's a very simple demo, just varying the password by a number, but the variation is only limited by what you can do with a number. 😉
WITH cteLogins AS (
SELECT name, RN = ROW_NUMBER() OVER(ORDER BY name)
FROM sys.server_principals
WHERE type = 'S'
AND name NOT LIKE '##%'
)
SELECT 'ALTER LOGIN ' + name + ' WITH PASSWORD = ''new_password_' + RIGHT('00000' + CONVERT(Varchar(5), RN), 5) + ''';'
FROM cteLogins
ORDER BY name;
August 31, 2017 at 4:11 am
@ed - Thanks that code it is very useful.
Another more complex challenge is to alter the password for an SA account on 100+ different sql instances, all on the same day!
@sue - to correct my original systax, it is as follows:
select 'alter login ' + name + ' with password = ''99new_password'' must_change, check_expiration=on, check_policy=on;'
from sys.server_principals
where Type IN ('S');
August 31, 2017 at 6:28 am
caz100 - Thursday, August 31, 2017 4:11 AM@ed - Thanks that code it is very useful.Another more complex challenge is to alter the password for an SA account on 100+ different sql instances, all on the same day!
@sue - to correct my original systax, it is as follows:
select 'alter login ' + name + ' with password = ''99new_password'' must_change, check_expiration=on, check_policy=on;'
from sys.server_principals
where Type IN ('S');
Thanks. I'm glad it works for you.
Regarding changing a password on multiple servers, it's actually quite simple if you have them registered as servers in your SSMS. In the Registered Servers pane, right-click the name of the group that contains the servers and then click New Query. SSMS will open a window with sessions connected to the servers in that group. Type your ALTER LOGIN statement. When you run it, it'll run against all the connected servers. Then you can repeat for the other groups until they're all done.
August 31, 2017 at 6:37 am
caz100 - Thursday, August 31, 2017 4:11 AM@sue - to correct my original systax, it is as follows:select 'alter login ' + name + ' with password = ''99new_password'' must_change, check_expiration=on, check_policy=on;'
from sys.server_principals
where Type IN ('S');
Okay thanks. Ed already has you covered on the query.
August 31, 2017 at 8:18 am
Regarding changing a password on multiple servers, it's actually quite simple if you have them registered as servers in your SSMS. In the Registered Servers pane, right-click the name of the group that contains the servers and then click New Query. SSMS will open a window with sessions connected to the servers in that group. Type your ALTER LOGIN statement. When you run it, it'll run against all the connected servers. Then you can repeat for the other groups until they're all done.
Thanks Ed that sounds like an nice and easy method to do it.
September 1, 2017 at 2:27 am
caz100 - Thursday, August 31, 2017 4:11 AMAnother more complex challenge is to alter the password for an SA account on 100+ different sql instances, all on the same day!
I have done this in the past before with PoSh where the serverlist and password where defined in a txt file the script read in and went and modified the sa Password. Not ideal having the password in clear text, but the file only existed while the process was being run and was local to the persons workstation but again is a flaw in the process.
My script was Add-Type -LiteralPath "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
Foreach ($list In $(Get-Content -Path C:\ProdSAPasswordChange.txt)) {
$server = $list.Split(":")
$srvObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $server[0]
$srvObject.Logins.Item('sa').ChangePassword($server[1])
$srvObject.Logins.Item('sa').Alter()
}
The format of the textfile is Servername: Password (Remove the space between : P as they need to be joined together), on as many lines as you need to change.
If you wanted to get cleaver you could always integrate a random password generator like the belowfunction New-SWRandomPassword {
<#
New-SWRandomPassword -MinPasswordLength 8 -MaxPasswordLength 12 -Count 4
#>
[CmdletBinding(DefaultParameterSetName='FixedLength',ConfirmImpact='None')]
[OutputType([String])]
Param
(
# Specifies minimum password length
[Parameter(Mandatory=$false,
ParameterSetName='RandomLength')]
[ValidateScript({$_ -gt 0})]
[Alias('Min')]
[int]$MinPasswordLength = 8,
# Specifies maximum password length
[Parameter(Mandatory=$false,
ParameterSetName='RandomLength')]
[ValidateScript({
if($_ -ge $MinPasswordLength){$true}
else{Throw 'Max value cannot be lesser than min value.'}})]
[Alias('Max')]
[int]$MaxPasswordLength = 12, # Specifies a fixed password length
[Parameter(Mandatory=$false,
ParameterSetName='FixedLength')]
[ValidateRange(1,2147483647)]
[int]$PasswordLength = 8,
# Specifies an array of strings containing charactergroups from which the password will be generated.
# At least one char from each group (string) will be used.
[String[]]$InputStrings = @('abcdefghijklmnopqrstuvwxyz', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', '1234567890', '!"£$%^&*()_-+={[}]~#@:;?/><\'), # Specifies a string containing a character group from which the first character in the password will be generated.
# Useful for systems which requires first char in password to be alphabetic.
[String] $FirstChar,
# Specifies number of passwords to generate.
[ValidateRange(1,2147483647)]
[int]$Count = 1
)
Begin {
Function Get-Seed{
# Generate a seed for randomization
$RandomBytes = New-Object -TypeName 'System.Byte[]' 4
$Random = New-Object -TypeName 'System.Security.Cryptography.RNGCryptoServiceProvider'
$Random.GetBytes($RandomBytes)
[BitConverter]::ToUInt32($RandomBytes, 0)
}
}
Process {
For($iteration = 1;$iteration -le $Count; $iteration++){
$Password = @{}
# Create char arrays containing groups of possible chars
[char[][]]$CharGroups = $InputStrings # Create char array containing all chars
$AllChars = $CharGroups | ForEach-Object {[Char[]]$_} # Set password length
if($PSCmdlet.ParameterSetName -eq 'RandomLength')
{
if($MinPasswordLength -eq $MaxPasswordLength) {
# If password length is set, use set length
$PasswordLength = $MinPasswordLength
}
else {
# Otherwise randomize password length
$PasswordLength = ((Get-Seed) % ($MaxPasswordLength + 1 - $MinPasswordLength)) + $MinPasswordLength
}
} # If FirstChar is defined, randomize first char in password from that string.
if($PSBoundParameters.ContainsKey('FirstChar')){
$Password.Add(0,$FirstChar[((Get-Seed) % $FirstChar.Length)])
}
# Randomize one char from each group
Foreach($Group in $CharGroups) {
if($Password.Count -lt $PasswordLength) {
$Index = Get-Seed
While ($Password.ContainsKey($Index)){
$Index = Get-Seed
}
$Password.Add($Index,$Group[((Get-Seed) % $Group.Count)])
}
} # Fill out with chars from $AllChars
for($i=$Password.Count;$i -lt $PasswordLength;$i++) {
$Index = Get-Seed
While ($Password.ContainsKey($Index)){
$Index = Get-Seed
}
$Password.Add($Index,$AllChars[((Get-Seed) % $AllChars.Count)])
}
Write-Output -InputObject $(-join ($Password.GetEnumerator() | Sort-Object -Property Name | Select-Object -ExpandProperty Value))
}
}
}
New-SWRandomPassword -MinPasswordLength 30 -MaxPasswordLength 30 -Count 4
September 1, 2017 at 9:04 am
Thanks Anthony its another good option if required.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply