Jump to content

njordur

Members
  • Content Count

    24
  • Joined

  • Last visited

  • Days Won

    3

Everything posted by njordur

  1. Hi, Just wanted to give you a powershell script you can use the privileged accounts when its an AD user. I've tested this on my systems and it works fine with either SQL Auth or AD Auth. Error handling should be fine ... but feel free to add more or change the error handling or rename the second function in the script. Method: Way to add this into your discovery is to replace the contents of C:\inetpub\Passwordstate\setup\scripts\Get-MSSQLAccounts.ps1 (default path) and then restore the script from Administration -> Powershell scripts -> Scripts Account Discovery. I take no responsibility if anything breaks with this. Just make sure you backup Get-MSSQLAccounts.ps1 before you start. /Njörður <# .SYNOPSIS Connect to a Microsoft SQL server using the supplied Privileged Account Credentials, and discover SQL accounts .NOTES Requires database connections on in-use Port to be allowed through Firewall #> function GetMSSQLAccountsFun { [CmdletBinding()] param ( [String]$iHostName, [String]$iInstanceName, [String]$iSQLPort, [String]$iPrivilegedAccountUserName, [String]$iPrivilegedAccountPassword, [String]$iAccountsToDiscover, [String]$iAccountsToExclude ) #Declare some connection string variables [String]$iInstanceNameString = '' [String]$iSQLPortString = '' #Construct the Instance Name section of the connection string if required if ($iInstanceName -ne '') { $iInstanceNameString = '\' + $iInstanceName } #Construct the Port Number section of the connection string if required if ($iSQLPort -ne '') { $iSQLPortString = ',' + $iSQLPort } $SQLConnection = New-Object System.Data.SqlClient.SqlConnection #If the value of the password contains a single quote and a double quote, then we need to raise an exception as it's not possible to construct a working database connection string in this instance if ($iPrivilegedAccountPassword -like '*"*' -and $iPrivilegedAccountPassword -like "*'*") { $exception = New-Object System.Exception ("DoubleQuote"); throw $exception } elseif ($iPrivilegedAccountUserName -match "\\") { $SQLConnection.ConnectionString = "Server=" + $iHostName + $iInstanceNameString + $iSQLPortString + ";Integrated Security=SSPI" } else { $iPrivilegedAccountPassword = $iPrivilegedAccountPassword.Replace("'", "''") #double escape any single quotes $ipasswordString = ";Password='" + $iPrivilegedAccountPassword + "'" $SQLConnection.ConnectionString = "Server=" + $iHostName + $iInstanceNameString + $iSQLPortString + ";User ID=" + $iPrivilegedAccountUserName + $ipasswordString } #$SQLScript to be called once a database connection has been established. Add one command per line. $SQLScript = @" SELECT name, type_desc FROM sys.server_principals WHERE TYPE IN ('S') AND Name NOT LIKE '%##%' "@ try{ $SQLConnection.Open() $SQLCommand = $SQLConnection.CreateCommand() $SQLCommand.CommandText = $SQLScript $reader = $SQLCommand.ExecuteReader() #Define table for returning results $dt = New-Object System.Data.DataTable $column1 = $dt.Columns.Add("UserName", [string]) while ($reader.Read()) { #We need to exclude distributor_admin by default, and then process the remainder of the accounts as appropriate if ($reader['name'].ToLower() -ne 'distributor_admin') { $iDiscoveredUserName = $reader['name'] if ($iAccountsToDiscover -eq '') { if ($iAccountsToExclude -eq '') { #There are no Excluded Accounts to consider, so we will simply return all accounts $row = $dt.NewRow() $row.UserName = $iDiscoveredUserName $dt.rows.add($row) } else { #Since there is a value in $AccountsToExclude, we need to make sure we don't include any of them in the return results $iExcludedAccounts = $iAccountsToExclude.split(",") $iMatchFound = $false foreach ($iAccount in $iExcludedAccounts) { if ($iAccount -eq $iDiscoveredUserName) { $iMatchFound = $true } } #If there is no matching excluded account found, then we add the results to the datatable if ($iMatchFound -eq $false) { $row = $dt.NewRow() $row.UserName = $iDiscoveredUserName $dt.rows.add($row) } } } else { #Look for accounts specifically set in AccountsToDiscover - we don't need to consider $AccountsToExclude here, as you wouldn't use both parameters at the same time $iIncludedAccounts = $iAccountsToDiscover.split(",") foreach ($iAccount in $iIncludedAccounts) { #Add the account if we have a match if ($iAccount -eq $iDiscoveredUserName) { $row = $dt.NewRow() $row.UserName = $iDiscoveredUserName $dt.rows.add($row) } } } } } #Close and Dispose of objects $SQLConnection.Close() $SQLCommand.Dispose() $SQLConnection.Dispose() #Return Json data if datatable has any rows if (@($dt).count -ne 0) { #Single records (scalar) does not converts to Json format properly, so we need to adjust ourselves if (@($dt).count -eq 1) { $dt = $dt | select UserName | ConvertTo-Json Write-Output '[' $dt ']' } else { Write-Output $dt | select UserName | ConvertTo-Json } } } catch { switch -wildcard ($error[0].Exception.ToString().ToLower()) { "*A network-related or instance-specific*" { Write-Output "Failure: Failed to connect to the Host '$iHostName' to discover SQL accounts. Verify that the Host and instance name are correct, and that SQL Server is configured to allow remote connections. Also confirm that a firewall is not blocking access - default Port is 1433."; break } "*A transport-level error*" { Write-Output "Failure: Failed to connect to the Host '$iHostName' to discover SQL accounts. Verify that the Host and instance name are correct, and that SQL Server is configured to allow remote connections. Also confirm that a firewall is not blocking access - default Port is 1433."; break } "*Login failed for user*" { Write-Output "Failure: Failed to connect to the Host '$iHostName' to discover SQL accounts. Please check the Privileged Account Credentials associated with the Password record is correct."; break } "*DoubleQuote*" { Write-Output "Failure: Failed to execute script correctly against Host '$iHostName' for the account '$iPrivilegedAccountUserName'. Privileged Account Password has both single and double quotes, which interferes with database connection strings."; break } "*WinRM cannot complete the operation*" { Write-Output "Failure: Failed to query for SQL accounts on Host '$iHostName' as it appears the Host is not online, or PowerShell Remoting is not enabled or hostname is invalid"; break } "*WinRM client cannot process the request*" { Write-Output "Failure: Failed to query for SQL accounts on Host '$iHostName' as it appears the Host is not online, or PowerShell Remoting is not enabled or hostname is invalid"; break } "*WS-Management service running*" { Write-Output "Failure: Failed to query for SQL accounts on Host '$iHostName' as it appears the Host is not online, or PowerShell Remoting is not enabled."; break } "*cannot find the computer*" { Write-Output "Failure: Failed to query for SQL accounts on Host '$iHostName' as it appears the Host is not online, or PowerShell Remoting is not enabled."; break } "*no logon servers available*" { Write-Output "Failure: Failed to query for SQL accounts on Host '$iHostName'. There are currently no logon servers available to service the logon request."; break } "*user name or password is incorrect*" { Write-Output "Failure: Failed to query for SQL accounts on Host '$iHostName' as the Privileged Account password appears to be incorrect, or the account is currently locked."; break } "*access is denied*" { Write-Output "Failure: Failed to query for SQL accounts on Host '$iHostName' as the Privileged Account does not appear to have the required permissions on the host, or its password could be incorrect."; break } "*execution policy error*" { Write-Output "Failure: Failed to query for SQL accounts on Host '$iHostName' as PowerShell hasn't been configured to allow execution of scripts - Needed for IIS Application Pools in some circumstances. See about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170"; break } #Add other wildcard matches here as required default { Write-Output "Failure: Failed to discover SQL accounts on Host '$iHostName'. Error = " + $error[0].Exception } } } } function Get-MSSQLAccounts { [CmdletBinding()] param ( [String]$HostName, [String]$InstanceName, [String]$SQLPort, [String]$PrivilegedAccountUserName, [String]$PrivilegedAccountPassword, [String]$AccountsToDiscover, [String]$AccountsToExclude ) try { if ($PrivilegedAccountUserName -match "\\") { #Establish the PowerShell Credentials used to execute the script block - based on the Privileged Account Credentials selected for this script $CredPassword = ConvertTo-SecureString $PrivilegedAccountPassword -AsPlainText -Force $Credentials = New-Object System.Management.Automation.PSCredential($PrivilegedAccountUserName, $CredPassword) #Execute the command and put the output in an array. Since we are running only SQL commands remotely there is no need to connect to remote host. $PSSessionOption = New-PSSessionOption -OperationTimeout 3000 -OpenTimeOut 3000 $resultsarray = Invoke-Command -SessionOption $PSSessionOption -ComputerName . -Authentication 'Credssp' -Credential $Credentials -ScriptBlock $function:GetMSSQLAccountsFun -ArgumentList $HostName, $InstanceName,$SQLPort,$PrivilegedAccountUserName,$PrivilegedAccountPassword,$AccountsToDiscover,$AccountsToExclude 2>&1 #Using 2>&1 to ensure STDERR is piped to STDOUT #include Port Number in SPN if required If ($resultsarray.ErrorDetails.Message -like "*Connecting to remote server $HostName failed with the following error message*0x80090322*" -or $resultsarray.ErrorDetails.Message -like "*Connecting to remote server $HostName failed with the following error message*The WS-Management service cannot complete the operation within the time specified in OperationTimeout*") { #Execute the command and put the output in an array. $PSSessionOption = New-PSSessionOption -OperationTimeout 3000 -OpenTimeOut 3000 -IncludePortInSPN $resultsarray = Invoke-Command -SessionOption $PSSessionOption -ComputerName . -Authentication 'Credssp' -Credential $Credentials -ScriptBlock $function:GetMSSQLAccountsFun -ArgumentList $HostName, $InstanceName,$SQLPort,$PrivilegedAccountUserName,$PrivilegedAccountPassword,$AccountsToDiscover,$AccountsToExclude 2>&1 #Using 2>&1 to ensure STDERR is piped to STDOUT } $resultsarray } else { GetMSSQLAccountsFun -iHostname $HostName -iInstanceName $InstanceName -iSQLPort $SQLPort -iPrivilegedAccountUserName $PrivilegedAccountUserName -iPrivilegedAccountPassword $PrivilegedAccountPassword -iAccountsToDiscover $AccountsToDiscover -iAccountsToExclude $AccountsToExclude } } catch { switch -wildcard ($error[0].Exception.ToString().ToLower()) { "*WinRM cannot complete the operation*" { Write-Output "Failure: Failed to query for SQL Ac on Host '$HostName' as it appears the Host is not online, or PowerShell Remoting is not enabled."; break } "*WS-Management service running*" { Write-Output "Failure: Failed to query for Dependencies on Host '$HostName' as it appears the Host is not online, or PowerShell Remoting is not enabled."; break } "*cannot find the computer*" { Write-Output "Failure: Failed to query for Dependencies on Host '$HostName' as it appears the Host is not online, or PowerShell Remoting is not enabled."; break } "*no logon servers available*" { Write-Output "Failure: Failed to query for Dependencies on Host '$HostName'. There are currently no logon servers available to service the logon request."; break } "*user name or password is incorrect*" { Write-Output "Failure: Failed to query for Dependencies on Host '$HostName' as the Privileged Account password appears to be incorrect, or the account is currently locked."; break } "*access is denied*" { Write-Output "Failure: Failed to query for Dependencies on Host '$HostName' as the Privileged Account does not appear to have the required permissions on the host, or its password could be incorrect."; break } "*execution policy error*" { Write-Output "Failure: Failed to query for Dependencies on Host '$HostName' as PowerShell hasn't been configured to allow execution of scripts - Needed for IIS Application Pools in some circumstances. See about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170"; break } #Add other wildcard matches here as required default { Write-Output "Failure: Failed to query for Dependencies on Host '$HostName'.Error = $resultsarray." } } } } #Make a call to the Get-MSSQLAccounts function Get-MSSQLAccounts -HostName '[HostName]' -InstanceName '[SQLInstanceName]' -SQLPort '[DatabasePort]' -PrivilegedAccountUserName '[PrivilegedAccountUserName]' -PrivilegedAccountPassword '[PrivilegedAccountPassword]' -AccountsToDiscover '[AccountsToDiscover]' -AccountsToExclude '[AccountsToExclude]'
  2. njordur

    MSSQL Account Discovery with AD User

    Of course you can use it... just make sure to test it well before
  3. I can't see it in the API documentation. Is it possible give individual permissions to passwords in Passwordstate? If not can I expect this to be in the API any time soon. /Njörður
  4. njordur

    API Create Password with $ in it

    Thanks for this, didn't solve the input problem though. I just excluded $ from the input so I don't have to handle it. Nice touch with the psobject, will use that. /Njörður
  5. Hi, Can you fix so the whenever you use the API to create a password and the password has a $ sign in it truncates the rest of the password set in passwordstate. I proably can fix this with modifications in my script. But just wanted to have this so I wouldn't have to. Example of a powershell script, works fine if there are no $ signs in the password. Probably the same happens with other parameters. param($username, $password, $description) $jsonString = @" { "PasswordListID":92, "Title":"company\\$username", "Description":"$description", "AccountTypeID":64, "UserName":"$username", "password":"$password", "APIKey":"XXXXXXXXXXXXXXXXXXXXXXXX", "PasswordResetEnabled":true, "PrivilegedAccountID":2, "HeartbeatEnabled":true, "ValidationScriptID":7, "ADDomainNetBIOS":"company" } "@ Invoke-RestMethod -Uri https://passwordstate.company.com/api/passwords/ -Method Post -ContentType "application/json" -Body $jsonString
  6. I ran into a problem when I was trying to use a generic field in password validation. It doesn't seem to get through even though its available from the variable list. I even tried to pass on the URL variable which was standard and had the same results. I made a script for password reset with successful results. This is the script I tried without luck. What I'm trying to do is to validate a password for a contained database user. <# .SYNOPSIS Connect to a Microsoft SQL server and validates the password for a local SQL account. .NOTES Requires database connections on in-use Port to be allowed through Firewall #> function Validate-SQLPassword { [CmdletBinding()] param ( [String]$HostName, [String]$InstanceName, [String]$SQLPort, [String]$UserName, [String]$CurrentPassword, [String]$Database = 'master' ) try { #Declare some connection string variables [String]$InstanceNameString = '' [String]$SQLPortString = '' #Construct the Instance Name section of the connection string if required if ($InstanceName -ne '') { $InstanceNameString = '\' + $InstanceName } #Construct the Port Number section of the connection string if required if ($SQLPort -ne '') { $SQLPortString = ',' + $SQLPort } $SQLConnection = New-Object System.Data.SqlClient.SqlConnection $SQLConnection.ConnectionString = "Server=" + $HostName + $InstanceNameString + $SQLPortString + ";User ID=" + $UserName + ";Password=" + $CurrentPassword + ";Initial Catalog=" + $Database + ";" $SQLConnection.Open() $SQLConnection.Close() Write-Output "Success" } catch { switch -wildcard ($error[0].Exception.ToString().ToLower()) { "*A network-related or instance-specific*" { Write-Output "Failed to execute script correctly against Host '$HostName' for the account '$UserName'. Please check SQL details are correct, and that a firewall is not blocking access - default Port is 1433."; break } "*The password of the account must be changed*" { Write-Output "Failed to validate the password for the SQL account '$UserName' on Host '$HostName'. Password has expired."; break } "*The account is disabled*" { Write-Output "Failed to validate the password for the SQL account '$UserName' on Host '$HostName'. Account is disabled."; break } #"*Login failed for user*" { Write-Output "Failed to validate the password for the SQL account '$UserName' on Host '$HostName'. UserName or Password is incorrect."; break } #Add other wildcard matches here as required default { Write-Output "Failed to validate the password for the SQL account '$UserName' on Host '$HostName' with Database:'$Database' Error = " + $error[0].Exception} } } } #Make a call to the Validate-SQLPassword function Validate-SQLPassword -HostName '[HostName]' -InstanceName '[SQLInstanceName]' -SQLPort '[DatabasePort]' -Username '[UserName]' -CurrentPassword '[CurrentPassword]' -Database '[GenericField1]'
  7. njordur

    Custom Password Validation Error

    No rush, its just an improvement for you guys :). I'll just wait for the release.
  8. njordur

    Custom Password Validation Error

    No thats not it, I'm overwriting it each time with the variable. I hadn't even started handling that part. What comes in the variable database is just [GenericField1] So if I switch the catch output to this... switch -wildcard ($error[0].Exception.ToString().ToLower()) { #"*A network-related or instance-specific*" { Write-Output "Failed to execute script correctly against Host '$HostName' for the account '$UserName'. Please check SQL details are correct, and that a firewall is not blocking access - default Port is 1433."; break } #"*The password of the account must be changed*" { Write-Output "Failed to validate the password for the SQL account '$UserName' on Host '$HostName'. Password has expired."; break } #"*The account is disabled*" { Write-Output "Failed to validate the password for the SQL account '$UserName' on Host '$HostName'. Account is disabled."; break } #"*Login failed for user*" { Write-Output "Failed to validate the password for the SQL account '$UserName' on Host '$HostName'. UserName or Password is incorrect."; break } #Add other wildcard matches here as required default { Write-Output "Failed to validate the password for the SQL account '$UserName' on Host '$HostName' with Database:'$Database' Error = " + $error[0].Exception} } Then this is what I get in the attached picture
  9. I'm using the latest version 7.7 (Build 7737) When I'm adding a new password to a password list that does not have the password & password strength field shown on it nothing happens. This happens both to old password lists and new. The cause seems to be that the password strength field has the required field grayed out and enabled so it seems to be still in effect even if the field is not shown. This started when I upgraded directly from Version 7.6 (Build 7676) to Build 7737
  10. njordur

    Password discovery - bug of feature?

    Yes this was it thanks a lot for clarifying. About the changelog on the webpage can you update it.
  11. njordur

    Password discovery - bug of feature?

    Ah ok, that must be the case then. I'll try to add the value and see if it pops in.
  12. njordur

    Password discovery - bug of feature?

    This does not work for me i have build 7537. Still creates a new password for a existing password in another password list. Also update the changelog plz.
  13. njordur

    Passwordstate 7.5 (Build 7539) Update

    Seems the changelog web page hasnt been updated since november 18, version 7483. You might want to look at that. I would like to to be aware of the changes made
  14. Maybe I spoke too soon. I just added the same computer again manually with a different instance name and added a password reset task on it. Hopefully this will solve the issue.
  15. I have several SQL servers with multiple instances on them. What would be the best way to do change the password on the instances this as the hosts in passwordstate only provides an option for one instance. Is DNS alias my only option perhaps?
  16. Hi, I'm using v.7.3 (Build 7316) and have restored the latest discovery scripts to the database. I had a problem with one 2012R2 server when discovering the resources on it. The problem had to do with the service account on the server had an SPN record for http. So the get-resources script needed to use the option If ($resultsarray.ErrorDetails.Message -like "*Connecting to remote server $HostName failed with the following error message*0x80090322*" -or $resultsarray.ErrorDetails.Message -like "*Connecting to remote server $HostName failed with the following error message*The WS-Management service cannot complete the operation within the time specified in OperationTimeout*") But the if statement: If ($resultsarray.ErrorDetails.Message -like "*Connecting to remote server $HostName failed with the following error message*0x80090322*" -or $resultsarray.ErrorDetails.Message -like "*Connecting to remote server $HostName failed with the following error message*The WS-Management service cannot complete the operation within the time specified in OperationTimeout*") didn't work since this is not the error message I got was different. When I added: If ($resultsarray.ErrorDetails.Message -like "*Connecting to remote server $HostName failed with the following error message*0x80090322*" -or $resultsarray.ErrorDetails.Message -like "*Connecting to remote server $HostName failed with the following error message*The WS-Management service cannot complete the operation within the time specified in OperationTimeout*") ... the results from the server came through and was written in passwordstate. Just wanted to let the community know if anyone else had this problem.
  17. njordur

    Get-Resources script

    Hi, I'm wondering if the get-resources.ps1 discovery script is run from the database or from the c:\inetpup\passwordstate\setup\scripts folder. Reason I ask is because I want to change it by adding IncludePortInSPN parameter to the script, like this: $PSSessionOption = New-PSSessionOption -OpenTimeOut 3000 -IncludePortInSPN Only reason is because some applications need to have spn for http and when a remote powershell session is trying to open a connection to those computer it fails because the SPN points to port 80 for http instead of 5985. I've verified this by registering spn for those computers with this: SetSPN.exe -s HTTP/$($env:COMPUTERNAME):5985 $env:COMPUTERNAME SetSPN.exe -s HTTP/$($env:COMPUTERNAME).$($env:USERDNSDOMAIN):5985 $env:COMPUTERNAME And then running the script manually. So how can I change the discovery script do this by default, or is it enough for me to change the script in the folder?
  18. njordur

    Get-Resources script

    This is how the script shows the empty string ResourceName when I ran the non-modified script against a server that had these problems (It was a 2008R2 File server): PS C:\scripts\Get-Resources.ps1 [ { "ResourceType": "Windows Service", "ResourceName": "", "UserName": "" }, { "ResourceType": "Scheduled Task", "ResourceName": "DHCP Backup", "UserName": "DOMAIN\\SOMEUSER" } ]
  19. njordur

    Get-Resources script

    Hi I think I found a small bug in the get-resources script. I was getting services from some servers with startname that had $null in startname and would go into the database with an empty string.... couldn't figure out which services. Then it was crashing the discovery for the resources. Maybe you should update the script with this or some other way you see fit. I updated the script for the service part like this. if($WindowsService.StartName -ne $null){ $row = $dt.NewRow() $row.ResourceType = 'Windows Service' $row.ResourceName = $WindowsService.DisplayName $row.UserName = $WindowsService.StartName $dt.rows.add($row) } For reference, this is the error I got in the event logs: Error executing 'ProcessResourcesDiscoveryJob' - An error has occurred in the Passwordstate Windows Service executing the method 'ProcessResourcesDiscoveryJob'. Error = Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path '', line 1, position 1.&stacktrace= at Newtonsoft.Json.Converters.DataTableConverter.ReadJson(JsonReader reader, Type objectType, Object existingValue, JsonSerializer serializer) at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.DeserializeConvertable(JsonConverter converter, JsonReader reader, Type objectType, Object existingValue) at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent) at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType) at Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonSerializerSettings settings) at Newtonsoft.Json.JsonConvert.DeserializeObject[T](String value, JsonSerializerSettings settings) at PasswordstateService.PasswordstateService.QueryHostForResourceDiscovery(String HostID, String HostName, String Script, String PasswordListID, String InitialPassword, String AddDaysExpiryDate, String WindowsServicesScriptID, String IISAppPoolsScriptID, String ScheduledTasksScriptID, String PasswordList, String AutoGenerateExpiredPassword, String AutoGenerateExpiredTime, String AutoGenerateAddDays, String AutoGenerateUnlockAD, String AccountTypeID, String TreePath, String Title, String Description, Boolean SimulationMode) at PasswordstateService.PasswordstateService.ProcessResourcesDiscoveryJob(DataTable DataTable)
  20. njordur

    Get-Resources script

    Nice, missed that in the upgrade log, was using 7200. Upgraded, tried it and verified it works. Super service.
×