Check whether Employee ID is present in SQL Database

This PowerShell script checks whether an Employee ID specified for a new user is present in the user's record from an SQL HR database.

To use the script, you need to create a business rule triggered automatically on user creation. For more details, see Validate/Modify User Input Using a Script.

Parameters:

$databaseHost - Specifies the fully qualified domain name or IP address of the database host.
$databaseName - Specifies the database name.
$tableName - Specifies the name of the database table that contains information on employee IDs.
$fieldName - Specifies the name of the database field to search employee IDs in.
$databaseUsername - Specifies the username to be used to connect to the database.
$databasePassword - Specifies the database password.

To use credentials of the Adaxes service account when connecting to the database, set both $databaseUsername and $databasePassword to $NULL.

Note: The script uses cmdlets from Adaxes PowerShell module for Active Directory. To run the script, you need to install the PowerShell Module for Active Directory component of Adaxes.

Edit Remove

PowerShell

Import-Module Adaxes
$databaseHost = "host.company.com" #TODO: modify me
$databaseName = "MyDatabase" #TODO: modify me
$tableName = "UsersTable" #TODO: modify me
$fieldName = "EmployeeID" #TODO: modify me

$databaseUsername = $NULL #TODO: modify me
$databasePassword = $NULL #TODO: modify me

$employeeIdSpecified = $Context.GetModifiedPropertyValue("employeeID")
if ($employeeIdSpecified -eq $NULL)
{
    $Context.Cancel("Employee ID not specified.") # TODO: modify me
    return
}

# Search for the Employee ID in the SQL database
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($databaseUsername -eq $NULL)
{
    $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 ($employeeIDFromSQL -eq $NULL)
    {
        $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 ($user -ne $NULL)
    {
        $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()
}

Script repository

Parameters:

Got questions?