June 2008 - Posts

Database Secure These past two weeks have been very exciting for me.  I have gotten to be involved in some R&D for one of the companies that I consult for.  I LOVE R&D!  There is always a better way to do things and poking your head out from the sand every once in a while can be very beneficial! 

Okay, so here is the story.  You want to have auditing so you can log the user responsible for the change.  It follows that you simply connect with that users credentials and now you have a great audit log!  The problem is that if you have thousands of users (or maybe even less) you are going to start to experience a large number of connections on the server. [Image 1]  This is because each user has their own connection pool that, even if it is going through a service, cannot be shared with any other user.  A large number of connections is starting to really slow down your database so you decide to create a generic user account for the service.  The problem is now our audit log will only show the service account as the person responsible for the change! [Figure 2]

no connection pooling
Image 1


Pooled Connections (Shared Login) 
Image 2

So you have two ways in which you can fix this.  First you can mandate that all changes to the data must happen through stored procedures.  If we make sure that every stored procedure passes the user who is responsible for the DML changes then we can add our own audit records.  The upside is that not only can we take full advantage of connection pooling and security is better using procs.  The downside is that this can be intensive and the change log probably cannot be driven by triggers and we may have to come up with a complex and fallible process. 

Alternatively, you can use a basic service account for the connection and connection pool and run the SQL 2005 / 2008 "EXECUTE AS LOGIN" command before any other DML statement.  [Image 3] This is called User Context Switching and could be done automatically using a specialized command object.  The only down side is that because SqlCommand is a sealed class we have to use composition rather than inheritance.  This may also force us to create a compatible SqlDataAdapter but when all is said and done you have a system that is both scaleable and robust.  These changes are also likely to be compatible with SQL Server 2008's CDC technology which can automatically log changes to a table. 

-- TSQL TO CREATE A USER WITHOUT A LOGIN -- AND USE USER CONTEXT SWITCHING CREATE DATABASE [TestDB] GO USE [TestDB] GO -- Create the Service User CREATE LOGIN [ServiceLogin] WITH PASSWORD = 'Uor80$23b91'; CREATE USER [ServiceLogin] FOR LOGIN [ServiceLogin] GO -- If we ran this before then we need to drop this user DROP USER [nzaugg] GO -- Create a user without a login CREATE USER [nzaugg] WITHOUT LOGIN GO
-- Wade said this is backward, so I swaped it for him...although I'm not fully convinced! GRANT IMPERSONATE ON [nzaugg] TO USER::[ServiceLogin] GO -- Switch User Context; Optionally Specify 'NO REVERT' -- If we run this in Query Editor with 'NO REVERT' the -- only way to go back to our original login is to reconnect! EXECUTE AS USER = 'nzaugg' --WITH NO REVERT GO -- Verify that we are now user 'nzaugg' SELECT user_name(), suser_name(), original_login() -- If we used 'WITH NO REVERT' on our EXECUTE AS statment -- We won't be able to revert and this will throw an exception REVERT GO -- Are we still 'nzaugg'? SELECT user_name(), suser_name(), original_login() -- DROP THE DATABASE DROP LOGIN [ServiceLogin] GO USE [master] GO DROP DATABASE [TestDB] GO

SQL User Context Switching Results

Remember, in order to do this all of these users must exist in the database.  They must also have rights to perform the operation in the original DML statement.  This is where users without logins come in handy (see code lines 18 & 21).  The optional WITH NO REVERT will be handy for logging and will further secure our database.

Pooled Connections with EXECUTE AS LOGIN 
Image 3



SQL Server 2005 Books Online (September 2007)


Sets the execution context of a session.

By default, a session starts when a user logs in and ends when the user logs off. All operations during a session are subject to permission checks against that user. When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. After the context switch, permissions are checked against the login and user security tokens for that account instead of the person calling the EXECUTE AS statement. In essence, the user or login account is impersonated for the duration of the session or module execution, or the context switch is explicitly reverted. For more information about execution context, see Understanding Execution Context. For more information about context switching, see Understanding Context Switching.





Firewall When I seem to have re-occurring themes happen naturally I find that those are good things to blog about.  Recently I was speaking with my brother (who has incessant adware problems) about Internet Safety and Cyber Security.  A week or two ago I had a stimulating conversation with my friend Anthony about security, and this morning on the Diane Rehm Show there was a segment about Cyber Threats.  In which one of the guests stated that he works on Banking systems and will not engage on Online Banking.

The first question anyone has is how wide-spread is this problem and does this really affect me.  The answer is that this is wide-spread and it affects everyone whether you own a computer attached to the Internet or not.  The scary part is that even the most pragmatic and Internet-savvy users can fall victim.  Does this mean you or I should stop using the Internet altogether?  Absolutely not.  While anyone can fall victim to this kind of threat there are steps to take to greatly reduce your risk!

In this age of technology we have almost no limits to our technical abilities.  Unfortunately, our attackers have this same ability.

Types of attacks:

  1. Trojan Horses - A computer program that poses to be something useful but allows access to your system from the Internet.
  2. Adware - A computer program that either tracks your usage and sells that information to marketers or pops adds up on your computer.
  3. Spyware / Key-loggers - A program that "watches" what you do on your computer.  They can record every keystroke and send that information to a scammer.
  4. Worms - A special kind of program (which usually includes spyware or Trojan horses) that spreads its self -- usually through email or mapped network drives.
  5. Proxy - A term used for an attack coming from a computer in which the user/operator has no idea about.  This is a compromised computer system that a remote scammer has installed a Trojan horse on.  This "bot" can now do anything it's owner wishes. 
  6. DOS - Denial of Service attack.  This is an attack on a server which will render it unable to complete the task it for which it was designed.
  7. DDOS - Distributed Denial of Service attacks.  This type of attack usually involves overwhelming a web site to the point that it can not serve requests to legitimate customers.  This usually involves a large amount of "bots" controlled by a single party and often controlled through a mechanism called IRC which is a lot like a chat room.
  8. Buffer Overflow - this is a special type of attack that targets specific code.  Basically if the scammer can pass a malformed piece of data to a function in code then they might craft it in such a way that it will execute part of the data.  That allows the attacker to run any kind of code on your machine.  Depending on the security of the process that was compromised (which is usually pretty high) they can take over your computer.  Remember, any maliciously-crafted data can cause this including data they try to send to you on an unprotected Internet port or data that you requested from a malicious website.  Simple things such as an image can contain a buffer overflow attack (and have in the past).  This type of attack is not only limited to windows.  It can be attributed to careless programming but can often be a weakness in the compiler itself. 
  9. Root Kits - This is a special kind of hacking technique which involves exploiting one small veunerability after another.  This is typically on web servers who's upload function is unprotected or ones which have a buffer overflow exploit in place.  Once a file is uploaded it is executed and causes a larger hole to be created.  Eventually they can take control over that machine.
  10. Email Scams - Email is where most of the bad stuff originates from.  That is because it is cheap and easy to send mail and because it is often easy to harvest or guess an email address.  It's far more difficult to get people to visit a malicious website.

Q & A:Virus

  • Info: The terms  virus's, adware, spyware, and worms can be safely summed by the term malware.
  • Q: Are Mac's really more secure than PC's?
  • A: Yes and No.  Although the Mac has made a comeback the past few years it is still a very, very small percentage of the computers in the world.  Because of this most every virus targets a PC running some version of Windows.  However, this does not mean that your "safer" using a Mac.  As Macs become more more popular more virus will be written to target them and they may have more success than targeting windows.  Windows has gone a few rounds of cops and robbers where Macs have not.  In my opinion, if you are buying a Mac simply because you think you are "more secure" than don't bother.  A sense of false security is the most detrimental risk of all.
  • Q: Who is attacking me and why?
  • A: Attackers are generally part of 1 of 2 different types.  People in it for personal gain and government-sponsored groups.  There has been a very significant and organized amount of hacking coming from China which suggests that the Chinese government sponsors this type of activity.  Much of their effort seems to be on mapping our resources around the net.
  • Q: I get a lot of email about stocks, what is that about?
  • A: This is the old pump & dump scam!  They artificially inflate the price of "penny stock" that they own a large number of shares of.  They send this email en masse telling people to buy lots an lots of this stock.  Enough people buy that the stock price raises and the scammer sells the stock and allows it to tank.  This kind of scam can be costly for both the business offering the stock and for those foolish enough to actually invest in it.
  • Q: Do people really fall for the emails claiming to be from their bank?
  • A:  No, not really.  The problem is that if just one in ten million *do* fall for this scam then it would have been worth it.  They can send these phishing emails out at a rate of millions per minuet. 
  • Q: Will the Internet ever become a safe place.
  • A: No.  Like the game of cops and robbers this will likely play out forever.  The programmer in me wants to believe that it is possible to have 100% secure software.  The pragmatist in me knows that it may not ever be possible.  However, overall I do tend to believe that it will get much better but will probably get much worse before that begins to happen.
  • Q: Does looking at porn on the Internet make me more susceptible to malware?
  • A: Absolutely!  Porn and Malware go hand-in-hand! 
  • Q: Does downloading "cracked" programs make me more susceptible to malware?
  • A: Absolutely!  Crack sites, key sites, etc. are a Trojan horse delivery mechanism.  Why do you think these people crack these apps?  They do it to lure you there and take control of your computer.
  • Q: Can a virus really take control of my email?
  • A: Yes, it can but usually it doesn't have to.  SMTP (the protocol in which mail is sent over the Internet) has absolutely no good way of verifying that you are who you say you are. If your computer is hacked then it's probably your email address book they are after. 

Tips & Tricks:

  1. Get a home firewall that uses NAT.  You may already have this and not realize it but computer systems sitting behind NAT "invisible" to Internet scans which greatly helps keep your computers safe!
  2. Let your computer update regularly!  Make sure auto-update is turned on and working.  If the computer needs to restart to apply a patch make sure that happens ASAP.
  3. Install a software firewall.  They will slow down your computer, I know, but they are a necessity today.
  4. Let your virus scan run weekly
  5. Own two computers (especially if you have kids).  Use one for Internet banking, and Internet purchases, storing personal information and nothing else.  Use the other one for everything else, keeping any kind of personal information off of this machine.
  6. Use the least possible permissions you can for your user accounts. 
  7. Our school used a hardware device in which every time the computer is rebooted the state is restored to exactly the same state every time.  This would be a really great tool for your general use computer. (I'll post a link when I can find one)
  8. If you are at an Internet shopping site and you get a certificate error, leave now!  That certificate error is the ONLY thing protecting you from a man-in-the-middle attack!
  9. NEVER, NEVER, NEVER, NEVER, NEVER download or open an attachment you are not expecting!  Even if it looks like it is from a person you trust!  If it is from a person you trust verify it's contents before opening!  They may have been sent this wonderful screen saver and wanted to share it with you.  That's great and all but that screen saver is probably a worm!  Also, they may not have actually sent it to you, the screen saver did it!
  10. Do not download any executable file.  Those include files that end with: .exe, .scr, .bat, .pif, .com, .dll, .ocx, .sys  Also watch for the space trick where the filename is "myfile.zip                                              .exe".  Notice the spaces?  You may not see those in Outlook or whatever else you are using.
  11. Verify from the author any other types of download.  Recently virus have been able to attach to innocent PDF files!  The moral is that there really is no such thing as an innocent file!


  1. Virus' are not easy to detect!  Virus scanners use something called Heuristics to find virus's, adware, spyware, and worms.  
  2. Both client and server need a way to be independently authenticated by a trusted 3rd party, and if the trust can not be established then there must be no way to continue.
  3. We need to phase out passwords!  They are way too easy to predict and/or capture!
  4. We need a way to positively identify (for computing purposes) every user on the Internet.  This is the only way we can really develop trust relationships with other systems and the only way to end SPAM/Phishing.


Visit these links for more information:

Be careful out there!

linux Recently, some Linux developers have condemned the practice of shipping drivers without the source in an open petition.  The reasoning for such a petition is that "any closed-source Linux kernel module or driver to be harmful and undesirable. We have repeatedly found them to be detrimental to Linux users, businesses and the greater Linux ecosystem."

This is the same kinds of growing pains that Microsoft had in the early days of Windows, esp Windows 95.  The Windows OS seemed to be riddled with bugs but the problem was (and always had been) the device drivers and graphics cards are the usual culprit.  However, stating simply and closed source drivers are "harmful and undesirable" is a lot like saying that the only to prevent accidents is to not allow anyone to leave their homes.  Very few device drivers for windows are open source and there has been tremendous improvement in stability over the past few years.  Microsoft has made enormous efforts to train device driver programmers for other companies and has released an extensive DDK and debugging tools.  Most recently they even made it so some drivers don't even have to operate in the kernel space but can exist in the user space.  This will greatly increase stability of the Operating System by protecting the sensitive kernel.  A similar concept could be adopted by the Linux Kernel.

Open source drivers may be a good solution for Linux but this petition makes it sound like it is the only solution for the longevity of Linux. There is a down-side to open access to source code including uncontrolled visioning which can cause worse problems.


--Nathan Zaugg


See also:


with no comments
Filed under: ,

For now on you may address me as my proper title:

NerdTests.com says I'm a Nerd God.  What are you?  Click here!

I wish the title was a little less blasphemous; therefore you may alternately call me 'King Nerd'.  That would also match with my wife who is a "Slightly Dorky Nerd Queen".


I also scored quite high on the version 1.0 test:

I am nerdier than 92% of all people. Are you a nerd? Click here to find out!

Remember, that is 92 percentile!

--Nathan Zaugg

with no comments
Filed under:

Those who know me will tell you that Microsoft might as well just put me on payroll because I talk about their new products all day long.  I have even characterized myself as an "Unofficial, Unpaid, Microsoft Solutions Evangelist".  Having said that, I know what's good and bad about the products I love so much.  I also know when someone else has something worth taking a look at. 

Google Defined

Google: very successful Internet advertising company seeking to find, buy, and evolve technology that has promise and then figure out a way to turn a profit on the new technology. 

And they are very good at what they do.  When Google purchased KeyHole (Google Earth) everyone started scratching their heads and wondered why!  I may not understand exactly how but I am almost positive that they have turned a profit on it. 

Well, here is another interesting gem.  Google SketchUp is a armature 3D modeling tool.  It's easy to pick up and has some pretty cool features.  I created the sketch below of my back yard (or at least how I want it to be).  There is tons of detail from the landscape brick in the back to the translucent windows.  I did the below model in less than an hour and when I started I couldn't figure out how to add dimension to my square shape or how to move it around. 


3D Sketch Yard-2

Cool Features

Some of the cool features are textures, and the 3D model community.  The textures makes these simple sketches look very life-like.  It has most of the common building materials and many basic outdoor shrubs & plant-life.  You can also capture an image from Google Earth and transpose it onto your work area.  You can even take a picture of something and mold it to your objects. 


Google 3D Model Search

I added my model to the Google search and now anyone can use my shop! 

The Not So Cool

It would be nice if there was more keyboard involvement.  I find my self switching between tools a lot and it's cumbersome.  While you can get results pretty quickly the interface is difficult to maneuver even after using it for a while.  Simple tasks like changing the size of a rectangle are complicated.  You also have an inability (or at least it seems to me for now) to make things exact.  I'd like to be able to enter the size of the rectangle and then enter the coordinates.  And although it's pretty fast for 3D modeling it does make you wait quite a while for some tasks and I think it could be a whole lot faster!  It seems to be written in Ruby and my guess is that it does not take advantage of hardware acceleration and that if it were written in a language like C++ or C# than it would be much faster!  Also, I hate the name!  CouldnÂ’t they have named it Google 3D, G-3D, or even GSketch?

All said, for a free pice of software it does help a lot in convincing my wife that a 30-35 shop will not look too big for our yard!


--Nathan Zaugg

with no comments
Filed under: , ,

The term "circular dependency" may be foreign to some programmers (especially if you do Java as it is a pretty common practice).  However, anyone who has done some scripting for a referential database knows that you have to run scripts in a certain order.  Running scripts out of order causes errors when you run.  The interesting trick is that if you run that same incorrectly-ordered DDL script again and again you will eventually get it to run without errors.  If you were unaware of the order being incorrect and thought to yourself in that moment "Stupid database!" then this blog post is for you!

What is a circular dependency?

It is simply two libraries that use each other (either directly or indirectly) as shown below:

circular depencency 

Figure 1: Circular Dependency




Figure 2: Complex Circular Dependency


The complexity of a circular dependency may vary.  If you are using Visual Studio and have all of your projects loaded into a single solution AND you add Project References (Right click on project -> Add Reference -> Projects Tab -> {Project Name}) then the IDE will not allow you to create Circular Dependencies.  In fact, this is a good practice as Visual Studio will ensure the correct build order. 

Why are circular dependencies bad?

Just like our Database example above, a circular dependency makes it so you can not guarantee that your application has the latest code.  That is a big deal!  Here is why:

  1. I make changes to Application 1 (in Figure 2)
  2. I build my project, The changes I made in Application 1 may or may not have gotten into Application 2 (depending on build order).  It may have taken a copy of the compiled code that was left over from the last time I built.
  3. Application 2 depends on this new functionality to provide services to Application 3; This functionality will not work correctly with this build.
  4. Application 3 may or may not depend on these same services to provide back to Application 1

As you can see in this scenario, there is no such thing as a "correct" build order when there are these circular dependencies.  The only way you can arrive at the correct version of the code is to build it as many times as there are nodes in our circle.  That would mean for Figure 1 that we would need to build twice and three times for Figure 3.  Some of these dependencies can get really ugly!  Here is some actual code running in an actual company that I did analysis on some time ago using a tool called Structure 101.

image image image image


How do I fix circular dependencies?

There are some steps to take to solve even the most complex tangles!  They all involve refactoring your code though.

  1. Refactor common code into a "base" dependency; I usually call this "Common" (figure 3).  BEST SOLUTION
  2. Remove code that is unused.  In the tangles shown above many of them are using deprecated/unused code.
  3. Duplicate the sections of code used.  This should be seen as a last resort but given the choice between code duplication and circular dependencies, I take code duplication ever time!



Figure 3: Refactor a Common



There are two kinds of design concepts for nTier (and other types of architectures as well) called Logical Layout Design and Physical Layout Design.  The Logical Layout is simply that your software occupies the same project/package but leverage different classes.  In contrast Physical Layout Design forces each tier to be separated into different Projects/Packages.  So long as we are careful to manage the dependencies between these packages from the start this is the preferable way to code.  While the logical layout does not suffer from the dependency problem eventually you may wish to break these classes apart and find that there are a lot of inner-dependency that should not exist simply because they occupied the same project.  Remember to keep it clean!

Microsoft Silverlight 2.0 Beta 2 has been released.  This version is supposed to be pretty stable as I understand it.  The tools on the other hand, still feel very much like a beta!  I had a heck of a time getting this junk installed!

The link to download Silverlight 2.0 Beta 2 for Visual Studio is here: http://silverlight.net/GetStarted/
(note: This installation package includes all you need including Silverlight runtime and SDK)


My Installation Issues


I got to know these dialogs pretty good as I'd tried this many times!


It makes you close all browsers!  Ok, sure!


DANG IT!  What now? 


So you're telling me that I can't have a patch?


Here is the output of the error:

Created new ExePerformer for Exe item

[6/9/2008, 13:48:11] Action: Performing Action on Exe at c:\6472026481f78458c12aa62cd2\silverlight_uninstallrtmpatches.exe...

[6/9/2008, 13:48:11] (IronSpigot::ExeInstallerBase::Launch) Launching CreateProcess with command line = c:\6472026481f78458c12aa62cd2\silverlight_uninstallrtmpatches.exe /q /uninstall
[6/9/2008, 13:49:11] (IronSpigot::ExeInstallerBase::PerformAction) c:\6472026481f78458c12aa62cd2\silverlight_uninstallrtmpatches.exe - Exe installer does not provide a log file name
[6/9/2008, 13:49:11] (IronSpigot::ExeInstallerBase::PerformAction) Exe (c:\6472026481f78458c12aa62cd2\silverlight_uninstallrtmpatches.exe) failed with 0x80070643 - Fatal error during installation. :
[6/9/2008, 13:49:11] (IronSpigot::ExeInstallerBase::PerformAction) PerformOperation on exe returned exit code 1603 (translates to HRESULT = 0x80070643)

[6/9/2008, 13:49:11] Action complete

[6/9/2008, 13:49:11] (IronSpigot::LogUtils::LogFinalResult) Final Result: Installation failed with error code: (0x80070643), Fatal error during installation.

so I hit the net and find these instructions: http://weblogs.asp.net/bradleyb/archive/2008/03/06/installation-tips-for-sivliverlight-tools-beta-1-for-visual-studio-2008.aspx

try to uninstall the KB:
(found under updates)


Kept asking me for some location!  Wasn't going to work....what is the backup solution?


Ok, extract the files in the installer...sure.


I put them in a dir called "Install"


Then RUN: msiexec /uninstall VS90-KB949325.msp /L*vx VS90-KB949325-2.log

Double Dang!  Ok, whats up??


Hit the web agian: http://silverlight.net/forums/p/17663/58925.aspx
and download MSI ZAP: http://support.microsoft.com/kb/290301


Tripple Dang!  Updates are not listed!


Downloaded tool: MSIINV

Download Link:


msiinv.exe -p > Installed.txt

No luck!  Still not giving me the GUID for KB949325! 


msiinv.exe -v > Installed.txt

Took SEVERAL minuets to run and created a 10MB text file!  When it was done I tried to run msizap but it wouldn't go.  I'm just going to install these packages seperatly now that I have them extracted from silverlight_chainer.exe.



  1. Silverlight.2.0_Developer.exe
  2. msiexec /p VS90-KB949325.msp /L*vx VS90-KB949325.log REINSTALL=ALL
    (gave me an error -- ok, I'm skipping it!)
  3. silverlight_sdk.msi
  4. VS_SilverlightTools_Beta2_Setup.exe


Sat here for quite a while!
devenv.exe seems active though.


Install Completed!


This is also a good resource:



This seems to only be a problem if you were an early adopter of the Visual Studio 2008 SP1 Beta 1 fix.