# encoding: ascii
# api: powershell
# title: SQL Query - AD Pwd Reset
# description: Script connects to a SQL database and runs a query against the specified table. Depending on table record values, an Active Directory user object will have it’s password reset.  Once, the account is reset the SQL record is updated.
# version: 0.1
# type: script
# author: Vidrine
# license: CC0
# function: Get-Timestamp
# x-poshcode-id: 3336
# x-archived: 2014-08-18T21:32:49
# x-published: 2014-04-10T11:12:00
#
# This SQL update is to prevent resetting the user object’s password, again, and to store the password for use.
#
<#
.SYNOPSIS
  Author:......Vidrine
  Date:........2012/04/08
.DESCRIPTION
  Script connects to a SQL database and runs a query against the specified table. Depending on table record values, 
  an Active Directory user object will have it's password reset.  Once, the account is reset the SQL record is updated.
  This SQL update is to prevent resetting the user object's password, again, and to store the password for use.
.NOTES
  Requirements:
  .. Microsoft ActiveDirectory cmdlets
  .. Microsoft SQL cmdlets
  
  Additionally:
  The script must be ran as account that has access to the database and access to 'reset passwords' within ActiveDirectory.
#>

##====================================================================================
## Load snapins and modules
##====================================================================================
add-pssnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
add-pssnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
Import-Module activeDirectory -ErrorAction SilentlyContinue

##====================================================================================
## Variables: SQL Connection
##====================================================================================
$sqlServerInstance  = 'SERVER\INSTANCE' # ex. '.\SQLEXPRESS'
$sqlDatabase        = 'DatabaseName'
$sqlTable           = 'TableName'

##====================================================================================
## Variables: Password Creation/Reset Configuration
##====================================================================================
## File contains a list of 5-character words, 1 per line.
$word    = Get-Content "C:\..\5CharacterDictionary.txt"
## List of allowed special characters for use
$special ='!','@','#','$','%','^','&','*','(',')','-','_','+','='
## Length of the random number
$nmbr    = 4

##====================================================================================
## Variables: Log
##====================================================================================
$logFile = (Get-Date -Format yyyyMMdd) + '_LogFile.csv'
$logPath = 'C:\..\Logs'
$log     = Join-Path -ChildPath $logFile -Path $logPath

##====================================================================================
## Functions
##====================================================================================
function Get-Timestamp {
	Get-Date -Format u
}

function Write-Log {
	param(
		[string] $Path,
		[string] $Value
	)

	$Value | Out-File $Path -Append
}

function Create-Password {
	## Generate random 4 digit integer.
	$NewString = ""
	1..$nmbr | ForEach { $NewString = $NewString + (Get-Random -Minimum 0 -Maximum 9) }

	## Select random 5-character word from wordlist
	$lowerWord		= Get-Random $word

	## Normalize the selected word. Convert all to lowerCase and then convert third character to UPPERcase
	$firstLetters	= $lowerWord.Substring(0,2)
	$upperLetters	= $lowerWord.Substring(2,1).toUpper()
	$lastLetters	= $lowerWord.Substring(3,2)
	$NewWord		= $firstLetters + $upperLetters + $lastLetters

	## Select random special character from wordlist
	$NewSpecial = Get-Random $special
	
	## Combine selected word, random number, and special character to generate password
	$NewPassword = ($NewWord + $NewSpecial + $NewString)
	
	## Returns the newly created random string to the function
	return $NewPassword
}

Function Reset-Password {
	param (
		[string]$emailAddress,
		[string]$password
	)

	## Convert the password to secure string
	$password_secure = ConvertTo-SecureString $password -AsPlainText -Force
		
	## Query for the user based on email address; Resets the user account password with value from database
	try
	{	
		Get-ADUser -Filter {emailAddress -like $emailAddress} | Set-ADAccountPassword -Reset -NewPassword $password_secure
		$Value = (get-timestamp)+"`tSUCCESS`tReset Password`tPassword reset completed for end user ($emailAddress)."
		Write-Log -Path $log -Value $Value
	}
	catch
	{
		$Value = (get-timestamp)+"`tERROR`tReset Password`tUnable to reset password ($emailAddress). $_"
		Write-Log -Path $log -Value $Value
	}
}

function Get-Username {
	param (
		[string]$emailAddress
	)
	
	try
	{
		$user = Get-ADUser -Filter {emailAddress -like $emailAddress}
		$Value = (get-timestamp)+"`tSUCCESS`tQuery Username`tDirectory lookup for username was successful ($emailAddress)."
		Write-Log -Path $log -Value $Value
		
		return $user.sAMAccountName
	}
	catch
	{
		$Value = (get-timestamp)+"`tERROR`tQuery Username`tDirectory lookup failed ($emailAddress). $_"
		Write-Log -Path $log -Value $Value
	}
}

function SQL-Select {
<#
.EXAMPLE
$results = SQL-Select -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -selectWhat '*'
.EXAMPLE
$results = SQL-Select -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -selectWhat '*' -where "id='64'"
#>

param(
	[string]$server,
	[string]$database,
	[string]$table,
	[string]$selectWhat,
	[string]$where
	
)

## SELECT statement with a WHERE clause
if ($where){
$sqlQuery = @"
SELECT $selectWhat 
FROM $table 
WHERE $where
"@
}

## General SELECT statement
else {
$sqlQuery = @"
SELECT $selectWhat 
FROM $table
"@
}

try
{
$results = Invoke-SQLcmd -ServerInstance $server -Database $database -Query $sqlQuery
$Value = (get-timestamp)+"`tSUCCESS`tSQL Select`tDatabase query was successful (WHERE: $where)."
Write-Log -Path $log -Value $Value

return $results
}
catch
{
$Value = (get-timestamp)+"`tERROR`tSQL Select`tDatabase query failed (WHERE: $where). $_"
Write-Log -Path $log -Value $Value
}
}

function SQL-Update {
<#
.EXAMPLE
SQL-Update -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -dataField $sqlDataField -dataValue $sqlDataValue -updateID $sqlDataID
#>
param(
	[string]$server,
	[string]$database,
	[string]$table,
	[string]$dataField,
	[string]$dataValue,
	[string]$updateID
)

$sqlQuery = @"
UPDATE $database.$table 
SET $dataField='$dataValue' 
WHERE id=$updateID
"@

try
{
Invoke-SQLcmd -ServerInstance $server -Database $database -Query $sqlQuery
$Value = (get-timestamp)+"`tSUCCESS`tSQL Update`tUpdated database record, ID $updateID ($dataField > $dataValue)."
Write-Log -Path $log -Value $Value
}
catch
{
$Value = (get-timestamp)+"`tERROR`tSQL Update`tUnable to update database record, ID $updateID ($dataField > $dataValue). $_"
Write-Log -Path $log -Value $Value
}
}

function Check-Status {
	$results = $NULL
	$results = SQL-Select -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -selectWhat 'id,email,pword,pwordSet,status' -where "(pwordSet IS Null OR pwordSet='') AND status='CheckedIn'"
	$results | ForEach {
		if ($_.pword.GetType().name -eq 'DBNull')
		{
			## Generate a new password for the end-user
			$password = Create-Password
			
			$sqlDataID = $_.id
			
			## Configure SQL statement to UPDATE the end-user 'pword'
			$sqlDataField = 'pword'
			$sqlDataValue = $password
			SQL-Update -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -dataField $sqlDataField -dataValue $sqlDataValue -updateID $sqlDataID
			
			## Reset the end-user's password
			Reset-Password -emailAddress $_.email -password $password
			
			## Configure SQL statement to UPDATE the end-user 'pwordSet'
			$sqlDataField = 'pwordSet'
			$sqlDataValue = 'Yes'
			SQL-Update -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -dataField $sqlDataField -dataValue $sqlDataValue -updateID $sqlDataID
			
			## Configure SQL statement to UPDATE the end-user 'samaccountname'
			$sqlDataField = 'samaccountname'
			$sqlDataValue = Get-Username -emailAddress $_.email
			SQL-Update -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -dataField $sqlDataField -dataValue $sqlDataValue -updateID $sqlDataID
		}
		elseif($_.pword -eq '')
		{
			## Generate a new password for the end-user
			$password = Create-Password
			
			$sqlDataID = $_.id
			
			## Configure SQL statement to UPDATE the end-user 'pword'
			$sqlDataField = 'pword'
			$sqlDataValue = $password
			SQL-Update -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -dataField $sqlDataField -dataValue $sqlDataValue -updateID $sqlDataID
			
			## Reset the end-user's password
			Reset-Password -emailAddress $_.email -password $password
			
			## Configure SQL statement to UPDATE the end-user 'pwordSet'
			$sqlDataField = 'pwordSet'
			$sqlDataValue = 'Yes'
			SQL-Update -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -dataField $sqlDataField -dataValue $sqlDataValue -updateID $sqlDataID
			
			## Configure SQL statement to UPDATE the end-user 'samaccountname'
			$sqlDataField = 'samaccountname'
			$sqlDataValue = Get-Username -emailAddress $_.email
			SQL-Update -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -dataField $sqlDataField -dataValue $sqlDataValue -updateID $sqlDataID
		}
		else 
		{
			Reset-Password -emailAddress $_.email -password $_.pword
			
			$sqlDataID    = $_.id
			
			## Configure SQL statement to UPDATE the end-user 'pwordSet'
			$sqlDataField = 'pwordSet'
			$sqlDataValue = 'Yes'
			SQL-Update -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -dataField $sqlDataField -dataValue $sqlDataValue -updateID $sqlDataID
			
			## Configure SQL statement to UPDATE the end-user 'samaccountname'
			$sqlDataField = 'samaccountname'
			$sqlDataValue = Get-Username -emailAddress $_.email
			SQL-Update -server $sqlServerInstance -database $sqlDatabase -table $sqlTable -dataField $sqlDataField -dataValue $sqlDataValue -updateID $sqlDataID
		}
	}
	return $results
}

##====================================================================================
## Main script begins here
##====================================================================================
Check-Status
