Friday, May 29, 2009

Active Directory & SQL: Web Portal configuration

Previous part of this post is HERE!

The last part of the Web Portal deployment is the configuration!

Of course, configuration depends on Web Portal itself and the integration environment. This post is about to share some powreshell tips around Active Directory and SQL which are the common technologies used for a Web Portal.

Here, our Web Portal needs a dedicated organizationnal unit (OU) in our Active Directory to store groups used to manage permissions.

First, we need to create the OU:

### <summary>
### Creates Web portal prerequires in Active Directory.
### </summary>
### <return>True on success, false otherwise</return>
function Configure-ADWebPortal()
{
    trap {
        Display-Info $_.Exception.Message
        $bln = $false
        continue
    }
    $bln = $true
    $CONST_GLOBAL_GRP = 2
    $CONST_UNIV_GRP = 8
    Display-Info 'Creating WebPortal Organizational Unit...'
    $objOU = [ADSI]''
    $objOU = $objOU.Create('OrganizationalUnit', 'OU=WebPortal')
    $objOU.SetInfo()
    
    Create-Group $objOU 'AdmWebPortal' 'WebPortal Administrators' $CONST_UNIV_GRP
    
    return $bln
}

Configure-ADWebPortal function creates an OU and a group in the new container.

The function uses an other one to create the Active Directory group. You can find the source code here:

### <summary>
### Creates an Active Directory group.
### </summary>
### <param name ="objOU">The container (ADSI object)</param>
### <param name ="strName">Group's name</param>
### <param name ="strDescription">Group's description</param>
### <param name ="intGroupType">Group's type</param>
function Create-Group($objOU, $strName, $strDescription, $intGroupType)
{
    trap {
        Display-Error $_.Exception.Message
        continue
    }
    Display-Info "Creating group $strName..."
    $objGroup = $objOU.Create('Group', "CN=$strName")
    $objGroup.Put('SamAccountName', "$strName")
    $objGroup.Put('Description', "$strDescription")
    $objGroup.Put('GroupType', $intGroupType)
    $objGroup.SetInfo()
}

To log errors and warnings, we call our dedicated functions.

In the example, the created group type is Universal. Why ? Well, maybe it will be use in a couple of days, maybe it will be a distribution group used by Exchange 2007... :)

That's fine, we successfully created Active Directory objects! And now ?

We will change on the fly the Active Directory and SQL database! In fact, the next step is to write a function to:
  • Get all OU with a specific name
  • Create a group in theses OU
  • Insert the OU name in the database calling a stored procedure!
### <summary>
### Synchronizes SQL database and Active Directory.
### </summary>
### <param name="objSQL">SQL connection object</param>
### <return>True on success, false otherwise</return>
function Synchronize-ADAndSQL($objSQL)
{
    trap {
        Display-Error $_.Exception.Message
        $bln = $false
        continue
    }
    $CST_GLB_GRP = 2
    $bln = $true
    $objEntry = [ADSI]''
    Clean-Database $objSQL
    foreach ($obj in $objEntry.psbase.Children) {
        if ($obj.Name.Value -match "^(\w+\d+)_(\w+)$") {
            $strFullName = $matches[0]
            $strCode = $matches[1]
            $strName = $matches[2]
            $strDN = $obj.psbase.Properties.DistinguishedName.Value
            Display-Info "OU $strFullName discovered"
            $strGroupName = "$strCode" + '_Administrators_WebPortal'
            Create-Group $obj "$strGroupName" "Admins for $strCode" $CST_GLB_GRP

            $objAdapter = New-Object Data.SqlClient.SqlDataAdapter
            $objAdapter.SelectCommand = New-Object Data.SqlClient.SqlCommand
            $objAdapter.SelectCommand.Connection = $objSQL
            $objAdapter.SelectCommand.CommandText = '[PR_ADDOU]'
            $objAdapter.SelectCommand.CommandType =
                [Data.CommandType]::StoredProcedure

            $objSqlParam = Build-SQLParam '@OUCode' $strCode
            [void]$objAdapter.SelectCommand.Parameters.Add($objSqlParam)
            $objSqlParam = Build-SQLParam '@OUName' $strName
            [void]$objAdapter.SelectCommand.Parameters.Add($objSqlParam)
            $objSqlParam = Build-SQLParam '@OUDN' $strDN

            $objAdapter.SelectCommand.ExecuteNonQuery() | Out-Null
        }
    }
    
    return $bln
}

OU name is divided in 2 parts: a code and the OU name joined by the '_' character. The stored procedure named PR_ADDOU has 2 parameters for theses 2 parts of the OU name. Executing a stored procedure is a good deal to separate PowerShell script and SQL traitment!

This solution Accelerator is just an example but we have seen a practical case of information system automation!

No comments:

Post a Comment