MSSQL 2012 creating CLR triggers for WCF fails

Question

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.

Answer 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.