none
SQL server 2012 loading SMDiagnostics assembly RRS feed

  • Question

  • Hi

    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? Or how to use WCF from SQL server 2012 CLR triggers?

    Thanks

    Wednesday, May 9, 2012 12:22 PM

Answers

All replies

  • Hi Shemshurenko,

    Since one reason for this error is that the CLR version does not support assemblies built using previous versions of CLR. Please check the CLR version which you want to use to build assemblies. SQL Server 2012 now uses version 4.0 of the CLR although previous versions of SQL Server (from SQL Server 2005) used version 2.0 of the CLR.

    For more information, please see this blog “SQL Server 2012 - SQLCLR .NET Framework Version”, and this thread.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedbackhere.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Thursday, May 10, 2012 4:28 AM
    Moderator
  • Hi Iric.

    Thanks fro your response.

    Still it does not make sense. Ive run following command select * from sys.dm_clr_properties 

    It says that CLR version is 4.0.30319.

    So ive tried to load SMDiagnostics.dll from same framework. 

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

    The result was the same.

    Thursday, May 10, 2012 1:13 PM
  • Ive managed to insert SMDiagnostics into db.

    It appears i had forgot to set db to trustworthy.

    Now i have different problem.

    Im doing all this things to use WCF from CLR trigger.

    So i need several assemblies in order to use WCF , i loaded several until i stumble on this one System.IdentityModel.dll

    When i try to load it i receive this error

    Warning: The Microsoft .NET Framework assembly 'system.identitymodel, version=4.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 6218, Level 16, State 2, Line 2
    CREATE ASSEMBLY for assembly 'System.IdentityModel' failed because assembly 'System.IdentityModel' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
    [ : System.IdentityModel.SafeKeyHandle::SafeCryptImportKey][mdToken=0x600014c][offset 0x00000033] Unmanaged pointers are not a verifiable type.
    [ : System.IdentityModel.SecurityBufferDescriptor::.ctor][mdToken=0x600024b][offset 0x00000017][found Native Int][expected unmanaged pointer] Unexpected type on the stack.
    [ : System.IdentityModel.SafeDeleteContext::InitializeSecurityContext][mdToken=0x6000254][offset 0x0000033F][found Native Int][expected address of Native Int] Unexpected type on the stack.
    [ : System.IdentityModel.SafeDeleteContext::InitializeSecurityContext][mdToken=0x6000254][offset 0x000000A7][found address of value 'System.IdentityModel.SecurityBufferStruct'][expected address of Native Int] Unexpected type on the stack.
    [ : System.IdentityModel.SafeDeleteContext::InitializeSecurityContext][mdToken=0x6000254][offset 0x000000B2][found address of Native Int] Expected numeric type on the stack.
    [ : System.IdentityModel.SafeDeleteContext::InitializeSecurityContext][mdToken=0x6000254][offset 0x00000338][found Native Int][expected address of Native Int] Unexpected type on the stack.
    [ : System.IdentityModel.SafeDeleteContext::InitializeSecurityContext][mdToken=0x6000254][offset 0x000001CD][found address of value 'System.IdentityModel.SecurityBufferStruct'][expected address of Native Int] Unexpected type on the stack.
    [ : System.IdentityModel.SafeDeleteContext::InitializeSecurityContext][mdToken=0x6000254][offset 0x000001D2][found address of Native Int] Expected numeric type on the stack.
    [ : System.IdentityModel.SafeDeleteContext::InitializeSecurityContext][mdToken=0x6000254][offset 0x000001C1][found Native Int][expected address of Native Int] Unexpected type on the stack.
    [ : System.IdentityMode...


    Thursday, May 10, 2012 3:51 PM
  • Hi Alex,
     
    Using SQLCLR-unsupported assemblies is always tricky. Technically, all of WCF is untested and unsupported in SQLCLR. Did you recompile your user assembly (and all your assemblies that depend on it) with .NET 4.0 and catalog them all with permission_set = unsafe? You could be running into the next incantation of this issue http://www.sqlskills.com/BLOGS/BOBB/post/Installing-NET-35-on-a-SQL-Server-2005-machinebe-careful.aspx.
     
    Hope this helps, Cheers, Bob
    • Proposed as answer by yaphets Monday, May 14, 2012 1:55 AM
    • Marked as answer by Iric WenModerator Thursday, May 17, 2012 1:49 AM
    Thursday, May 10, 2012 4:02 PM
    Moderator
  • Hello

    Did you solve your problem? We are having the same problem and are getting exactly the same error message.

    Thank you

    Thursday, May 24, 2012 10:34 AM
  • The following article from Feb 23, 2013 seems to indicate that we may be out of luck all together, at least with a 4.0 solution:

    http://blogs.msdn.com/b/psssql/archive/2013/02/23/unable-to-register-net-framework-assembly-not-in-the-supported-list.aspx

    In summary:

    • There was a list of supported assemblies. (http://support.microsoft.com/kb/922672)
    • The assemblies required by WCF are not on that list.
      • This is why we have to “register” them manually with SQL Server
    • Mixed Mode assemblies are NOT supported in 2012
    • Some of the WCF assemblies became Mixed Mode in C# 4.0
    • The single hot fix mentioned in the thread* only addresses a single dll required (http://support.microsoft.com/kb/2742595)
      • Though given the additional error mentioned in the thread is not related to our code we may be safe
    • There may be some hope in .NET 4.5

    * The thread that pointed me to the above and mentioned the hot fix: http://social.technet.microsoft.com/Forums/sqlserver/en-US/6e0bf601-8e53-4946-9a82-b05d009cd705/unable-to-load-systemidentitymodeldll-assembly-into-sql-server-2012

    Wednesday, September 11, 2013 8:14 PM
  • I am currently attempting to get the earlier version of the Framework dlls to load into SQL Server 2012 which allegedly works...

    If I get any updates I'll post here...

    Wednesday, September 11, 2013 8:16 PM