Script repository
The script checks whether the new Employee ID set for a user is present in the user’s SQL database record and in other AD accounts. To execute the script, create a business rule triggering Before creating a user or Before updating a user. If either of the below conditions is met, the script will cancel the operation:
- The Employee ID property is empty.
- The Employee ID property is cleared.
- The Employee ID being set is not present in the user’s SQL database record.
- The Employee ID being set is already in use by another AD user account.
To use the script, install the Adaxes PowerShell module on the computer where the service runs.
Parameters
$databaseHost- the fully-qualified domain name or IP address of the database host.$databaseName- the database name.$tableName- the name of the database table that contains information on employee IDs.$fieldName- the name of the database field to search employee IDs in.$databaseUsername- the username for database connection. If set to$null, the connection will be established using the credentials of the Adaxes service account (specified during the software installation).$databasePassword- the password for database connection.
$databaseHost = "host.company.com" # TODO: modify me
$databaseName = "MyDatabase" # TODO: modify me
$tableName = "UsersTable" # TODO: modify me
$fieldName = "EmployeeID" # TODO: modify me
# Specify credentials for database connection.
# When set to $NULL, the credentials of the Adaxes service account will be used.
$databaseUsername = $NULL # TODO: modify me
$databasePassword = $NULL # TODO: modify me
# Get Employee ID.
$employeeIdSpecified = $Context.GetModifiedPropertyValue("employeeID")
if ($NULL -eq $employeeIdSpecified)
{
$Context.Cancel("Employee ID not specified.")
return
}
# Search for the Employee ID in the SQL database.
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($NULL -eq $databaseUsername)
{
$connectionString = $connectionString +
"Integrated Security=SSPI;"
}
else
{
$connectionString = $connectionString +
"User ID=$databaseUsername;Password=$databasePassword;"
}
try
{
$connection = New-Object "System.Data.SqlClient.SqlConnection" $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = "SELECT $fieldName FROM $tableName WHERE $fieldName = $employeeIdSpecified;"
$employeeIDFromSQL = $command.ExecuteScalar()
if ($NULL -eq $employeeIDFromSQL)
{
$Context.Cancel("You have entered an invalid Employee ID.") # TODO: modify me
return
}
# Search AD for a user with the specified Employee ID
$domainName = $Context.GetObjectDomain("%distinguishedName%")
$user = Get-AdmUser -Filter 'employeeID -eq $employeeIDFromSQL' `
-Server $domainName -AdaxesService localhost
if ($NULL -ne $user)
{
$Context.Cancel("The specified Employee ID already exists in Active Directory.") # TODO: modify me
return
}
}
finally
{
# Release resources used by the SQL query and the AD search
$command.Dispose()
$connection.Close()
}
Comments 0
You must be signed in to comment.