Friday, August 10, 2007

So I was talking with Rob Farley at Tech.Ed (Rob, you’re my hero ;-)) and the topic he raised the topic of what if we did not grant *users* access to our SQL Servers, but instead only allowed groups.

Not something I had really thought about much before but This is a really good idea™.

The question that naturally arises is: How do we enforce this with something stronger than “because I said so”.

The good news is that it appears that SQL Server 2008’s Declarative Management Framework (a.k.a. “policies”) can be of assistance here.

Background on the Declarative Management Framework:
  • A Facet is a piece of data about the system that you can use in your policies. 23 Facets are included in the current Katmai CTP.
  • Each Facet can have 2 or 10 or more Properties. It is the properties that are used in your conditions. Facets are logically groupings of properties by SQL Server
  • A Condition is a logical expression about a property or set or properties
  • A Policy is a named instance of a specified Condition with a rule on how it will be enforced (on a schedule, on changes etc).
Step 1: Create a new Condition and call it GroupsExist. Choose the Login facet and the LoginType property. Set it to equal WindowsGroup.

In the case that there are user accounts like .\ASPNET or the ##MS_PolicyExecutionLogin## login that have a legitimate right to be there, list those in the expression as illustrated below.
 

Step 2: Create a new Policy based on the GroupsExist condition and cal it OnlyAllowGroupsNotUsers. Apply it to Server/Login and set it to Enforce as illustrated below.



One of the really cool things is that you can create your policies in a not enabled state, and use the built-in testing harness to get the policy right before enforcing it.

To test a policy in SQL Server Management Studio, right-click on it and choose Test… This will run the policy against the current state of the server and report the details of success or failure.

Now when the policy is disabled, the following statement will do as it is designed.

CREATE LOGIN [MYLAPTOP\SampleUserAccountAccess] FROM WINDOWS GO


However when the policy is Enabled, I am getting the following error, which I assume means I am am on the right track :-)

Msg 10314, Level 16, State 11, Procedure sp_syspolicy_execute_policy, Line 25
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.

Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'Microsoft.SqlServer.DmfSqlClrWrapper, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies.

Strong name validation failed. (Exception from HRESUL
T: 0x8013141A) ---> System.Security.SecurityException: Strong name validation failed. (Exception from HRESULT: 0x8013141A) System.Security.SecurityException: System.IO.FileLoadException:
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)

at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
The statement has been terminated.


I’ll research this message next week, as well as re-do the sameple when back home and connected to AD, and if the results differ I’ll re-post.

...Topic for future blogging: Ensuring consistent policies across several instances of SQL Server. Stay tuned.

Listening To: The dodgy motor in the fridge in my apartment.





Friday, August 10, 2007 8:14:44 AM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [1]  | 
 Tuesday, August 07, 2007



If you are attending and wanna hang out, drop me a line via this blog.

Otherwise, see you next week* :)

* by which time I will be significantly smarter... ;-)

Tuesday, August 07, 2007 12:34:22 PM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [0]  | 
 Tuesday, July 31, 2007
The CLR Add-In Team Blog has just posted a list of the 7 new features that they have included in Visual Studio Orcas2008 Beta 2.

Tuesday, July 31, 2007 11:49:06 AM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [0]  | 
 Thursday, July 26, 2007

(this is just a post that I'm putting out there, when I Googled for the error I got nothing relevant, so this post is dedicated to the future searcher, whoever you may be...)

The error occured on a brand new install of Windows 2003 Server R2 w/ SP2 slipstreamed.  Oddly, this media set had installed another server without error, but anyway the error was:

Setup library netfxocm20.dll could not be loaded, or function UrtOcmProc could not be found.  Contact your system administrator.  The specific error code is 0x7e:

The solution was to reinstall the "R2 bits" (aka. Disk 2) and reboot.

I'm still looking for a log file or event log message or any clue anyware in the system to indicate why it went bad, but don't like my chances...

Thursday, July 26, 2007 5:58:16 PM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [0]  | 
 Sunday, July 22, 2007

It dawned on me today, why the same old song isn't working for the youth of today...

The message should not be that sex leads to pregnancy, but that sex leads to prams!

Yes, I'm talking about prams, strollers and buggies people!  You're killing me already!  I give in! 

That will be all :-)

Sunday, July 22, 2007 10:24:06 PM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [1]  | 
 Thursday, July 19, 2007

This is the third of my posts about group policy for web developers [part 1] [part 2], and today's post is a from-the-trenches tail about how you can get 401.1 Access Denied as a result of applying group policy.

Mad props to Jimmie Russ and his post Access Denied 401.1 goes away temporarily after IISRESET, as it was his post that sorted my problem today.

As you will see in his post, it is possible to push out User Rights Assignment policies via a GPO that can define the following rights:

  • Log on as a service
  • Log on as a batch job
  • Allow log on locally
  • Access this computer from the network

The above list may not be complete, but it was these four that caused a problem in my IIS environment.

Two tell-tail signs that this is your problem:

  1. If you run the always awesome AuthDiag utility and it reports that certain local accounts (like IUSR_*) have rights missing, specifically the ones listed above.
  2. When you view the User Rights Assignment in Local Security Settings
    • The above four rights are locked (have a little lock icon instead of the "blue 1s and 0s icon") and are not able to be edited.
    • The above four rights are not granted to our local accounts (again IUSR_* et al)

Again, it may not always be exactly those four - what you are looking for are policies that will not allow your IIS accounts (as listed in the AuthDiag results) to log on.

If I had my time again, I would have cranked up the failed security events and listed them here, but my servers are working again and I like it that way so this can be left as an exercise to the reader :-)

As Jimmie said, the solution is to have these rights not pushed down to your web servers.  This can be by not applying them in your Default Domain Policy, or by putting your web servers in a seperate OU blocking policy inheritance.  If you're not sure, consult an expert.

Thursday, July 19, 2007 4:39:09 PM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [0]  | 
 Wednesday, July 18, 2007
Sorry to anyone who is waiting for an IM, email, phone call or anything from me... there is a burning smell coming out of a server :-(

If you need me, I'll be double-checking that I have valid backups...

Update:  The acrid smell I mentioned above was in fact the smell of fresh just-out-of-warranty power supply.  I even had to wash my hair to get the smell out.  All is OK now.  :-)

Wednesday, July 18, 2007 5:08:36 PM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [0]  | 
 Thursday, July 12, 2007

How to start?
You will need several assemblies.  This is because to reach the level of decoupling that is desired, the pipeline has a certain pattern of dependencies between projects.  I’m not sure if I am subconsciously not objecting to it or actually actively like this now, was not always the way.

A pipeline?
Yes, you need one. 

What is a pipeline?
It’s a series of folders that live somewhere (does not have to be under your application's path) and each of the assemblies have their place in the structure.  The folders will look like this:

It’s probably a good idea to build one now in your project tree and set the build output as you go.

There is a graphic explaining the pipeline on the CLR Add-In team blog (with nice colours), it is also in the Orcas MSDN under the title: "Pipeline Development Requirements".  The MSDN one has the addition of lines to indicate the static dependancies.

I've chosen to show it differently in my graphics (don't say a word!) to indicate the order of it all coming together as well as the references.  In the graphic the arrow lines illustrate the dependancies between the projects.

Also, I make reference to the sample code throughout this post.  Download link is at the bottom.

Step 1: Define Contracts

The first step is to define the contracts in your contracts assembly and DTOs in your DTO’s assembly.

Your contract should be one interface per kind of add-in you will support.  These interfaces need to inherit System.AddIn.Contract.IContract and be decorated with System.AddIn.Pipeline.AddInContractAttribute.

Types in the DTO that you wish to pass over the boundary will need to be serializable.

Jason He’s example of a Paint.NET Add-In (link below) takes a different path, by adding the extra types in the Add-In View, adding more wrappers.  His approach may have advantages that I have not discovered yet, but it reminds me of some of the dancing you have to do when for example you have the same type in a Web Reference and a binary reference in the same project. 

My preference is then to make a behavior-free separate assembly that anyone can take a dependency on safely.  This is the reason for including a DTO project.  I also like having the DTO types there to do as a [DataContract] does in WCF.  This will allow the Add-In contract to remain static if the host side changes it’s implementations of the DTO equiv. types.

Change the project properties of the Contracts project to build to: ..\Pipeline\Contracts\

Also note, for types that take a dependency on the DTO project, set the Copy Local property of the reference to False.  This is to keep the pipleline path clean.  i.e. we don’t actually want a copy of the DTO assembly in the \pipeline\contracts\ path!  This will slow our discovery and generate warnings as the Add-In infrastructure tries to examine it for contracts.

Step 2: Host View and Add-In View

Add two new assemblies:  host view and add-in view
Each has a public abstract class that mirrors the contract interface but does not reference it.

Host view does not even need a reference to System.AddIn.

When building the Add-In View, be sure to:

  • Apply System.AddIn.Pipeline.AddInBaseAttribute
  • Set the project to build to ..\Pipeline\AddInViews\

Step 3:  Host Adaptor and Add-In Adaptor

Add two more assemblies:  host adaptor and add-in adaptor

Add-in adapter will have one AddinToContractAdapter class for each Add-In type and the Host adapter has one ContractToHostViewAdapter class for each Add-In type.

The AddinToContractAdapter descends from System.AddIn.ContractBase and implements the contract for the Add-In

The ContractToHostViewAdapter descends from the Abstract class in the Host View for the Add-In.

Set the build output path of the Host Side adapter to: ..\Pipeline\HostSideAdapters\
And set the build output path of the Add-In Side adapter to: ..\Pipeline\AddInSideAdapters\

The purpose of these adapters is too marshal calls between the instances of your IContract's and should just look like wrapper code.

Step 4: Build the Add-Ins

Every Add-In needs to take a dependency on the Add-In View project and the DTO project.  Be sure to set their Copy Local = False in the reference properties.

Each Add-In also needs to descend from the abstract class in the Add-In View project, implement overrides, and to be decorated with System.AddIn.AddInAttribute.

The AddIn Attribute is the key to how the Add-In will be seen by the host so pay attention to the parameters on the constructor.

Set the build output path of each Add-In to: ..\Pipeline\AddIns\<addinName>\

I should mention that the Add-In's in the sample code below are just stubs to show the complete loop.  The Book Library Add-In returns empty objects of the prescribed types, the other two throw an Exception.  This is to show error handling.

Step 5:  The Add-In Host

Take a reference to the Host View and Dto projects, this time it is OK to have the reference Copy Local.

In the attached example the Book Library add-in returns empty objects of the designed type, however the other two (DVD Library and CD Library) throw an Exception for each operation. 

By the end of the process, the project will look like this:


Trouble shooting & Error Handling

Just skiming over this topic, but it is wise to wire up a handler to AppDomain.UnhandledException for the app domain that will run your Add-Ins, and Application.ThreadException (Windows Forms examples) as well as wrapping calls into the Add-In with a Try... Catch block. 

If you are trying to work out why your AddIns are not being discovered, remember that:

  • AddInStore.Rebuild()
  • AddInStore.RebuildAddIns()
  • AddInStore.Update()
  • AddInStore.UpdateAddIns()

...all return an array of strings that can be examined to determine how the AddIn discovery went.

Source Code:

The source code for this example is available for download from the following link:

LibraryAddInDemoCode_deepdark.net.zip (152.81 KB)

It requires Visual Studio Codename Orcas Beta 1.

Summary:

There are a couple of steps to implementing the new System.Addin stuff, but none of it is hard!

...and considering that it replaces a lot of very untidy and error-prone Reflection code that we had to do in prior versions of the framework I think this is a great addition to the toolbox.

This post is not meant to be a hardcore reference, just the total of my experience with this technology to date.  If you have any feedback please don't hesitate to contact me by email or leaving a comment on this post.

Sources:

Listening to: The Chillout Sessions 3

Thursday, July 12, 2007 5:18:15 PM (AUS Eastern Standard Time, UTC+10:00)  #    Disclaimer  |  Comments [0]  |