locked
Attempting to Create an Assembly in SQL for VS Project but getting Errors RRS feed

  • Question

  • I am attempting to create an assembly

    create assembly "TEST" from c:\Test...

    I know that the framework version for SQL Server is v4.0.30319 which is what I have on my local machine as well.

    The project is in C#, VS 4.5.2. I can't use an earlier version than that and have the application run.

    I get the following error. What can I do to prevent the error?

    Msg 6211, Level 16, State 1, Line 3

    CREATE ASSEMBLY failed because type 'System.ComponentModel.Composition.Hosting.CompositionContainer' in safe assembly 'System.ComponentModel.Composition' has a static field 'EmptyProviders'. Attributes of static fields in safe assemblies must be marked  readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.



    • Edited by BartonL Monday, July 18, 2016 7:55 PM
    Monday, July 18, 2016 7:53 PM

Answers

  • You cannot have a static variable in a safe assembly (unless it is marked readonly apparently). If you want to play this gamble, you will need to mark the assembly as unsafe. Keep in mind that if you have a static variable in the assembly, it will be reset every time the AppDomain is unloaded and reloaded.

    It appears that this case, the static variable appears in a system assembly. I don't know if this assembly is white-listed by SQL Server in the first place (never heard of it before). But I think you need to investigate what this EmptyProvider is and what happens if the AppDomain is unloaded, so you can determine if you "only" need to create this assembly as UNSAFE, or if you need to reconsider your design more thoroughly.

    Monday, July 18, 2016 9:03 PM
  • What error do you get when you try to create your assembly (Test) as unsafe? There are additional requirements to create an unsafe assembly, see https://msdn.microsoft.com/en-us/library/ms345106.aspx

    When you create a user assembly in SQLCLR, SQL Server recursively walks the set of assemblies that your assembly references, including system assemblies. *Even if you don't specifically call anything* in each of these assemblies. The system assemblies have a *lot* of interconnected references, and that's why the assembly you site is included.

    The assembly mentioned in the error message is not on the list of supported assemblies, see https://support.microsoft.com/en-us/kb/922672

    See https://msdn.microsoft.com/en-us/library/ms131071.aspx for the implementation details and the reasoning behind SQLCLR security. SQLCLR has much more stringent security requirements that vanilla .NET. You may need to move this call out to the client/middle-tier.

    Tuesday, July 19, 2016 2:42 AM

All replies

  • You cannot have a static variable in a safe assembly (unless it is marked readonly apparently). If you want to play this gamble, you will need to mark the assembly as unsafe. Keep in mind that if you have a static variable in the assembly, it will be reset every time the AppDomain is unloaded and reloaded.

    It appears that this case, the static variable appears in a system assembly. I don't know if this assembly is white-listed by SQL Server in the first place (never heard of it before). But I think you need to investigate what this EmptyProvider is and what happens if the AppDomain is unloaded, so you can determine if you "only" need to create this assembly as UNSAFE, or if you need to reconsider your design more thoroughly.

    Monday, July 18, 2016 9:03 PM
  • I do not know for sure why the 'System.ComponentModel.Composition..dll  is even included. I think the reason is that there is an external call in SQL Server. I would like to call C# code which will add subscribers to MailChimp. I can't create the assembly called Test in an unsafe mode. Are you suggesting that I create an assembly for just System.ComponentModel.Composition?

    create function dbo.UFN_USR_UNC_MAILCHIMP_ADD_SUBSCRIBER
    (
      @LISTID nvarchar(50),
      @EMAILADDRESS nvarchar(200)
    )
    returns nvarchar(max)
    with execute as caller
    as external name [UNCSQL...].[UNC.CLRSQL.Catalog.UNCMailchimpAPI].[AddSubscriber]

    Monday, July 18, 2016 11:10 PM
  • What error do you get when you try to create your assembly (Test) as unsafe? There are additional requirements to create an unsafe assembly, see https://msdn.microsoft.com/en-us/library/ms345106.aspx

    When you create a user assembly in SQLCLR, SQL Server recursively walks the set of assemblies that your assembly references, including system assemblies. *Even if you don't specifically call anything* in each of these assemblies. The system assemblies have a *lot* of interconnected references, and that's why the assembly you site is included.

    The assembly mentioned in the error message is not on the list of supported assemblies, see https://support.microsoft.com/en-us/kb/922672

    See https://msdn.microsoft.com/en-us/library/ms131071.aspx for the implementation details and the reasoning behind SQLCLR security. SQLCLR has much more stringent security requirements that vanilla .NET. You may need to move this call out to the client/middle-tier.

    Tuesday, July 19, 2016 2:42 AM
  • I may have to do something different than using an assembly in SQL Server. I have not been able to get more information about the empty provider. Thanks so much for your help!
    Tuesday, July 19, 2016 1:43 PM
  • I get the message that I cannot make the assembly as unsafe. I am not sure if I will have to try something completely different. Thanks so much for your help. I have read the links in your message.
    • Edited by BartonL Tuesday, July 19, 2016 2:35 PM
    Tuesday, July 19, 2016 1:50 PM
  • What's the exact text of the error when you try and catalog the assembly as unsafe?

    You may have to use SQL DDL to make the database trustworthy (for testing *only*, use code signing and keys for production) for unsafe assemblies, VS auto-deploy feature won't do that for you. Here's a sample script for code signing (the key-generation and signing can be done in VS project settings) http://www.sqlskills.com/blogs/bobb/security-in-sql-server-2005-unsafe-assemblies-in-sept-ctp/


    Tuesday, July 19, 2016 2:57 PM
  • I have gotten instructions to get the project completed in a different way so I won't be using the SQL Create Assembly code. It doesn't seem that there is a good reasonable way to do it with an assembly.

    Please close this question. I couldn't see a way for me to do that. Thanks!


    • Edited by BartonL Tuesday, July 19, 2016 4:49 PM
    Tuesday, July 19, 2016 4:48 PM