Notes on installing and configuring Windows Software Update Service under offline model for isolated network

Executive summary

Due to strict requirements of security for finance businesses, a finance company’s network should be isolated from outside environment. This has the effect of shielding making the entire software infrastructure less prone to software invulnerabilities and thus external attacks. However, this does not eliminate the need for software updates since the network’s operation can still be disrupted by internal sabotage or worm infection from careless users.

Since large financial institutions often have management workflows, updates are subject to prior approval by the management and not all updates maybe approved. The process of maintaining software updates manually, which includes downloading, tracking, approving and installing’s cost could escalate as vulnerabilities are discovered over time and software enhancements are made.

As a major software manufacturer, Microsoft is aware of this issue and have developed Windows Software Update Service (WSUS) as a solution for the update management problem. WSUS allows for approval, mass install, provides a central configuration point and thus reduces the maintenance cost.

This document tried to summarize the knowledge needed for installing and applying WSUS’s update model to an isolated network. This includes:

1. The requirements and how to install WSUS under the isolated model.

2. Configuring WSUS and the approval workflow with WSUS.

3. Synchronize update databases between computers.

4. Configure clients to receive intranet software updates.

Infrastructure requirements

image2

Figure 1 A typical corporate intranet

The picture above describes a typical “closed-circuit” corporate network, which connects computers within a branch and branches together. This network is isolated from outside environment (e.g. the internet or other non-corporate affiliated networks).

Two servers, disconnected model

Because the update server must be connected to the internet somehow (to receive updates from the root update server – Microsoft’s), the safest model for this application is to utilize two server – one to receive updates from outside and one to propagate updates inside the intranet. Data would be manually synchronized between the two machines by various means like tapes, external HDDs or an ad-hoc network. Tape and HDD synchronization theoretically is the most secure option, since no connection is required between two machines and thus, makes data leaks and network penetrations almost impossible (data can only go in and can’t go out). Also, update package’s integrity is always verified with digital signatures from Microsoft so they can’t be compromised.

image3

Figure 2 Two servers model

Package approvals can be made on the internal server. Subsequent package updates from the external server will not affect existing approvals.

Although being the most secure option, this model is costly to deploy:

  • It requires an additional server to receive updates from the internet.
  • It requires additional data containing medium (tapes, HDDs) to synchronize the servers securely.
  • It takes time to synchronize (updates are usually large – to the extent of terabytes and external storage media is usually slow)

This technical note is focused primarily on how to configure and make servers perform this role.

One server, switching connection model

image005

Figure 3 Switching connection model – update phase

image006

Figure 4 Switching connection model – distributing phase

To avoid the cost of an additional server and updating time, this one-server model can be used. The update server could switch between two connections: one to the internet (to download updates) and one to the internal network (to distribute updates). The switch could be made at anytime because WSUS itself could handle partial downloads.

With this model, penetrations and leaks may occur if a malicious party could install a drone in the update server to execute predetermined commands or probe for data from the internal network. Therefore t is advised that the update server be carefully firewalled and allow only the update service to access the network interface.

Also, the update server must be dedicated to the role can can’t perform other server roles since it could be disconnected from the internal network at any time.

One server, always on model

image007

Figure 5 One server, always on model

This model is similar to the one server, switching model except that both the connection to the internal network and the internet is always on to the update server. This way, the update server can continuously update its repository and distribute the update to internal computers. The update server acts similar to a router that allow the computers in the internal network to access the internet, except that the only kind of data that can get through is updates and this data also subjects to approval in WSUS.

This model requires an additional interface for the update server. However, the server can also perform other roles for the internal network, since it is always connected.

This model is the least secure of all three. It poses a serious security threat to the internal network in case the update server is compromised by a malicious party. Therefore, making sure the update server itself is up-to-date with the latest security patches and strict firewall configuration is a must.

Comparison between models

Two servers One server, switch One server, always on
Security ranking High Medium Low
Hardware cost Expensive Minimal Medium
Configuring cost Medium (Two servers and transfer media setup) Low (One server and firewall setup) Low
Management cost High (Update approval on internal server and decide when to sync.) Medium (Need to decide when to switch) Low (Update approval only)
Maintenance cost High (Manual sync.) Medium (Manual/Automatic switch required) Low (Usual security and maintenance checks only)

Configuring WSUS servers

WSUS setup process is straightforward. A configuration wizard is started after WSUS installation is completed to help users configure WSUS. This wizard can be accessed later through the option item in WSUS configuration snap-in (Control panel à Administrative tools à Microsoft Windows Server Update Services)

image008

Figure 6 Options item

WSUS can be configured to pull data from another WSUS server instead of the default Microsoft server (useful when deploying the two server model, connected with an ad-hoc network). Other configuration options are visible on Figure 6 and to the left of Figure 7. It should be noted that this wizard should not be followed on the internal server in the two server model since it requires a direct connection to the upstream update server to continue past step 4. Actually no configuration is needed in this case.

image8

Figure 7 Update source configuration screen

Managing approvals and installing updates

Computers in the internal network could be configured into groups to manage which update should or should not be installed on them. WSUS can only manage clients which have reported to it. Refer to section “Configuring clients” for details on how to configure clients.

image9

Figure 8 Client management screen, listing all active clients

image10

Figure 9 Update approval / rejection

image11

Figure 10 Detailed status report for a client, with report viewer installed

After being properly configured, clients will automatically pull updates from the WSUS server and install them if those update packages are approved and have not been installed already.

Synchronizing update repositories

Deploying the two servers model with tapes and external HDDs require manual export and import of update data. “Update data” includes:

  • Package executables themselves, which are saved as files in WSUS’ update repository (the default folder is C:WSUSWsusContent and can be changed during WSUS’ setup). This is synchronized simply by copying the content of the entire folder.
  • Information about the packages such as checksum and verification data, termed “metadata”. See “Exporting metadata” and “Importing metadata” for details.

WSUS requires both kind of data to be synchronized to work.

Usually, the list of available updates and their metadata is downloaded before the update packages themselves. WSUS will not distribute such incomplete packages.

image12

Figure 11 Warning when package’s files have not been downloaded

Exporting metadata

Syntax: wsusutil export <datafile> <logfile>. This will produce a metadata container file (.cab) and a log file (.log). Both are needed for metadata import. The exporting process could take a while.

image13

Figure 12 Exporting metadata

Importing metadata

Syntax: wsusutil import <datafile> <logfile>.

image14

Figure 13 Metadata import

Configuring clients

To make client update through the new WSUS server instead of the default (Microsoft’s server), the policy for the domain should be modified as in Figure 14 to point to the new WSUS server: http://<servername>.

image15

Figure 14 Client configuration (illustrated with local policy editor)

To edit corporate policy, you con either use the Group Policy Management Console, right click the OU you want to apply WSUS policy and select create policy, you should now see he Group policy object editor similap to tho above screenshot.

Alternatively, you can open Active Directory users and computers, open the properties windows for the OU you want, select the policy tab and click Add (or edit an existing policy).

After policy modification, it may be necessary to:

  • Forces refresh the active policy with gpupdate /force (which will be automatically refreshed in 15 minutes anyways).
  • Clear Internet Explorer’s cache on the client to force reload of update configuration files downloaded from the previous update server.
  • Reauthorize Automatic updates on the client so it is registered on the update server with wuauclt.exe /resetauthorization /detectnow.
  • Make sure that Automatic Update for the client is enabled.

image16

Figure 15 Windows XP Automatic updates configuration screen (My computer à Properties)

Upon successful configuration, the client will appear on WSUS configuration snap-in as in Figure 8.

Downloads

1. WSUS 3.0 SP2: Main WSUS Component.

2. Update fix for WSUS 3.0 SP1: WSUS 3.0 SP1 is known to have a bug that make some client unable to download updates.

3. Microsoft report viewer 2005: Required to view detailed reports.

4. Windows Installer 3.1: WSUS perquisite.

5. .net Framework 2.0 SP1: WSUS perquisite.

6. Background intelligent transfer service 2.0 update: WSUS perquisite.

7. Windows Server 2003 SP2: WSUS perquisite.

References

1. Microsoft Windows Server Update Services 3.0 SP2 Deployment Guide

2. Microsoft Windows Server Update Services 3.0 SP2 Operations Guide

Disclaimer

Registered trademarks are property of respective owners. This document contains no sensitive or specific information about any corporate entity.

Basic for Excel

* For the real action, jump straight to the “with VBA” section

It’s there a decade ago, it started off the wrong foot, generated some (bad) publicity and people started to wonder why it’s there in the first place. It’s quite surprising how people are ignoring it by now. It’s just sitting there in the corner of the file system, doing various sort of slavery work for people. Visual Basic for applications (VBA), imagine how we missed ya!

When I was a kid, I once thought that by choosing the path of a developer, I would sooner or later encounter it and do various sort of wondrous stuff with it, like some kind of smart worksheet that won’t let you type character data into numeric cells.

Sadly, that never happened. There are other ways to do that without VBA, or even without Excel since now I know, there’s Open Office and all. VBA’s role is even somewhat superseded by Visual Studio tools for Office/Applications.

Despite all that, VBA is still there, in the latest (official) version of Office for you… There’s no reason for it to wait any longer, right? This is intended to be a short (and simple) tutorial covering a fraction of what VBA can do for an average user, so you don’t have to be a total nerd to follow this.

Custom functions

Preface

Excel comes with a huge load of functions (hundreds or so) serving various purposes, but sometimes even that is not enough! Let’s say you want a function that returns the last working day before a certain date. Doing this week Excel’s function could be tedious because you need to handle three separate cases which will require two IFs:

– If the given date is Sunday, the last working date is last Friday (Two days backwards).

– If the given date is Monday, the last working date is also the last Friday (Three days backwards).

– For every other day of the week, it’s just the previous day

And also, WEEKDAY() returns a number from 1 to 7 so you have to remember which number corresponds to which day to make the IFs. The finished function should look like this:

=IF(WEEKDAY(C14)=2,C14-3,IF(WEEKDAY(C14)=1,C14-2,C14-1))

(C14 is the cell containing the original date)

Give that function to someone who haven’t read the two paragraphs above, it will take them at least 5 minutes to figure out what are you trying to do in that cell, and then you’ll waste yet another comment to explain that to them!

With VBA

You can program a new function to do the above.

1. From Excel press Alt+F11 or open the following menu/ribbon:

image1

image2

2. Select new module from the toolbar

image3

3. Type in the following code in the new window

Public Function BusinessDayPrior(dt As Date) As Date
 
    Select Case Weekday(dt, vbMonday)
        Case 1
            BusinessDayPrior = dt - 3 'Monday becomes Friday
        Case 7
            BusinessDayPrior = dt - 2 'Sunday becomes Friday
        Case Else
            BusinessDayPrior = dt - 1 'All other days become previous day
    End Select
End Function

It will look like this

image4

4. Click save, return to the main excel window, type a date you want and in C14 and =BusinessDayPrior(C14) (replace C14 with any cell of your choice), the result will look like this

image5

Much clearer, and for an exercise, try to handle the case to suit your own specific need: tweak that function so it will handle holidays too:

– What if the given date is right after a one-day long holiday?

– What if the given date is right after a several days long holiday?

– What if you have compensated holidays (e.g. if the holiday is on weekends, you get another day off)

Automating tasks

Imagine this scenario: You are browsing through a list of stock quotes and you want to take note of profitable stocks on another sheet. If you are doing this manually you’ll have to copy the stock name, switch to the other sheet and paste it. With VBA you can have a button on the sheet that when you click, will do all three steps for you.

1. Open the visual basic editor with Alt + F11 and create a new module (see above on how to do it)

2. Add the following code to the window

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 14/10/2009 by SilentWind
'

    Dim Temp As Double
    Temp = ActiveCell.Value               'Get selected cell value
    Sheets("Sheet3").Select 
    For i = 1 To 65535                    'Scan the destination sheet…
        Range("A" + Trim(Str(i))).Select
        If (ActiveCell.Value = "") Then   '…for the first empty cell
            ActiveCell.FormulaR1C1 = Temp 'Copy it to the destination sheet
            Exit For
        End If
    Next i
End Sub

3. Draw a button on the sheet:

– In Excel 2007, go here

image6

– In Excel 2003, right-click the toolbar, select customize, and drag the button onto the sheet

image7

– In place of the button, you can even you an auto shape! Contrary to popular belief, it does not always require a button to do some useful task!

4. Right click the newly added button/shape and select Assign Macro…

image8

5. Select Macro1 (the one you created at step 2)

image9

Now, every time you select a stock name and click on the button/shape, it gets automatically added to the last empty cell in column A on sheet3.

image10 image11

In Excel 2003, you can drag the button onto the toolbar instead; the steps are still the same. In Excel 2007, you can only add that button to the Quick Access toolbar (well, unless you know how to use VSTO…):

– Right click the Quick Access toolbar and select Customize Quick Access Toolbar…

image12

– Add your macro by selecting “Macros” from “Choose commands from”, select your macro and click add.

image13

Conclusion

I think the two examples above are enough for casual user to understand and adapt for their daily use. Application is endless! Should there be any other questions, feel free to ask; I will add them to future posts if they are interesting enough.

You can download the demo workbook here.

Reference

Wikipedia’s article on this

Comeback

And that’s it! over 3 months since I ever had a thought about blogging! Maybe I wouldn’t make a come back this soon if Matt over there didn’t wrote that scary post about the recent WordPress worm. I’m invulnerable to it since I turned off user registration since day 1 :P. However, lacking behind in updates isn’t something an IT professional should get used to so I paid 3 hours to have this blog upgraded and moved to a new domain, since my old one is going to expire in December anyways (The Koreans hosting it decided to cheapen themselves earlier this year by violating their own mission to bring free domains. Oh well).

Not the first time I move nor upgrade; in fact maybe I should consider doing WP upgrades and moves for other people for profit: it’s so smooth it almost feels good 😛 Can you imagine it? I described the process once before already but this time the situation is a bit different: I’m not moving host, everything is already in the database so Import/Export isn’t really necessary (and it costs time + bandwidth to do so).

1. I installed WP at the new domain and copy all the plug-in and upload over

2. I crafted the wp-config.php for the new domain to make sure all changes are dealt with. This time there is no change from WP 2.7 to 2.8

3. I changed the domain in the CP and have to login to the new domain. It says a database upgrade is needed, I hit next and got a blank admin screen. Befuddled

4. Google a bit, the first hit says this is caused by plugins, I moved them out and reload, it worked! All the posts are there and the theme is kept

5. Add the plugins back one by one until I found the one causing problem: No follow in posts

6. Edit the images to point to the new domain: if I were to use Import/Export like last time, the server will have to pull all the file over (which is on the same server, a waste of bandwidth) but the image URL in the posts will be automatically updated. This time I just move them over to the new domain’s directory, so I updated the posts’ images by two simple SQL queries:

UPDATE wp_posts SET guid = replace(guid, 'http://silentwind.co.cc', 'silentwind.za.net');
UPDATE wp_posts SET post_content = replace(post_content, 'http://silentwind.co.cc', 'silentwind.za.net');

Images should be displaying by now.

More disassembly

I concluded my last blog post streak with a disassembling post, it’s quite appropriate to have another one right now to celebrate my comeback 😛 This is going to be a short one: my TV Box which is used to watch analog TV with my computer’s monitor.

It used to work fine when new but when it’s about 6 months of age, its image quality gradually decrease until I’m more frustrated by watching TV with it than not at all, at which point I thrown it into storage. When I pulled it back out 2 weeks ago for a sudden need, it worked. But the next day it refused to display most of the channel :/

So I took it apart, maybe I can do something about it, it’s better than being furious at it anyway.

Pict0031

To the top is the outer case and the speaker (the one with a couple of wire dangling). To the bottom is the main circuit board, which is also everything. All the components are soldered to the board so nothing else could be taken apart without damaging it

Pict0033

Close up of the mainboard

I noticed a variable resistor above the chip in the middle (the one with the number 4 on it). I plugged everything in, turned it on and start tuning it left and right slowly to no avail. Later, I decided to turn it all the way to the left (so it would rise and possibly allow me to take something else out :P), this time it gives crystal clear picture and got all the channels. What confuses me is if from the start, it was working well because the resistor were at the top then what kind of mysterious force pushed it down later?

Chinese stuff after all, heh.

Disassembling that MP3 player

Almost everyone has one of them. They are cheap, they are convenient, they are a good way to show off. I had one from years ago but rarely use it since I hate anything that isn’t rechargeable (the ISO-14000 kind of guy). The button are a little off since I move it around a lot and must have broken something :/

So, when I have a new set of screwdrivers, it became my first victim :P.

I got off on the wrong foot: I tried to crack open the top side (the side with the screen) when actually I should do it otherwise. I found out eventually, but the casing suffered heavy scratches; and I acidentally peeled of a capacitor too 🙁

The rest is only a few screws, loose them and here are the results

Back

Battery side

Front

Screen side

Nice look inside Chinese manufacturing “technology”. All they’ve been doing is make a cheap (I mean really cheap) processor and bridge between the storage and the USB interface. It’s the Processor you see in the middle. AT is for Actions semiconductor. The storage chip is Samsung’s and the Radio is from Phillips.

It turned out what I’ve worried about this poor thing’s quality doesn’t matter that much. As long as the computer can recognize it, the thing will just work fine, the rest of the parts are from more qualified manufactures 😛

IMO, when stripped bare, the LCD looks a lot sexier, you can see the bright LED illuminating the fiber optics plate beneath. They look pretty good but is covered by the original casing. My guess is they make color by blending 4 LEDs: red, green, blue and white as they seem to be the strongest colors and have only one dot of light to the left side.

Pict0008

Blue

Pict0009

Red

It works fine even when disassembled (and lose a certain capacitor :”>). I guess that control screen brightness, since that’s the only setting not working.

In the end I’m too lazy to reassemble the thing. Furthermore, the buttons are off because the plastic handle inside is swollen and broken. Cheers to Chinese plastic! I can hold it back with glue but why do so when I can hit the circuits directly? So now I have the weirdest MP3 player I’ve ever seen. At least it does not electrocute me 😛 and is about 30% smaller from the original one (the case is damn big =__=).

Also, it’s not until I completely disassembled the thing I found many have disassembled their MP3s too; anh this website. They have a nice guide how to do what I’ve done to recover dead player. The site is a community run by owners of the MP3s. It turns out that they are pretty popular around the world. They have everything you’ll ever need related to these things (circut map, specs, firmware etc.)

The MP3s came under a variety of brands and forms but the internals are just the same. Some folks even made a computer out of it. There are also a resource editor and (code) disassembler so you can modify your MP3’s animation, logo and strings.

Report @ localhost

I was developing an automated report maker. Its mission is to collect data from various sources (MSSQL, Excel and Access) to create a summary of the numbers. Things hit a few bumps along the way but finally after 2 days, the collecting phase (the one I thought was hard) is complete.

On to the “easy” part…

C# is such a great environment that they could have offered you with two report solution: the standard report control that comes with the framework and Business Object’s Crystal Report. I was looking for something that could accept parameters, use that for some SQL queries and display the result with a couple groupings.

Crystal report as overtly complex for the purpose, heh, and adding parameter to the query is not that easy as it depends on the project’s datasource.

The report control is even worse. I vividly recall from the last time I worked with such reports they have three tabs in the designer for me to edit data, layout and preview the result. But to my surprise, I can’t even find the preview command anywhere after I created a report in a Winform application. Not on the toolbar, not in the menu, nowhere to be found!

After a little twiddling around, I found another kind of project dedicated to report: the report wizard from the Business Intelligence studio (I happen to have SQL Server Express installed on the development machine). Exactly what I’m looking for! I just paste in the query, choose the groupings and voilà! Report to print! And they have three tabs…

I thought to myself it was all Microsoft stuff, and maybe the report viewer back from the winform project could view this, so I copied it over, point the control to the report, and execute it.

“Data source not found” is all I get.

It turned out that the world isn’t all pink like Microsoft thought (see the “just rename your file and it’ll work, we are Microsoft!” article). The business intelligence’s report is to be and only to be deployed on a report server, which is really not convenience for me since my version of SQL is Express and does not include the report server :(. From a winform application, the report viewer control will just ignore the queries you spent hours crafted in the .rdl report when you rename it to .rdlc (Because they name it report for different meanings on different tiers – A total solution on the server and a mere viewer on the client). They say you have to rebind the datasource. Easy for them to say when they aren’t binding a lot of queries together – by hand since Microsoft’s great data designer won’t allow you to create a new table with schema based on your queries, the situation is even worse for parameterized queries! =__=

Queries are naughty, so no rows for you!

Queries are naughty, so no rows for you!

I'm too lazy to parse that text (the same query run just fine from SSMS)

I'm too lazy to parse that text (the same query run just fine from SSMS)

Folks on the ‘net have not been helpful this time. All they did was point back to the article, It’s surprising to see that something not impossible as viewing .rdl locally is impossible to do with what Microsoft allow you to use. (Yes, they allow you to preview the report locally just fine but they hid the damn report viewer!)

Who needs Microsoft when we have the Apache license? 😛

Somebody did it the hard way: they created a new parser and render the report from scratch. It have evolved for sometime: 0.5 from the like above to 4.0 latest. But still I does have limitations: some attributes are yet to be parsed and are ignored (for example, tables will not expand to accomodate the records) so the report may look a little off, but that could be fixed with a simple moving of parts. After all, this is open source software: if you wanted it to be better, dig in! 🙂

Anyways, it gets the job done. My reports are displaying expected numbers and passing parameters are painless. Cheers to the author!

RDL reports being viewed locally

RDL reports being viewed locally

Latest version of project RDL is available at FYIReporting’s homepage.

UPDATE: And don’t think you can just bind your data with the dataset designer, it won’t tell you anything when you choose the data set as the data source, but when you run it you get an ugly “not bind to an instance” error. In the end you’ll have to do things manually with Microsoft.
reportViewer1.LocalReport.DataSources.Clear();
reportViewer1.LocalReport.DataSources.Add(
                new ReportDataSource("Table name", new DataSetName.TableAdapter().GetData(Parameters))
);