asked on

How to use Powershell data from SQL

Hello,
This is in regards to question asked here:
https://www.experts-exchange.com/questions/29013772/Problems-creating-account-in-AD-with-Powershell-data-from-SQL.html?anchor=a42078157¬ificationFollowed=186907313&anchorAnswerId=42078157#a42078157

If you see in the script posted, I have $otherAttributes being used for New-Aduser, however the same variable cannot be used for Set-Aduser as it doesn't support -OtherAttributes.
I want to have the possibility of making the changes to an existing user by using the new values from SQL where the Action Column has value Update.

Further I want to use the same scenario for New-MsolUser & Set-MsolUser.

Regards,
A

Jason Crawford

The post you're referencing is extremely long. Can you just rephrase the question?

Chris Dent

For the benefit of anyone joining in.

Problem:

Set-ADUser uses a different set of parameters from New-ADUser. The attribute updates need to be handled a little differently.

Summary:

The existing script performs the following actions:
- Executes a query against SQL to generate a list of updates which should be published into Active Directory
- Executes the New-ADUser command if a user should be created with a set of dynamically filled parameters
- Clears the flag from the entry in the SQL database.

Code breakdown:

SQL reader

Import-Module ActiveDirectory


# Path to create users in
$path = "OU=TestUser,OU=Test,DC=Test,DC=com"

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Select statement to return new user accounts
# Needs to return "sAMAccountName" & "Password" columns
# Note: Other columns names should match AD attribute name
$sql = "Select FirstName as GivenName,
				 LastName as sn,
				 DisplayName as DisplayName,
				 samAccountName as sAMAccountName,
				 EmailAddress as mail,
				 City as l,
				 Department as Department,
				 StreetAddress as StreetAddress,
				 State as st,
				 samAccountName+'@wuestundpartner.com' as userPrincipalName,
				 PostalCode as postalcode,
				 MobilePhone as mobile,
				 OfficePhone as telephoneNumber,
				 Department as department,
				 Title as Title,
				 Office as physicalDeliveryOfficeName,
				 c as c,
				 CountryCode as co,
				 Action as Action,					
				 'Abc-123456' as Password
from GetActiveDirectoryUsers where Action = 'update' OR Action = 'create' "

###########################

$cn = new-object system.data.sqlclient.sqlconnection
$cn.ConnectionString = $connectionString
$cn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $sql
$cmd.connection = $cn
$dr = $cmd.ExecuteReader()

Open in new window

Parameter population:

$colCount = $dr.FieldCount
$sAMAccountNameOrdinal = $dr.GetOrdinal("sAMAccountName")
$PasswordOrdinal = $dr.GetOrdinal("Password")
$DisplayNameOrdinal = $dr.GetOrdinal("DisplayName")
$ActionOrdinal = $dr.GetOrdinal("Action")

while ($dr.Read())
{
	# Get value of sAMAccountName column
	$sAMAccountName = $dr.GetValue($sAMAccountNameOrdinal)
	# Get value password column (converted to secure string for New-ADUser Cmdlet)
	$password = ConvertTo-SecureString -AsPlainText $dr.GetValue($PasswordOrdinal) -Force
	# Get value of DisplayName column
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
	$Action = $dr.GetValue($ActionOrdinal)
	
	#write-host "Creating user account..." $sAMAccountName
	
	$otherAttributes = New-Object System.Collections.HashTable
	
	# Create a hash table of attribute names and attribute values
	# Used to populate other attributes. 
	for ($i = 0; $i -le $colCount - 1; $i++) {
        if ($attribute -notin 'Password', 'SAMAccountName', 'Action') {
            $value = $dr.GetValue($i)
            if ($value -is [String]) {
                $value = $value.Trim()
            }
            if ([String]::IsNullOrEmpty("$value") -eq $false) {
                Write-Host "Name: $attribute; Value: $value $($value.GetType()) isNull? $($null -eq $value)"
                $otherAttributes.Add($attribute, $value)
            }
        }
    }
}

Open in new window

Actions:

if ($Action -eq 'create')
	{
		try
		{
			Write-Host "Create branch executed for $sAMAccountName"
			New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password -PasswordNeverExpires $true 
			#Write-Host "UserID $($DisplayName) created!"
		}
		catch
		{
			Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
			Write-Host "Error message: $($_.Exception.Message)"
		}
	}
	elseif ($Action -eq 'update')
	{
		Write-Host "Update is triggered for $sAMAccountName"
	}
}

Open in new window

Chris Dent

And what I believe might work...

elseif ($Action -eq 'update') {
    Write-Host "Update is triggered for $sAMAccountName"

    Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes
}