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+'@test.com' as userPrincipalName,
PostalCode as postalcode,
MobilePhone as mobile,
OfficePhone as telephoneNumber,
Department as department,
Title as Title,
Office as physicalDeliveryOfficeName,
Country as co,
'Abc-123456' as Password
from GetActiveDirectoryUsers where Action = 'yes' "
###########################
$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()
$colCount = $dr.FieldCount
$sAMAccountNameOrdinal = $dr.GetOrdinal("sAMAccountName")
$PasswordOrdinal = $dr.GetOrdinal("Password")
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
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++)
{
$attribute = $dr.GetName($i)
switch ($attribute)
{
"Password"{ } #Ignore
"SAMAccountName" { } #Ignore
default
{
$otherAttributes.Add($attribute, $dr.GetValue($i))
}
}
}
# Create Active Directory User Account
New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password
}
$dr.Close()
$cn.Close()
ASKER
ASKER
ASKER
New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password -Country $Country
That means you'll have to create a variable for Country as well.
ASKER
<#
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+'@test.com' as userPrincipalName,
PostalCode as postalcode,
MobilePhone as mobile,
OfficePhone as telephoneNumber,
Department as department,
Title as Title,
Office as physicalDeliveryOfficeName,
Country as Country,
'Abc-123456' as Password
from GetActiveDirectoryUsers where Action = 'yes' "
###########################
$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()
$colCount = $dr.FieldCount
$sAMAccountNameOrdinal = $dr.GetOrdinal("sAMAccountName")
$PasswordOrdinal = $dr.GetOrdinal("Password")
$DisplayNameOrdinal = $dr.GetOrdinal("DisplayName")
$CountryOrdinal = $dr.GetOrdinal("Country")
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
$DisplayName = $dr.GetValue($DisplayNameOrdinal)
$Country = $dr.GetValue($Country)
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++)
{
$attribute = $dr.GetName($i)
switch ($attribute)
{
"Password"{ } #Ignore
"SAMAccountName" { } #Ignore
default
{
$otherAttributes.Add($attribute, $dr.GetValue($i))
}
}
}
# Create Active Directory User Account
try
{
New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password -ChangePasswordAtLogon $true -Country $Country
Write-Host "UserID $($DisplayName) created!"
}
catch
{
Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
}
}
$dr.Close()
$cn.Close()
Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
Write-Host "Error message: $($_.Exception.Message)"
ASKER
switch ($attribute)
{
"Password"{ } #Ignore
"SAMAccountName" { } #Ignore
"Country" { } # ignore
default
{
$otherAttributes.Add($attribute, $dr.GetValue($i))
}
}
You could change that part to:if ($attribute -notin 'Password', 'SamAccountName', 'Country') {
$otherAttributes.Add($attribute, $dr.GetValue($i))
}
ASKER
ASKER
ASKER
ASKER
ASKER
ASKER
ASKER
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+'@test.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,
'Abc-123456' as Password
from GetActiveDirectoryUsers where Action = 'yes' "
#Country as co,
###########################
$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()
$colCount = $dr.FieldCount
$sAMAccountNameOrdinal = $dr.GetOrdinal("sAMAccountName")
$PasswordOrdinal = $dr.GetOrdinal("Password")
$DisplayNameOrdinal = $dr.GetOrdinal("DisplayName")
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
$DisplayName = $dr.GetValue($DisplayNameOrdinal)
# $Country = $dr.GetValue($Country)
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++)
{
$attribute = $dr.GetName($i)
switch ($attribute)
{
"Password"{ } #Ignore
"SAMAccountName" { } #Ignore
default
{
$otherAttributes.Add($attribute, $dr.GetValue($i))
}
}
}
# Create Active Directory User Account
try
{
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)"
}
$updateqry = "update dbo.GetActiveDirectoryUsers set Action = ' ' where Action = 'Yes';"
$cmd.CommandText = $updateqry
$dr = $cmd.ExecuteNonQuery()
}
$dr.Close()
$cn.Close()
ASKER
ASKER
Active Directory (AD) is a Microsoft brand for identity-related capabilities. In the on-premises world, Windows Server AD provides a set of identity capabilities and services, and is hugely popular (88% of Fortune 1000 and 95% of enterprises use AD). This topic includes all things Active Directory including DNS, Group Policy, DFS, troubleshooting, ADFS, and all other topics under the Microsoft AD and identity umbrella.
TRUSTED BY
For Country, you'd have to debug it to see what value is being passed for that attribute.