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!

Wednesday, May 27, 2009

IIS automation

Previous part of this post is HERE !

After having deployed the SQL database on which our Web portal is based on, we are going to deploy the web site... Using the second menu:


... We are discovering the GUI dedicated to the Web site deployment:


As the previous one, GUI is very simple. Four information are needed to deploy the Web Site:
  • Web server name,
  • Listening port,
  • Path to the Web Site source,
  • Credentials used by the application pool.
First fields are automatically filled and can be modifiable.

Let's see the PowerShell code !

First, we need to check that all specified information are correct:

### <summary>
### Checks the Web part parameters.
### </summary>
### <return>True if parameters are correct, false otherwise</return>
function Check-WebParameters()
{
    $bln = $false
        
    $bln = ($TxtWebServer.Text.Length -gt 0-and
           ($TxtWebPort.Text.Length -gt 0-and
           ($TxtWebPath.Text.Length -gt 0-and
           ($TxtWebLogin.Text.Length -gt 0-and
           ($TxtWebPassword.Text.Length -gt 0)
    if ($bln) {
        $bln = ($TxtWebServer.Text -match "^\w[\w\d-_]*$"-or
               ($TxtWebServer.Text -match "^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$")
        if ($bln -eq $false) {
            Display-error 'Specified server name is invalid'
        } else {
            $bln = ($TxtWebPort.Text -match "^\d{1,5}$")
            if ($bln -eq $false) {
                Display-error 'Specified port is invalid'
            } else {
                $bln = Test-Path $TxtWebPath.Text
                if ($bln -eq $false) {
                    Display-error 'Specified Web folder is invalid'
                } else {
                    $strLogin = $TxtWebLogin.Text
                    if ($strLogin.Contains('\')) {
                        $strLogin = $strLogin.Split('\')[1]
                    }
                    $objUser = SearchOne-InDomain '' "(samaccountname=$strLogin)"
                    if ($objUser -eq $null) {
                        Display-error 'Specified user doesn''t exist in the domain'
                        $bln = $false
                    }
                }
            }
        }
    } else {
        Display-error 'All fields are required'
    }
    
    return $bln
}

Check-WebParameters function checks:
  • Web server name or IP address using a regular expression,
  • Listening port (a number up to 5 digits),
  • Existence of the user account used by the application pool. SearchOne-InDomain function makes the job:

    ### <summary>
    ### Searchs one object in an Active Directory container.
    ### </summary>
    ### <param name ="domainName">The container</param>
    ### <param name ="filter">LDAP filter</param>
    ### <param name ="scope">The scope for the research (optional)</param>
    ### <returns>Active Directory object</returns>
    function SearchOne-InDomain($container, $filter,
                                $scope = [DirectoryServices.SearchScope]::Subtree)
    {
        trap { return $null }
        $entry = New-Object DirectoryServices.DirectoryEntry("$container")
        $searcher = New-Object DirectoryServices.DirectorySearcher($entry)

        $searcher.Filter = $filter
        $searcher.SearchScope = $scope
        $searcher.FindOne()
    }

    We have already seen all this stuff !
Well, after having checked all information we can deploy our Web Site !

### <summary>
### Installs the IIS website.
### </summary>
function Deploy-WebServer()
{
    $bln = Check-WebParameters
    
    if ($bln -eq $false) { return }
    $bln = Copy-Source
    if ($bln -eq $false) { return }
    $LblError.Text = ''
    $strServer = $TxtWebServer.Text
    $strPath = $TxtWebPath.Text
    $strWebSiteName = 'Provisioning'
    $objLocator = New-Object -com WbemScripting.SWbemLocator
    $objProvider = $objLocator.ConnectServer($strServer, 'root/MicrosoftIISv2')
    $objService = $objProvider.Get("IIsWebService='W3SVC'")
    $objBindings = @($objProvider.Get('ServerBinding').SpawnInstance_())
    $objBindings[0].Properties_.Item('Port').value = $TxtWebPort.Text
    $createNewSiteMethod = $objService.Methods_.Item('CreateNewSite')

    $objInParameters = $createNewSiteMethod.InParameters.SpawnInstance_()
    $objInParameters.Properties_.Item('PathOfRootVirtualDir').value = $strPath
    $objInParameters.Properties_.Item('ServerBindings').value = $objBindings
    $objInParameters.Properties_.Item('ServerComment').value = $strWebSiteName
    
    Display-Info "Creating new WebSite '$strWebSiteName'..."
    $objOutParameters = $objService.ExecMethod_("CreateNewSite", $objInParameters)
    Display-Info "WebSite '$strWebSiteName' created"
    $id = ''
    $objOutParameters.properties_ | % {
        $id = $_.Value -match "[^']'([^']+)'.*"
        if ($id) { $id = $matches[1] }
    }
    if ($id.ToUpper() -match "^W3SVC/\d+$") {
        Display-Info "Creating new Application Pool '$strWebSiteName'..."
        $bln = Create-ApplicationPool $strServer $strWebSiteName
        if ($bln) {
            Display-Info "Configuring Website '$strWebSiteName'"
            $objSite = [ADSI]"IIS://$strServer/$id/Root"
            $objSite.Put("DefaultDoc", "Default.aspx")
            $objSite.Put("AppPoolId", $strWebSiteName)
            $objsite.put("AuthFlags", 4)
            $objsite.Put("AppFriendlyName", $strWebSiteName)
            $objsite.Put("AccessFlags", 1)
            $objsite.Put("AccessRead", $true)
            $objsite.Put("AccessScript", $true)
            $objsite.Put("AccessExecute", $true)
            $objSite.SetInfo()
            if ((Get-ChildItem env:COMPUTERNAME).Value -eq $strServer) {
                Set-FrameWorkVersion $id
                Display-Info "WebSite '$strWebSiteName' successfully created"
            } else {
                Display-Info "WebSite '$strWebSiteName' successfully created. " +
                             "You need to manually set up the .Net 2 version"
            }
        } else {
            Display-Error "Error creating Application pool '$strWebSiteName'"
            Display-Info "WebSite '$strWebSiteName' has not been configured"
        }
    } else {
        Display-Error "Invalid WebSite ID ($id)"
        Display-Info "WebSite '$strWebSiteName' has not been configured"
    }
}

Deploy-WebSite function makes the job!

At the begining of the function, we used Copy-Source: it's a small function called to copy Web Site source files to the Web server destination folder. This function uses Copy-Item Cmdlet (nothing special!).

Then, we use ADSI to configure IIS! So, we can use this function to deploy a Web Site using IIS 6 and later version!

The function makes the following operations:
  • Web Site creation using a specific name and listening port,
  • Application pool creation using Create-ApplicationPool function:

    ### <summary>
    ### Creates the IIS application pool.
    ### </summary>
    function Create-ApplicationPool($strServer, $strAppPoolName)
    {
        trap [Exception] {
            Display-Info $_.Exception.Message
            $bln = $false
            continue
        }
        $bln = $false
        $objApp = [ADSI]"IIS://$strServer/W3SVC/AppPools/$strAppPoolName"
        if ($objApp.distinguishedname -eq $null) {
            $strDomain = ([ADSI]'').Name.Value
            $objApp = [ADSI]"IIS://$strServer/W3SVC/AppPools"
            $objPool = $objapp.Create("IIsApplicationPool", $strAppPoolName)
            $objPool.Put('AppPoolIdentityType', 3)
            $strLogin = $TxtWebLogin.Text
            if ($strLogin.Contains('\'-eq $false) {
                $TxtWebLogin.Text = "$strDomain\$strLogin"
                $TxtWebLogin.Refresh()
            } else {
                $strLogin = $strLogin.Split('\')[1]
            }
            Display-Info "Setting Application pool credentials to $strLogin..."
            $objPool.Put('WAMUserName', $TxtWebLogin.Text)
            $objPool.Put('WAMUserPass', $TxtWebPassword.Text)
            $objPool.SetInfo()
            Display-Info "Adding user $strLogin to group IIS_WPG..."
            $objGroup = [ADSI]"WinNT://$strServer/IIS_WPG"
            $objGroup.Add("WinNT://$strDomain/$strLogin")
            $bln = $true
        } else {
            Display-Info "Application pool '$strAppPoolName' already exists."
            $bln = $true
        }
        
        return $bln
    }

    This function also adds the specified user account used by the application pool to the local group IIS_WPG
  • Web Site configuration:
    • Dafault web page configured to "Default.aspx"
    • Application pool assignment
    • Windows authentication configuration
    • Permisssions configuration: Read and Execute permissions
  • .Net Framework version configuration. This step is available only if the machine running our Solution Accelerator is our Web server. Set-FrameworkVersion function configures the Web Site to use the version 2 of the .NET framework:

    ### <summary>
    ### Sets the .NET framework version for a given website.
    ### </summary>
    ### <param name="strID">Website ID</param>
    function Set-FrameWorkVersion($strID)
    {
        $strPath = (Get-ChildItem Env:windir).Value + '\Microsoft.NET\Framework\'
        
        $objDir = Get-ChildItem $strPath | where {
                   ($_.GetType().ToString() -eq 'system.io.directoryinfo'-And
                   ($_.name -match "^(v2.[\d\.]+)$")} | sort name
        if ($objDir -ne $null) {
            $strPath += $matches[1]
            $strPath += "\aspnet_regiis.exe"
            $objSI = New-Object System.Diagnostics.ProcessStartInfo
            $objSI.UseShellExecute = $false
            $objSI.FileName = $strPath
            $objSI.Arguments = "-s $strID/Root"
            $objSI.RedirectStandardOutput = $true
            $objP = [System.Diagnostics.Process]::Start($objSI)
            $objP.WaitForExit()
        }
    }

    aspnet_regiis.exe binary is executed here!
All warnings or errors are displayed at the bottom of the window!

Well, IIS automation is simple! I hope that was not boring :)

Monday, May 25, 2009

SQL database deployment automation

Previous part is HERE.

Before deploying Web Portal, we have to set up the database: Which web portal haven't a database... ? :)

So, that's the first job of our Solution Accelerator. In the previous post, you have seen the graphical user interface: let's use it !

So, just click on the first menu to deploy database !

A new dedicated screen is displayed:

It is a very simple GUI!
SQL server name and instance name are the only require fields and all we need is to click on the OK button...

However, there is one thing we can do using this GUI: configuring the user account which will be use to deploy the database. This can be done on the top of the window: we can choose if you want to use windows authentication (current logged user) or an other account.

At the bottom of the window, there is an empty space... So, we won't just keep this space empty! In fact, this area is dedicated to log: all information, warnings, errors will be display here.

Each step, each traitment of the script is described and displayed in the log area. That's the Display-Info and Display-Error functions job:

### <summary>
### Adds a log.
### </summary>
### <param name="str">Message to add</param>
### <param name="strType">Message type</param>
function Log-Message($str, $strType)
{
    $strDate = [DateTime]::Now.ToString('G')
    $strType = $strType.ToUpper()
    $strLine = "[$strDate] $strType: $str"
    Write-Verbose $strLine
    $script:strLog += $strLine + "`r`n"
}

### <summary>
### Displays error message.
### </summary>
### <param name="str">Message to display</param>
function Display-Error($str)
{
    $LblInfo.Text = ''
    $LblError.Text = $str
    $LblError.Refresh()
    Log-Message $str 'Error'
}

### <summary>
### Display information message.
### </summary>
### <param name="str">Message to display</param>
function Display-Info($str)
{
    $LblInfo.Text = $str
    $LblInfo.Refresh()
    Log-Message $str 'Info'
}

All theses displayed messages are logged thanks to Log-Message function which adds a date to the message.
Well, logging is very helpful to debug !

Since we saw the GUI and we have all prerequires we need: SQL scripts which will be executed, let's see the most interesting part: How to deploy the database using PowerShell!

First, we have to check that we can access to the SQL scripts.
We declare some variables in the script scope:

### <summary>
### Path to current script.
### </summary>
$script:strPath = $MyInvocation.MyCommand.Path

### <summary>
### SQL scripts to execute during installation step.
### Order is essential.
### </summary>
$script:arrSQLGenerateDBScripts = 'CreateDatabase.sql',
                                  'ProvisioningDatabase.sql'
### <summary>
### Installation logs.
### </summary>
$script:strLog = ''

We used the $MyInvocation PowerShell builtin variable to get our last executed command line: usefull to get the full path where the script is located!

arrSQLGenerateDBScripts variable contains an ordered list of SQL scripts to execute.

Then, strLog variable is a simple string which is our log.

The first thing to do is to check the SQL scripts access:

### <summary>
### Checks SQL files.
### </summary>
### <return>True on success, false otherwise</return>
function Check-SQLFiles()
{
    trap {
        $blnRes = $false
        continue
    }
    $tab = $script:strPath.Split('\')
    $strPath = ''
    for ($i = 0$i -lt $tab.length - 2$i++) {
        $strPath += $tab[$i+ '\'
    }
    $blnRes = $true
    $bln = $true
    Display-Info "Checking files in $strPath..."
    if ($script:arrSQLGenerateDBScripts.Length -gt 0) {
        foreach ($strFile in $script:arrSQLGenerateDBScripts) {
            $bln = Test-Path "$strPath\Database\$strFile"
            if ($bln -eq $false) { break }
        }
        $blnRes = $blnRes -and $bln
    }
    if ($blnRes) {
        Display-Info "Installation files successfully checked"
    } else {
        Display-Error "Installation files missing."
    }
    
    return $blnRes
}

This function checks that scripts' names stored in the script variable exist and are located in the ../Database folder.

Now, it's time to execute the SQL scripts!
We have to connect to the SQL database using a connection string:

### <summary>
### Generate the SQL connection string.
### </summary>
### <param name="strDB">Database name</param>
### <return>SQL connection string</return>
function Generate-StringConnection($strDB)
{
    $strInstance = $TxtDBServer.Text
    if ($TxtDBInstance.Text.Length -gt 0) {
        $strInstance += '\' + $TxtDBInstance.Text
    }
    $strInstance = $strInstance.ToUpper()
    $objBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
    $objBuilder['Application Name'= 'Web Portal'
    $objBuilder['Data Source'= $strInstance
    $objBuilder['Initial Catalog'= $strDB

    if ($ChkWindowsAuth.Checked -eq $false) {
        $objBuilder['User Id'= $TxtLogin.Text
        $objBuilder['Password'= $TxtPassword.Text
    } else {
        $objBuilder['Integrated Security'= $true
    }

    return $objBuilder.ConnectionString
}

This function needs a database's name which will be used by the "Initial Catalog" parameter of the connection string.
If no SQL instance's name is provided, the server's name is used (default instance).

Then, we can write the function to execute the SQL scripts:

### <summary>
### Executes a SQL script.
### </summary>
### <param name="strScript">Script full name</param>
### <param name="objSQL">SQL connection</param>
### <return>True on success, false otherwise</return>
function Execute-SQLScript($objSQL, $strScript)
{
    trap {
        Display-Error $_.Exception.Message
        $bln = $false
        continue
    }
    $bln = Test-Path $strScript
    if ($bln) {
        Display-Info "Executing script $strScript..."
        $objSQLCmd = New-Object System.Data.SqlClient.SqlCommand
        $objSQLCmd.Connection = $objSQL
        $strContent = ''
        foreach ($strLine in Get-Content $strScript) {
            if ($bln -eq $false) { return $bln }
            if ($strLine.ToUpper() -match "^\s*GO\s*$") {
                $objSQLCmd.CommandText = $strContent
                $objSQLCmd.ExecuteNonQuery() | Out-Null
                $strContent = ''
            } else {
                $strContent += $strLine + "`r`n"
            }
        }
    } else {
        $LblError.Text = "File $strScript not found"
    }
    
    return $bln
}

There are more than one method to execute a SQL script. Here, we read the SQL script file line per line and execute the SQL request if we find the "GO" instruction.

Oh ! That's a chance to explain some regular expression basis !

"^\s*GO\s*$" string means that we search a string containing a GO surrounded by spaces (or not)!

Because it is relevant to know if the function has successfully executed the script, it returns a boolean :)

Well, at this point we have all elements to deploy the database:

### <summary>
### Installs the SQL database.
### </summary>
function Install-Database()
{
    trap {
        Display-Error $_.Exception.Message
        if (($objSQL -ne $null-and
            ($objSQL.State -eq [Data.ConnectionState]::Open)) {
            $objSQL.Close()
        }
        $bln = $false
        continue
    }
    $LblError.Text = ''
    $objSQL = New-Object System.Data.SqlClient.SqlConnection
    $objSQL.ConnectionString = Generate-StringConnection 'master'
    Display-Info "Opening database connection..."
    $objSQL.Open()
    if ($objSQL.State -eq [Data.ConnectionState]::Open) {
        $strPath = Get-SQLScriptPath
        foreach ($strScript in $script:arrSQLGenerateDBScripts) {
            $bln = Execute-SQLScript $objSQL "$strPath\$strScript"
            if ($bln -eq $false) { break }
        }
        $objSQL.Close()
        Display-Info "Database connection closed"
        if ($bln) {
            Display-Info "Database successfully installed"
        } else {
            Display-Error "Error executing script $strPath\$strScript."
            Display-Info "Database installation aborted"
        }
    }
}

This function uses the previously detailed functions and deploy the database in few steps:
  • Connection string creation
  • Database connection opening
  • SQL script execution
  • Database connection closing
All theses steps are described in the log area of the window.

Next step is to deploy our Web Portal: IIS Automation !
See you soon.

Friday, May 15, 2009

Web portal deployment automation

Web Portal deployment is not really easy. You have to check all prerequires, deploy source, check permissions, configure, and more...!

Here, just one simple thing will be done : Deploy a web portal using a single button : a pretty one !

So, I propose to make a kind of Solution Accelerator !

Let's see the graphical user interface (GUI)...



GUI is very simple, that's the goal ! It brings to light differents steps which will be detailed in the following posts:
  • SQL database deployment (SQL script execution)
  • Web portal deployment (IIS automation)
  • Web portal configuration (Acive Directory, universe... interaction)
So, the only thing to do rigth now is to launch our PowerShell session!

See you soon for the first step!

Thursday, May 14, 2009

PowerShell 2 & OpenXML: Generate a server form

That's it, now we can generate a server form in 4 steps :
  • Part 1 : Prerequires installation and configuration.
  • Part 2 : PowerShell module creation collecting software and hardware data from a remote machine.
  • Part 3 : Word 2007 template creation.
  • Part 4 : Server form generation.
See you soon for the next article !