Script Repository


Export user information to SQL database

January 23, 2020
1717

The script exports properties of a user to an SQL database. It can be used, for example, to export properties of new users to an external HR database once they are created in AD. To export properties of new users, create a Business Rule that runs the script automatically after creating a new user.

Parameters:

  • $databaseHost - specifies the DNS host name of the computer where the SQL database is homed;
  • $databaseName - specifies the database name;
  • $tableName - specifies the name of the table that holds user account information;
  • $userNameField - specifies the name of the database fields that is used to store usernames;
  • $databaseUsername - specifies the username of the user account that will be used to connect to the database. Specify $NULL to use the credentials of the Adaxes service account.
  • $databasePassword - specifies the password that will be used to connect to the database. Specify $NULL to use the credentials of the Adaxes service account.
Edit Remove
PowerShell
$databaseHost = "host.company.com" # TODO: modify me
$databaseName = "My Database" # TODO: modify me
$tableName = "My Table" # TODO: modify me
$userNameField = "User ID"

$databaseUsername = $NULL # TODO: modify me
$databasePassword = $NULL # TODO: modify me
# If set to $NULL, the credentials of the Adaxes service
# account will be used to connect to the database

# Connect to the 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()
    
    # Check whether the user already exists in the database
    try
    {
        $command = $connection.CreateCommand()
        $command.CommandText = "SELECT UserID FROM $tableName WHERE $tableName`.$userNameField = @Username"
        $command.Parameters.Add("@Username", "%username%") | Out-Null;
        $userFromDb = $command.ExecuteScalar()
    }
    finally
    {
        # Close the search and release resources
        $command.Dispose()
    }
    
    if ($userFromDb -ne $NULL)
    {
        $Context.LogMessage("The user has already been exported to the database", "Warning") # TODO: modify me
        return # Exit script
    }
    
    try
    {
        $command = $connection.CreateCommand()
        
        # Get domain name
        $domainName = $Context.GetObjectDomain("%distinguishedName%")
        
        # Get Extension
        $telephoneNumber = "%telephoneNumber%"
        if (!([System.String]::IsNullOrEmpty($telephoneNumber)))
        {
            $extension = $telephoneNumber.SubString($telephoneNumber.Length - 4)
        }
        else
        {
            $extension = [System.String]::Empty
        }
        
        # Create INSERT command to insert the data into the database
        # TODO: Modify me
        $command.CommandText = "
            INSERT INTO $tableName (UserID, LastName, FirstName, IsActive, DateCreated, Email, WorkPhone, Extension, EmployeeCode, JobTitle, ADDomain) 
            VALUES(@Username, @sn, @givenName, @isActive, @whenCreated, @mail, @telephoneNumber, @extension, @employeeID, @title, @domainName)"
        $command.Parameters.Add("@Username", "%username%") | Out-Null;
        $command.Parameters.Add("@sn", "%sn%") | Out-Null;
        $command.Parameters.Add("@givenName", "%givenName%") | Out-Null;
        $command.Parameters.Add("@isActive", (!$Context.TargetObject.AccountDisabled).ToString()) | Out-Null;
        $command.Parameters.Add("@whenCreated", "%whenCreated%") | Out-Null;
        $command.Parameters.Add("@mail", "%mail%") | Out-Null;
        $command.Parameters.Add("@telephoneNumber", "%telephoneNumber%") | Out-Null;
        $command.Parameters.Add("@extension", $extension) | Out-Null;
        $command.Parameters.Add("@employeeID", "%employeeID%") | Out-Null;
        $command.Parameters.Add("@title", "%title%") | Out-Null;
        $command.Parameters.Add("@domainName", $domainName) | Out-Null;

        # Insert the user account information into the database
        $command.ExecuteScalar()
    }
    finally
    {
        # Close the database connection and release resources
        $command.Dispose()
    }
}
finally
{
    $connection.Close()
}

Comments ( 0 )
No results found.
Leave a comment