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:
As expected, I got an error. Unfortunately, the error was much more verbose than I was hoping:
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)
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:
After restarting SQL Server, the policy automation should now work.
Dan Jones explains more in the MSDN Forums:
And in his blog: