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 HRESULT: 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.

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.

You can do this today, like in 10 minutes...

OpenSearch is a format for describing how your site is searched, and optionally for adding Search elements to other formats like RSS. 

Auto-discovery of OpenSearch description documents is done via a link in your HTML head like so:

<link rel="search" type="application/opensearchdescription+xml" title="My Site" href="http://www.mysite.com/open-search.xml" />

The OpenSearch xml document is interpreted in the browser to add extra search providers in the integrated search function as so:

The contents of the file can be short or it can be long.  A bare bones example may be as simple as this:

<?xml version=”1.0” encoding=”UTF-8”?>
<OpenSearchDescription>
   <ShortName>My Site</ShortName>
   <Description>Search My Site</Description>
   <Url type="text/html" template="http://www.mysite.com/search.aspx?terms={searchTerms}"/>
</OpenSearchDescription>

The spec gives another simple, and one much more detailed example document.  The IE blog also goes into some detail... but in true internet fashion, View Source is the quickest path to an example

Security?

The bad news is, as of current browsers at time of writing (IE 7.0.6000.16473, Firefox 2.0.0.4) it seems controls are quite lax around what search template you can include, I had no problems setting the URL in the search template as being a completely different site!   

While it would be completely hilarious to posion someone's search bar as a prank with the following three facts...

• IE stores the search settings here: HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\SearchScopes
• FireFox stores them in the filesystem: C:\Documents and Settings\<winprofile>\Application Data\Mozilla\Firefox\Profiles\<mozprofile>\searchplugins
• (sounds like google, not work safe)

I would ultimately like some control here.  I can't see anything in Group Policy for enforcing/allowing/denying new search providers.  I'd like to add one to all PCs for my internal Sharepoint site for example.

DasBlog

Tangentally, If you are looking for the file to edit to add a new link url to a dasBlog site it is:

\dasblogce\themes\*\homeTemplate.blogtemplate

Listening To:  Sonic Youth, Daydream Nation

This is the second post on Group Policy for web developers.  Part 1 was about managing the local Intranet Zone for your AD network.  This post will be based on a similar scenario.  Specifically, when issuing certificates from a local Certification Authority, like Microsoft Certificate Services that ships in Windows Server 2003.

Modern browsers give you a more pretty warning system than they did last year when you view a site that do not chain back to a Trusted Root CA.  This is still an important warning and we don't want to condition people into just clicking yes every time they see it.

The first thing you will need to do (and like the last post, a certain degree of domain administrative Godness is required...) is get the CA's certificate from the CA.  Using Microsoft Certificate Services, you want to choose the option highlighted below:

This will prompt you to save the certificate file.  Do this, then delete it when you are done. 

Next we need to install the certificate into our client machines.  We will need a GPO.  You may use the one from the previous example or make a new one. Again the place in your AD to create this will vary.  For smaller sites adding at the the top level is fine, but hands off the default policy.

Navigate to Security Settings -> PK policies ->Trusted Root CA's as in the screen below:

Right-click on the Trusted Root CA's container and choose Import.  Here, browse for the cert file saved in step one and you are done!

Finally, here is some linkage to much more detail about Certificates in AD.

Some sites that you may have on your internal network, such as Virtual Server console, Sharepoint, ASP.NET sites of your own creation, and so on, can require your AD credentials to log you on.  Typically the browser will prompt you for them unless you add the site to the Local Intranet zone. 

This quickly becomes cumbersome as the number of users grows.  Everyone will have to add the URL to their Local Intranet zone manually and that spells work.

Internet Explorer

All the IE Zones, including Local Intranet, can be administered by Group Policy.  Where in your AD you create your GPO will depend on the scale of your operation, for smaller sites a GPO at the domain level is not a bad choice.  I'd caution against editing your default domain policy, consider creating a new GPO just under it.

Edit the GPO and browse to the Site To Zone Assignment List, inside the Internet Control Panel \ Security Page settings:

  

The UI will let you add IP addresses, FQDN's or http/https addresses, and which zone they will belong to.

One nice side effect of this to watch for is the users can now not change their zones via the IE settings.

FireFox

For FireFox, you are going to have to create/maintain an all.js in the %installdir%\defaults\pref\ directory.

This is just a plain text file that can contain settings in the FireFox Javascript format.  For Integrated Authentication you will need to add the following line:

pref("network.negotiate-auth.trusted-uris", "comma seperated site list");

Listening To: Kruder and Dorfmeister, the K&D Sessions, part 1