MSSQL Account Discovery with AD User

[njordur]

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]'

 

[support]

Hello njordur,

 

Thanks so much for sharing this script, and all the hard work you've put into this. With your permission, we will see if we can incorporate this into our own script as well, otherwise the next time we make modifications to this script, it will be overwritten?

Maybe what we can do is figure out a way to either use a SQL Account for this, or an AD account - and let the customer decide what they want to use.

Thanks again, and please let us know if you are okay with us using your script in our software?

Regards

Click Studios

[njordur]

Of course you can use it... just make sure to test it well before