Ignite 2015 session Using Desired State Configuration to Deploy SQL

3- Create AD Accounts.ps1

# Active Directory Module for PowerShell feature needs to be installed
# spAccounts.csv can be downloaded from: http://1drv.ms/1bHTZC0

Import-Module ActiveDirectory -EA 0

# Create AD Users and Groups

#region Parameters

$file = "C:\DevOps\Microsoft\SQL Server 2014\config\spAccounts.csv"
$users = Import-Csv -Path $file

$serverName = $env:logonserver
$serverName = $serverName.Replace("\","")
$domainName = $env:USERDNSDOMAIN
$dmain= $domainName.split(".")
$path = "CN=Managed Service Accounts, DC=" + $dmain[0] +", DC=" + $dmain[1]
$DC = "LDAP://" + $serverName + "/" + $path

#endregion

function password {
    #Set up random number generator
    $newPassword = $null
    $rand = New-Object System.Random
    #Generate a new 18 character $newPassword
    1..18 | ForEach { $newPassword = $newPassword + [char]$rand.next(40,127) }
    return $newPassword
}

function encodePassword ($pw) {
    $pw = ConvertTo-SecureString -String $pw -AsPlainText -Force
    return $pw
}

function validateUser ($userName) {
    [bool]$status = ([adsisearcher]"samaccountname=$userName").FindOne()  
    return $status
}

#region Create User

foreach ($user in $users) { 
    if (($user.CN -ne "") -and ($user.Create -eq $true)) {
        $sam = $user.sAMAccountName
        $validUser = validateUser($user.sAMAccountName)
        if ($validUser -ne $true) {
            Write-Host("Creating $sam...")
            $CN = $user.CN
            $pw = password
            $user.Password = $pw
            New-ADUser `
                -AccountPassword (ConvertTo-SecureString $pw -AsPlainText -Force) `
                -ChangePasswordAtLogon $false `
                -DisplayName $CN `
                -Enabled $true `
                -Name $CN `
                -SamAccountName $sam `
                -Path $path `
                -Description $CN
            $encPassword = encodePassword($pw) 
            $user.encPassword = $encPassword | ConvertFrom-SecureString
        }
        ELSE {
            Write-Host($sam + " is already in AD...")
        }
    }         
}

#endregion

#region Create SP Admin Group

    $sgName = ($users | Where-Object {($_.adGroup -eq "SharePoint Administrators Group Name") -and ($_.Create -eq $true)}).sAMAccountName 
    $exists = $null
    TRY {
        $exists = Get-ADGroup $sgName 
    }
    CATCH {
        Write-Host("Creating " + $sgName + " in AD...")
        New-ADGroup -Name $sgName -Path $path -GroupScope Global 
    }
    If ($exists) {
        Write-Host($sgName + " is already in AD...")
    }
#endregion

#region Validate Group Members
$sgUsers = ($users | Where-Object {($_.adGroup -eq "SharePoint Administrators Group Member") -and ($_.adGroupName -eq $sgName)}).sAMAccountName
$adGroupMembers = $null
$adGroupMembers = Get-ADGroupMember $sgName
if ($adGroupMembers -eq $null) {
    foreach ($sgUser in $sgUsers) {
        Add-ADGroupMember $sgName $sgUser
        Write-Host("Added $sgUser to $sgName Security Group...")
    }
}
ELSE {
    foreach ($sgUser in $sgUsers) {
        foreach ($adGroupMember in $adGroupMembers) {
            if ($sgUser.ToLower() -eq $adGroupMember.SamAccountName.ToLower()) {
                Write-Host($sgUser + " already exists in " + $sgName + "...")
            }
            ELSE {
                Add-ADGroupMember $sgName $sgUser
                Write-Host("Added $sgUser to $sgName Security Group...")
            }
        }
    }
}
#endregion

# Output Table
$users | Export-CSV $file -Force