MSSQL 2012 creating CLR triggers for WCF fails

问题:

I've created system that uses CLR triggers to connect to WCF server and notify it about changes in DB. It runs ok on SQL server 2008 R2. Now im trying to migrate on SQL Server 2012. To use WCF i need to load SMDiagnostics.dll assembly along the others. Ive checked that clr is enabled in db , and set trustworthy to be "on", ive disabled WCF debuging, ive checked that SQL server runs under Local System account so there is no problems with permissions. Now my problem is that when i run following command

IF  NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SMdiagnostics')
create assembly [SMdiagnostics]
from  'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication        Foundation\SMdiagnostics.dll'
with permission_set = unsafe
go

i receive following error

Warning: The Microsoft .NET Framework assembly 'smdiagnostics, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details. Msg 6586, Level 16, State 1, Line 2 Assembly 'SMdiagnostics' could not be installed because existing policy would keep it from being used.

SMdiagnostics.dll exists at specified path. As i understand its some kind of policy in SQL server 2012 or in GAC, however i cant find no policies about SMdiagnostics. Any ideas how to solve it? Thanks.

回答1:

We submitted an issue with Microsoft a month ago on the same problem. My guess is you are unable to load System.IdentityModel.dll which is a dependency of the System.ServiceModel assembly. Microsoft has indicated to us that while this worked in SQL 2005 and SQL 2008 that it is a known bug in SQL 2012 and they are not going to fix it till SQL 2014.

The fact that this has not become widely known yet is a bit of a surprise to me except that 2012 is still very new. But this means that you cannot use what I would say is Microsoft's best practice Interprocess Communication technology within SQL CLR (WCF), note that .NET remoting would be out also because it also uses the ServiceModel assembly.

I am hoping for greater clarification on what they would say we should use instead, I am looking into if there is a way to write a WSE based SQL CLR, but not very excited about the prospect.

I'm quite unhappy about this and hope that others raising their voice will point out that it is a real problem and should be considered unacceptable.

回答2:

Nathan Schoenack. Yes, http://support.microsoft.com/kb/2742595 fix problem with System.IdentityModel.dll but with System.Data.Services.dll i still get Error: CREATE ASSEMBLY for assembly 'System.Data.Services' failed because assembly 'microsoft.visualbasic.activities.compiler' is malformed or not a pure .NET assembly. Unverifiable PE Header/native stub. Thanx.

回答3:

SQL 2012 switched to framework version 4.0.30319. So by default you won't be able to load assemblies from older frameworks.

See MSDN thread for details.

Unfortunately one also cannot load the System.ServiceModel assembly from framework version 4 because it depends on Microsoft.VisualBasic.Activities.Compiler which is not a pure .NET assembly.

Script:

CREATE ASSEMBLY [System.ServiceModel]
AUTHORIZATION [dbo]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ServiceModel.dll'
WITH PERMISSION_SET = UNSAFE;

I always get the following error:

Msg 6544, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'Microsoft.VisualBasic.Activities.Compiler' failed because assembly 'Microsoft.VisualBasic.Activities.Compiler' is malformed or not a pure .NET assembly. 
Unverifiable PE Header/native stub.

回答4:

The issue with failing to CREATE ASSEMBLY for System.IdentityModel.dll for .NET framework 4.0 version in SQL Server 2012 has been resolved with a security hotfix recently released by .NET team.

MS13-004: Description of the security update for the .NET Framework 4 on Windows XP, Windows Server 2003, Windows Vista, Windows Server 2008, Windows 7, and Windows Server 2008 R2: January 8, 2013 http://support.microsoft.com/kb/2742595

My testing has shown that there is no issue on CREATE ASSEMBLY with .NET 4.5 assembly of System.IdentityModel.dll in SQL Server 2012.

Nathan Schoenack SQL Support Technical Lead