Playing with DMF in Katmai

I've long been anticipating the Declarative Management Framework (DMF, not to be confused with dynamic management functions!) in Katmai. We were given an early peek at the product in March, but it was essentially SQL 2005 with a new @@version and slightly modified splash screen.

DMF allows you to create policies on your server; the prototypical example is to prohibit anyone from creating a table on your server with the prefix 'tbl'… Celko would be a big advocate of that policy I'm sure!

Anyway, I ran across a very early stumbling block when using this feature, and want to spread the word because I'm sure I won't be the last.

I installed Katmai on a clean server, and tried playing with this feature. Realizing that CLR was in use, I made sure that the service account was a privileged enough user, and enabled CLR using sp_configure. I created a very simple policy, using Enforce, with the following condition:

Multipart Name Facet : Name NOT LIKE 'tbl%'

I then tried the following statement:

CREATE TABLE dbo.tblFoo(bar INT);

As expected, I got an error. Unfortunately, the error was much more verbose than I was hoping:

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.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 got some information from Anand Doshi at Microsoft about how to get around this error. Basically, the Microsoft.SqlServer.DmfSqlClrWrapper.dll is only partially signed, and fails policy evaluation. To work around the issue, you can disable strong name validation for this assembly using the following command line:

"%ProgramFiles%\Microsoft Visual Studio 8\SDK\v2.0\Bin\sn.exe" -Vr Microsoft.SqlServer.DmfSqlClrWrapper,89845DCD8080CC91

After restarting SQL Server, the policy automation should now work.

Dan Jones explains more in the MSDN Forums:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1704822&SiteID=1&mode=1

And in his blog:
http://blogs.msdn.com/dtjones/archive/2007/06/06/sql-server-2008-june-ctp-bug.aspx