Encrypting Web.Config part 2

Someone noted that my previous script for encrypting web.config files was missing out some applications and I discovered it was because they where at the root level and my script expected everything to running under a virtual directory.  This was correct for the development machines but on a production machine the site could be anywhere.

Working by file

To fix this I changed the script to simply find all the web.configs on a specific drive and do it that way.  It turned out to be easier than going via IIS and probably more accurate too.  There modified script is below:

$Dir = get-childitem G:\ -recurse
$List = $Dir | where {$_.name -eq "web.config"}

## Sections we want Hashed ###
$configSections = @('connectionStrings','appSettings')
## Command line for the encrypting system ###
$CmdLine = "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -pef "

foreach($file in $List)
{
    ### Process the directories ###
    write-Host "processing  -> " $file.fullname;
    foreach($section in $configSections)
    {
        $command = $CmdLine + $section + " " + $file.fullname + "' -prov 'RsaProtectedConfigurationProvider'";
        #invoke-expression -command $command | out-null
    }

}

Encrypting Web.Config

Today I spent a few hours getting a script together that would encrypt all the web.config files for all the sites and applications on a server.   It was more difficult than expected, but I think that’s more because I don’t really know PowerShell as well as I figured.  It was also the fact that our servers are running version 1 rather than version 2 which makes things a little more complicated.

The Script

The script is given below (not much sot show for 4 hours work!). 

### Powershell script to encrypt all web.configs###
$objSites = [adsi]"IIS://localhost/W3SVC"
foreach ($objChild in $objSites.psBase.children)
{
    $sitePath=$objChild.psBase.Path + "/Root";
    Write-Host "Processing Site:" + $sitePath;
    $iis=[adsi]$sitePath;
    $vroot = $iis.psbase.children ;
    trap  { continue;}

    ## Sections we want Hashed ###
    $configSections = @('connectionStrings','appSettings')
    ## Command line for the encrypting system ###
    $CmdLine = "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -pe "
    ### Process the directories ###
    foreach($vdir in $vroot)
    {   
        Write-Host "processing  -> " $vdir.psBase.Name;
        foreach($section in $configSections)
        {
            $command = $CmdLine + $section + " -app '/" + $vdir.psBase.Name + "' -prov 'RsaProtectedConfigurationProvider'";
            invoke-expression -command $command | out-null
        }
    }
}

In essence its a simple loop, find all teh defined sites, then all the virtual directories below these.  Run teh command line utility “aspnet-regiis –pe ”, which will work it’s magic.

Powershell

One of the major things you need to have on teh server for this work is PowerShell.  This feature is built into Windows 2008 so all you need to do is enable it by adding the “Microsoft Powershell” feature.  For Windows 2003 you’ll need to download the installation package from Microsoft.

If you’ve installed PowerShell for teh first time tehn you’ll also need to set th Execution Policy.  Just enter “Powershell” at the command prompet and enter “Set-ExecutionPolicy RemoteSigned”, then exit.

 

The Timer Job

The simplest way of making sure that this is always applied to all sites is to create a timer job that runs every hour.

Mocking a View for Unit Testing

Had a very interesting question yesterday asking if it was possible to mock up a database view along side the in-memory SQLLite database we use for ActiveRecord.  Initially I figured it was a simple matter of just finding some form of attribute within the ActiveRecord class definition but it turns out that this is not the case.  Views not not actually supported by the CreateSchema() method we normally use with NHibernate/ActiveRecord.

As a work around you can create a view using the existing connection, for example I want to create a view called “mockview” which is based on the applications table.  All we need to do this is create a method:

private void CreateMockView()
{
    using (IDbCommand command = service.GetConnection().CreateCommand())
    {
        command.CommandText = "Create view mockview as select applicationid from applications";
        command.ExecuteNonQuery();
    }
}

If there is already an ActiveRecord class definition for this you need to decorate the class with the Schema=”none” parameter which will prevent the creation of a table with the same name.

[Serializable , ActiveRecord("mockview ", DynamicUpdate = true, Lazy = false, Schema="none")]
public partial class ApplicationsDAO : ActiveRecordBase    {
………………………….
}

Now you can query this to your hearts content.

If we want to query this but there is no ActiveRecord table definition we can just use a normal datareader, for example;

[Test]
public void GetApplications_Test()
{
    IList<Application> results = Application.FindAll();      // the AR Class
    Assert.IsTrue(results.Count > 0, "Returned values");
    _log.InfoFormat("GetAll Complete returned {0} records", results.Count);

    using (IDbCommand command = service.GetConnection().CreateCommand())
    {
        command.CommandText = "select applicationid from applications";  // the View on the database
        IDataReader reader = command.ExecuteReader();
        Assert.IsTrue(reader["applicationid"].ToString() == “1”, "there should be records in the view!");
    }
}

Authentication against Active Directory and ADAM

Today we were doing some work with authentication to see if we can improve the way it’s done on the external environments, Corp Website, Xtranet and the CEBs.  The plan was to use ADAM (Active Directory Application Mode) but this would mean a lot of nice features that are out-of-the-box with Active Directory.

To test both options I created a simple winform that will verify both options.

Using Active Directory

This is very well supported in the .Net Framework, you can use the built-in .Net references:
System.DirectoryServices.AccountManagement;
System.DirectoryServices;

Here is the code;

try
{ 
  // create a "principal context" - e.g. the domain (can also be a machine, too)
    using (PrincipalContext pc = new PrincipalContext(ContextType.Domain, txtDomain.Text))
    {
        // validate the credentials
        if (pc.ValidateCredentials(txtUsername.Text, txtPassword.Text))
            lblStatus.Text = "Login successful!";
        else
            lblStatus.Text = "Login unsuccessful!";
    }
}
catch (Exception ex)
{
    lblStatus.Text = ex.Message;
}

The PrincipleContext connects you to the domain, while the ValidateCredentials method will return True if its a valid name/password pair and false if not.

Using ADAM

This is not as well supported but it is there if needed.

try
{
     using (DirectoryEntry entry = new DirectoryEntry(txtPath.Text, txtUsername.Text, txtPassword.Text))
     {
         try
         {
             if (entry.Guid != null)
                 lblStatus.Text = "Login successful!";
             else
                 lblStatus.Text = "Login unsuccessful!";
         }
         catch (NullReferenceException ex)
         {
             lblStatus.Text = ex.Message;
         }
     }
}
catch (Exception ex)
{
     lblStatus.Text = ex.Message;
}

Here we create a Directory entry and connect to it using an LDAP path.  That is we tell the application where to find the Users information.  In my form I used; LDAP://localhost:389/cn=Groups,cn=XXX,cn=YYY,dc=ZZZ

The important thing here is that the address is entered in reverse order.  You enter the container for the User, then the container in which the User is located and ten any other container and so on until the top.

Someone might find it useful, but I’m happy to stick with Active Directory.

Running SQL scripts in PowerShell

I had the requirement to create a power shell script that would query a SQL database.  It turned out to be very easy indeed…

# Create SqlConnection object, define connection string, and open connection
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=Livesqlserver; Database=WebCDB; Integrated Security=true"
$con.Open()

First create the connection…

$cmdSelect = "SELECT DATEDIFF(day, update_date, getdate()) as datedifference, DATENAME(dw, update_date) as theday , count(*)as TotalMails FROM  mail_tbl where sent=1 and mail_type='XTRANET' and  DATEDIFF(day, update_date, getdate()) < 7 group by DATEDIFF(day, update_date, getdate()), DATENAME(dw, update_date) order by datedifference desc"
$da = New-Object System.Data.SqlClient.SqlDataAdapter($cmdSelect, $con)

Create the SQL you want to return values on..

$dt = New-Object System.Data.DataTable
$da.Fill($dt) | Out-Null

Fill a dataset with the results

Foreach ($row in $dt.rows)
{  Write-Host $row.theday $row.TotalMails  }

Print out the results… easy

Workflow work-around made easy

I had a typical User request today, you know the type of thing “We have a rules that says ‘Reporting is always to Managers’, but we’ve these two people people who are ‘Manager’, but report to other ‘Managers’”.  “Simple”, I said", “Just don’t call them Managers”.. “Ahh yeah..but we can’t do that!” was the reply. You know yourself usual things we get thrown every now and again.  So I figured I could do something that was a little but better than just the usual big “If” statement, so I’ve implemented the Strategy Pattern.

Before the good stuff

The code used to look like this a big if statement which did a recursive call to itself, if the person was a Manager, Divisional Manager, Director or CEO then that was fine, if not try the next person.

/// <summary>
/// Finds the next in line by post ID.
/// </summary>
/// <param name="Id">The id.</param>
/// <returns></returns>
public ReportingStructure FindNextInLineByPostID(string Id)
{
IList<ReportingStructure> records = ReportingStructure.FindAll(Expression.Eq("PostId", Id));
if (records.Count > 0)
{   // OK now we have to check if this is a department or overseas Manager by the title
    if (records[0].JobTitleDescription.Contains("Department Manager") ||
        records[0].JobTitleDescription.Contains("Overseas Manager") ||
        records[0].JobTitleDescription.Contains("Divisional") ||
        records[0].JobTitleDescription.Contains("Director") ||
        records[0].JobTitleDescription.Contains("Chief Executive Officer")
        )
        return records[0];
    // not an exec so move up the line
    return NextInLine(records[0].ReportsToPostId);
}
return null;
}

So I figured, hey I’ll just hard code in the two exceptions; but then it occurred to me that this could (and probably would) change over time.  More exceptions would be added, more code and next year it might all change again.

Getting down with Strategy

This is really easy once you get your head around it.  First we create an abstract class with an abstract method.  This will be the blue-print for the class we’ll use for our reporting strategy.

abstract class ReportingStrategy
{
    public abstract ReportingStructure NextInLine(string Id);
}

Next we create a concrete class that contains the code we want to implement.

internal class Reporting2010 : ReportingStrategy
{
    public override ReportingStructure NextInLine(string Id)
    {
        IList<ReportingStructure> records = ReportingStructure.FindAll(Expression.Eq("PostId", Id));
        if (records.Count > 0)
        {   // OK now we have to check if this is a department Manager or overseas
            if (records[0].JobTitleDescription.Contains("Department Manager") ||
                records[0].JobTitleDescription.Contains("Department Manager") ||
                records[0].JobTitleDescription.Contains("Divisional") ||
                records[0].JobTitleDescription.Contains("Director") ||
                records[0].JobTitleDescription.Contains("Chief Executive Officer")
                )
                return records[0];
            // not an exec so move up the line
            return NextInLine(records[0].ReportsToPostId);
        }
        return null;
    }
}

As you will see this is the same workflow logic as before but now it’s out on it’s own in its own class.

Finally we update the context class to make use of this new object. I’ve clipped out all the other code to make it easier to read.

    public class ReportingStructure 
    {

        #region Private Members
        ……….

        private ReportingStrategy _reportingStrategy = new Reporting2010();
        #endregion

public ReportingStructure FindNextInLineByPostID(string Id)
{
    return _reportingStrategy.NextInLine(Id);

}

   }

So what does that give us?

Well, now if I want to make a change to the 2010 workflow I can simply update a small specific class, which can also be use in other places if needed.  More importantly however I can create any number of specific new workflows and rules and just swap out the private member to point to the right one.  I can even make it public or build it into the constructer to allow dependency injection.

e.g.   ReportingStructure reporting = new ReportingStructure (new Reporting2011());

or     ReportingStructure reprting = new ReportingStructure();
        reporting.Workflow = new (ReportingWhatEverIWant();

Charting in ASP.NET and Visual Studio 2008

I was doing some VS2010 migration research over the weekend and found that the Charting options available are really good, we could finally rid ourselves of our old Dundus Charting software.  My heart sank when I came back to the other problems in moving from VS2008 to VS2010, but after a bit of searching I found that all the charting options are backwardly compatible.

Things to install

You need to install two items onto your desktop development PC, both are very simple self extractors.  It would be best to close Visual Studio before doing this, but I don’t think it would make a lot of difference.

• Microsoft Chart Controls for Microsoft .NET Framework 3.5
• Microsoft Chart Controls Add-on for Microsoft Visual Studio 2008

Following the installation you should now have a new Chart option available in your Data tool bar.

Using the new graph facilities

To create a simple graph I just created a new ASP.NET application with a single ASPX page called default. Dragging a chart from the toolbar to the design surface and switch to the source view will give you the code below.

<asp:Chart ID="Chart1" runat="server">
    <Series>
        <asp:Series Name="Series1">
        </asp:Series>
    </Series>
    <ChartAreas>
        <asp:ChartArea Name="ChartArea1">
        </asp:ChartArea>
    </ChartAreas>
</asp:Chart>

First thing we need to do is add soem values.  I could do this a code behind, but for this demo I’ll just use the page code.

<asp:Series Name="Column" BorderColor="180, 26, 59, 105" YValuesPerPoint="2">
    <points>
        <asp:DataPoint YValues="45,0" AxisLabel="Jan" />
        <asp:DataPoint YValues="34,0" AxisLabel="Feb" />
        <asp:DataPoint YValues="67,0" AxisLabel="Mar" />
        <asp:DataPoint YValues="31,0" AxisLabel="Apr" />
        <asp:DataPoint YValues="27,0" AxisLabel="May" />
        <asp:DataPoint YValues="87,0" AxisLabel="Jun" />
        <asp:DataPoint YValues="45,0" AxisLabel="Jul" />
        <asp:DataPoint YValues="32,0" AxisLabel="Aug" />
    </points>
</asp:Series>

Here I’ve added 8 random numbers and given them a label for each month.  Next we add in the chart area code.

<asp:ChartArea Name="ChartArea1" BorderColor="64, 64, 64, 64" BorderDashStyle="Solid" BackSecondaryColor="White" BackColor="64, 165, 191, 228" ShadowColor="Transparent" BackGradientStyle="TopBottom">
    <area3dstyle Rotation="10" perspective="10" Inclination="15" IsRightAngleAxes="False" wallwidth="0" IsClustered="False"></area3dstyle>
    <axisy linecolor="64, 64, 64, 64">
        <labelstyle font="Trebuchet MS, 8.25pt, style=Bold" />
        <majorgrid linecolor="64, 64, 64, 64" />
    </axisy>
    <axisx linecolor="64, 64, 64, 64">
        <labelstyle font="Trebuchet MS, 8.25pt, style=Bold" />
        <majorgrid linecolor="64, 64, 64, 64" />
    </axisx>
</asp:ChartArea>

Doing a preview will give you the following error: “Error executing child request for ChartImg.axd”

So what went wrong?  Well, as all the charts are generated on the fly, we need to make a few changes in the Web.Config.  Add the following  lines and all should be well; 

Within <system.web><httpHandlers>, add the following: <add path="ChartImg.axd" verb="GET,HEAD" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false" />

Within <system.webServer><handlers>, add the following:

<add name="ChartImageHandler" preCondition="integratedMode" verb="GET,HEAD,POST" path="ChartImg.axd" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

Now when we run the programme, we get the following:

Putting on a bit more flash on our creation

Ok that graph looks a bit dull, so we add a legend and border by adding the following code to the page;

<legends>
    <asp:Legend IsTextAutoFit="False" Name="Default" BackColor="Transparent" Font="Trebuchet MS, 8.25pt, style=Bold"></asp:Legend>
</legends>
<borderskin skinstyle="Emboss"></borderskin>

Then we change the type to “Area” by modifying the series properties;

<asp:Series Name="Column" BorderColor="180, 26, 59, 105" YValuesPerPoint="2" ChartType="Area">

Now it looks like this:

Its simple easy to use and very powerful and should be compatible with SharePoint.  It may not be Sliverlight, but it work…