April 9, 2014 at 4:09 pm
Have the steps associated with the CLR Verifier in SQL 2012 changed relative to 2008R2??
I'm looking at an assembly that I know up front has unmanaged pointers, so I have to register it as "PERMISSION_SET = UNSAFE" in SQL 2008R2. Now, migrating this to 4.0, those assemblies (which as best I can tell are simply the same source recompiled/relinked against the 4.0 libraries), which still have unmanaged pointers, willl *not* pass CLR Verification even with PERMISSION_SET = UNSAFE.
The assemblies aren't mixed mode, and as best I can tell from perusing ILDASM, they are all-but identical but for the 2.0/4.0 version difference. I have all the relevant permissions and settings for unsafe assemblies - trustworthy on, sysadmin role. But it looks for all the world like the CLR Verifier is simply ignoring the PERMISSION_SET = UNSAFE qualifier.
Curiously, I ran PEVERIFY /IL and PEVERIFY /MD against the 2.0 and 4.0 versions of these assemblies, and they generate nearly identical results. While I realize the CLR Verifier isn't literally firing PEVERIFY, it must be doing something substantially similar (persumably through reflection?), and for all the world it makes me think the UNSAFE tag should be the equivalent of PEVERIFY /MD, which both assemblies pass.
Bottom line, the only failures I see from this assembly appear to be those that should be overcome by PERMISSION_SET = UNSAFE, just as it has worked in the past. Has something changed in the verification process for Sql 2012?
Thanks in advance.
-David
April 9, 2014 at 4:14 pm
P.S. Can anyone recommend a really good SQL Server internals book that really dives deep into the internals of the SQLCLR?
April 9, 2014 at 8:50 pm
OklahomaDave (4/9/2014)
P.S. Can anyone recommend a really good SQL Server internals book that really dives deep into the internals of the SQLCLR?
+1 I have looked and not found much, and really want to know. Have you checked out the SQL Server Developer Training Kit samples for 2008 (the R2 one includes the 2008 stuff, and it has some training there for SQLCLR but I'm not sure how comprehensive it is).
That aside I'm really impressed with your understanding of assemblies. Are you a developer? Understanding GAC and assemblies and everything else beyond "I compiled and got a DLL and it works most of the time" is something I am severely under-trained in.
April 9, 2014 at 9:49 pm
Thanks for the kind words! 🙂
Yes, I'm a developer, going back a loooong way to pre-.NET days, writing a lot of T-SQL, ASP.NET, and C# stuff. Moreover, I just love the potential of CLR-based routines in SQL Server. I think they're really underused, primarily because I think the majority of DBA's (through no fault of their own, mind you) just hesitate to risk sanctioning them. The irony is that many of those same DBA's over the years probably didn't object to the old extended stored procs (xp_) that could be written in C++ and wreak all manner of havoc.
Properly designed CLR routines can be a tremendous boost. It's just a matter of using the tool properly. As an example, I get frustrated at the relative impotence of the T-SQL "LIKE" operator, so I rolled a very simple CLR function to perform basic regular expression matching - nothing fancy - just pass a string and a regexp, and you get back a 1 if the pattern matches, a 0 if it doesn't. It turned a query that took over a minute to run with a LIKE clause into one that now only takes 8 seconds.
The assembly that sparked my question is quite a bit more complicated, and I thought surely someone better versed in the deep, dark secrets of SQLCLR validation might have more specific insights - or surely someone had written an authoritative text on it. Looks like perhaps neither is necessarily so....who knows, I know enough just to be dangerous. Maybe I should try writing one LOL 🙂
April 10, 2014 at 8:26 am
I'm not aware of any big breaking changes. Are you using any assemblies outside of the supported ones?
Not sure of the exact message you're getting, but the first thing that jumps out would be that the assemblies you're referencing have had changes between 2.0 and 4.0 and one or more of those makes one of the assemblies incompatible.
There are quite a lot of things that just don't work in SQL CLR, it's not a 100% compatible with the framework and they wouldn't document any breaking changes that are in unsupported assemblies.
For e.g. SQL CLR only supports pure .NET assemblies but some assemblies may have changed from native to mixed between .NET versions.
April 11, 2014 at 7:17 am
Hi, Howard, and accept my thanks for taking the time to reply.
Yes, we are using assemblies outside of the "blessed" list 🙂
This project started as a 32-bit assembly under SQL2005 several years ago, and has been running happily even through the initial 64-bit migration into SQL2008, so while I realize we're skating on technically unsupported territory, I'm still not quite seeing why this project shouldn't work.
Your points about mixed-mode assemblies and 2.0-to-4.0 transitions are well taken; however, the 4.0 support assemblies needed for this project are not mixed mode. Not even the CLR Verifier indicates this. The Verifier, just as PEVERIFY.EXE, reports the same kinds of errors that the previous assembly (which registered as UNSAFE and has worked) generates, and are ignored with PERMISSION_SET = UNSAFE. I would expect to get something along the lines of an "Unverifiable PE header/native code stub" error for a mixed-mode assembly, but I'm not.
That's why I question the semantics of PERMISSION_SET = UNSAFE in SQL2012. At a naive, utterly simplistic level, it seems that the CLR Verifier is simply ignoring this clause for some unknown reason.
April 11, 2014 at 4:05 pm
One bit of clarification I would like to add:
I am attempting to register the 64-bit version of a DLL that registers and works normally in SQL2008R2 with PERMISSION_SET = UNSAFE. The *same physical DLL* fails to register under SQL2012 with the same permissions.
It looks for all the world like SQL2008 fired a PEVERIFY /IL for safe/external assemblies, and a PEVERIFY /MD for unsafe assemblies. Looks like SQL2012 is doing a PEVERIFY /IL for ALL assemblies, safe or unsafe.
Amid all my testing and efforts I hadn't made this aspect clear, and actually came across it today - absolutely certain that registration of the same DLL in the two different versions of SQL Server (2008R2 and 2012) yield different results. Would really like to know what's changed.
Thanks!
April 12, 2014 at 8:35 am
At this point I would suggest contacting Microsoft regarding this issue rather than relying on volunteers who may not have experienced the same problem as you are having at this moment.
April 12, 2014 at 10:26 am
Lynn Pettis (4/12/2014)
At this point I would suggest contacting Microsoft regarding this issue rather than relying on volunteers who may not have experienced the same problem as you are having at this moment.
Thanks, Lynn. That's clearly an option at this point - I just tend to think of myself as trailing behind other folks who have already trod the ground I'm crossing. Looks like I'm a loner out here 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply