locked
Problem in registering external dll using Create Assembly RRS feed

  • Question

  • Hi,

       I have created CLR Trigger project using SQL Server Project. In that project i want to add external dll which is located in disk. For that first i have placed the external dll in one folder. Than i have placed all the supporting dlls for the external dll in that folder. Then using Create Assembly i tried to register it(i have give PERMISSION_SET=UNSAFE). But it gives the following error. I  have placed the system.web.dll also to the external dll folder. How to solve this problem.


    Warning: The Microsoft .Net frameworks assembly 'system.web, version=1.0.5000.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' you are registering is not fully tested in SQL Server hosted environment.

    Msg 6586, Level 16, State 1, Line 1

    Assembly 'System.Web' could not be installed because existing policy would keep it from being used.



    Thanks,
    Vinoth

    Thursday, August 18, 2005 5:27 AM

Answers

  • SQL Server 2005 hosts .NET Common Language Runtime 2.0 and does not support assemblies built using previous versions of the runtime.
    Unfortunately this scenario would not work and you would require to recompine your external dll using .NET CLR 2.0 (VS 2005) and update the references to 2.0 version of Sharepoint.dll and Web.dll.

    Thanks,
    -Vineet.

    Thursday, August 18, 2005 7:53 PM

All replies

  • This is most likely since you are trying to register a framework assemblies from 1.x version of the .NET Framework. You need to use 2.0 version of System.Web instead.

    I would like to understand why you want to register and use 1.0 version of System.Web inside SQL Server 2005.

    Thanks,
    -Vineet.
    Thursday, August 18, 2005 7:39 AM
  • Hi,

      When i use v2.0.50125 framework i got the following error message.
     

    Warning: The Microsoft .Net frameworks assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.directoryservices.protocols, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.runtime.remoting, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.runtime.serialization.formatters.soap, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.design, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.drawing.design, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.web.regularexpressions, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.serviceprocess, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Warning: The Microsoft .Net frameworks assembly 'system.configuration.install, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Msg 10300, Level 16, State 1, Line 1

    Assembly 'Microsoft.SharePoint' references assembly 'system.web, version=1.0.5000.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the curent database and retry your request.

     
      Because my external dll is created in v1.1.4322 which also refers Microsoft.Sharepoint dll. So i think it asks for system.web.dll v1.1.4322. If i use use system.web.dll of V1.1.4322 i got the following error message.

    Warning: The Microsoft .Net frameworks assembly 'system.web, version=1.0.5000.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' you are registering is not fully tested in SQL Server hosted environment.

    Msg 6586, Level 16, State 1, Line 1

    Assembly 'System.Web' could not be installed because existing policy would keep it from being used.

    please help me to solve this issue.

    Thanks,
    Vinoth


    Thursday, August 18, 2005 8:22 AM
  • SQL Server 2005 hosts .NET Common Language Runtime 2.0 and does not support assemblies built using previous versions of the runtime.
    Unfortunately this scenario would not work and you would require to recompine your external dll using .NET CLR 2.0 (VS 2005) and update the references to 2.0 version of Sharepoint.dll and Web.dll.

    Thanks,
    -Vineet.

    Thursday, August 18, 2005 7:53 PM
  • I am having the same problem. I am using Visual Studio 2005 and all my DLLs are from .NET Framework 2.0.

    How esle can I get around this problem?

    Wednesday, November 8, 2006 2:31 PM
  • I am also having the same problem.  I have an external dll which I can't register in SQL Server because it has a reference to System.DirectoryServices.  So when I create the assembly in SQL Server, I get...

    Msg 10301, Level 16, State 1, Line 2

    Assembly 'PermissionsCLR' references assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

    So I tried the following command to register System.DirectoryServices in SQL Server...

    CREATE ASSEMBLY SystemDirectoryServices FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'

    But I get the following error...

    Warning: The Microsoft .Net frameworks assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

    Msg 6218, Level 16, State 2, Line 1

    CREATE ASSEMBLY for assembly 'System.DirectoryServices' failed because assembly 'System.DirectoryServices' 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.DirectoryServices.DirectorySearcher::SetSearchPreferences][mdToken=0x60000f6][offset 0x0000050E][found address of value 'System.DirectoryServices.Interop.AdsSortKey'] Expected numeric type on the stack.

    [ : System.DirectoryServices.SearchResultCollection::RetrieveDirectorySynchronizationCookie][mdToken=0x60001c4][offset 0x00000023][found address of value 'System.DirectoryServices.Interop.AdsSearchColumn'] Expected numeric type on the stack.

    [ : System.DirectoryServices.SearchResultCollection::RetrieveVLVResponse][mdToken=0x60001c5][offset 0x00000023][found address of value 'System.DirectoryServices.Interop.AdsSearchColumn'] Expected numeric type on the stack.

    [ : System.DirectoryServices.SearchResultCollection+ResultsEnumerator::GetCurrentResult][mdToken=0x60001d2][offset 0x00000033][found address of Native Int] Expected numeric type on the stack.

    Any help is greatly appreciated.  Is there something else I need to do to register System.DirectoryServices in SQL Server?

    Friday, November 17, 2006 8:29 PM
  •  

    I am also having the same problem.  I have an external dll which I can't register in SQL Server because it has a reference to System.DirectoryServices.  So when I create the assembly in SQL Server, I get...

    Msg 10301, Level 16, State 1, Line 2

    Assembly 'PermissionsCLR' references assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

    Any help is greatly appreciated.  Is there something else I need to do to register System.DirectoryServices in SQL Server?

    Thursday, March 29, 2007 10:42 AM
  • Hi,
    I am having the same problem, does anybody have a solution?
    Thank you.


    Friday, November 9, 2007 6:53 PM
  •  

    Its been a while since I was troubleshooting this one, but I did find a resolution.  As I recall, if you follow the below script (taking out the things specific to our environment) you will be able to get a CLR procedure to run, even if it contains a reference to DirectoryServices.  One of the steps displays a warning about the security, but will still run it.

     

     

     

    --Drop all the things previously created

    USE Warehouse

    DROP FUNCTION Permissions

    DROP ASSEMBLY assembly1

    DROP LOGIN CLRLogin

    DROP ASSEMBLY SystemDirectoryServices

    USE master

    DROP ASYMMETRIC KEY asymmetrickey1

     

    --Create key pair file for signing. At a command prompt, call sn.exe (in the Visual Studio 8 bin folder)

    -- sn -k mykeypair.snk

    --Create Visual Studio project. Write class. Sign code (project properties) using the snk file created.

    --Then import the dll.....

    --Enable the CLR in the database

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'clr enabled', 1;

    GO

    RECONFIGURE;

    GO

     

     

    ALTER DATABASE Warehouse SET TRUSTWORTHY ON

    USE master

    CREATE ASYMMETRIC KEY asymmetrickey1 FROM EXECUTABLE FILE = 'E:\Databases\CLR\PermissionsCLR.dll'

    CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY asymmetrickey1

    GRANT UNSAFE ASSEMBLY TO CLRLogin --GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin

    USE Warehouse

    CREATE ASSEMBLY SystemDirectoryServices FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll' WITH PERMISSION_SET = UNSAFE

    CREATE ASSEMBLY assembly1 FROM 'E:\Databases\CLR\PermissionsCLR.dll' WITH PERMISSION_SET = UNSAFE --EXTERNAL_ACCESS

    CREATE FUNCTION Permissions(@username nvarchar(100))

    RETURNS TABLE ([Property_Code] [nchar](5))

    AS EXTERNAL NAME assembly1.[PermissionsCLR.Permissions].InitMethod

    SELECT * FROM Permissions(dbo.UserName()) order by property_code

    • Proposed as answer by MusakkhirSayyed Thursday, October 16, 2014 12:10 PM
    Friday, November 9, 2007 9:38 PM
  • Using 2.0, the UNSAFE dll I'm trying to register is my own with a single namespace therein which contains BOTH Sql-Clr

    functions/procedures as well as regular C# classes defined in the namespace and invoked by Sql-Clr members.

    Several problems, but though I would first attack the issues that look most troubling to "Create Assembly".

     

    1. Hbm.Listing.BusinessComponents.ListingSqlClrFunctions.IntListToBeConvertedToByteArray::ToByteArray][mdToken=0x6000020][offset 0x0000003C][found address of Byte]
    Expected numeric type on the stack.     And for the same function:

    [mdToken=0x6000020][offset 0x0000002F]
    [found Native Int][expected address of Byte] Unexp..

             

    public class IntListToBeConvertedToByteArray : List<int>

    {

    /// <summary>

    /// Convert base object's int List to a byte array.

    /// </summary>

    unsafe public byte[] ToByteArray()

    {

    if (Count == 0)

    return (new List<byte>()).ToArray(); // will non-null array having 0 Length.

    byte[] OutArray = new byte[4 * Count];

    // The following fixed statement pins the location of

    // the Outarray object in memory, protecting it from

    // relocation by garbage collection.

    fixed (byte* pDest = OutArray)

    {

    byte* pDestination = pDest;

    // transfer AS integer

    for (int i = 0; i < Count; i++)

    { //Array.Copy( BitConverter.GetBytes( thisIdea ), 0, OutArray, i*4, 4 );

    *((int*)pDestination) = thisIdea;

    pDestination += 4;

    }

    }

    return OutArray;

    }

    } // class

     

    2. Hbm.Listing.BusinessComponents.ListingSqlClrFunctions.AllFeatureNumGroupsOneListing::GetFeatureNumFromListing]
    [mdToken=0x6000018][offset 0x00000075]
    [found address of Single] Expected numeric type on the stack.

     

    The signature of the offending function is:

    unsafe public void GetFeatureNumFromListing(int FeatureGroupId, int FeatureNumIndex, out float Min, out float Max)

     

    If you can't call non-Sql-CLR signatures from within Sql-CLR functions, it forces one to write very huge self-contained Sql-CLR routines.  My sys-admin guy has been helping me to clear the Create Assembly roadblocks to UNSAFE.

     

    Any thoughts?????

     

    The context:  the query has up to 5 stages depending on the deserialized input received by Sql-CRL procedure.

    Hence, no back and forth between client and server as the initial subqueries results are successively pared down,

    then shaped/projected before return. All stages are performed on the server and at least TWO of the subqueries must be done by SQL-CLR since the data is stored in a packed/encoded format not supported by Sql data types or conversions.

     

    Mike Liddell

    App Architect Home Buyer's Marketing.

     

    Thursday, December 13, 2007 9:41 PM
  •  

    Baloo: thanks for posting this, I was having a lot of issues getting an unsafe assembly to run in my CLR sproc and this totally fixed it. This process is pretty convoluted, that whole ASYMMETRIC KEY thing I woulda never figured out. Well, I would've but I'm glad you figured it out for me Smile

    Wednesday, October 15, 2008 2:41 PM
  • Hi thanks for this solution but i was wondering if anyone could help expand on some of the instructions.

    particularly this one; how do you do that?, i know how to use visual studio but what class do i write? and what is this 'sign code' project properties instruction, i presume once you finish writing the class you set some project property and compile the dll?

     

    any help would be much appreciated.

    --Create Visual Studio project. Write class. Sign code (project properties) using the snk file created.


     

     

    Its been a while since I was troubleshooting this one, but I did find a resolution.  As I recall, if you follow the below script (taking out the things specific to our environment) you will be able to get a CLR procedure to run, even if it contains a reference to DirectoryServices.  One of the steps displays a warning about the security, but will still run it.

     

     

     

    --Drop all the things previously created

    USE Warehouse

    DROP FUNCTION Permissions

    DROP ASSEMBLY assembly1

    DROP LOGIN CLRLogin

    DROP ASSEMBLY SystemDirectoryServices

    USE master

    DROP ASYMMETRIC KEY asymmetrickey1

     

    --Create key pair file for signing. At a command prompt, call sn.exe (in the Visual Studio 8 bin folder)

    -- sn -k mykeypair.snk

    --Create Visual Studio project. Write class. Sign code (project properties) using the snk file created.

    --Then import the dll.....

    --Enable the CLR in the database

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'clr enabled', 1;

    GO

    RECONFIGURE;

    GO

     

     

    ALTER DATABASE Warehouse SET TRUSTWORTHY ON

    USE master

    CREATE ASYMMETRIC KEY asymmetrickey1 FROM EXECUTABLE FILE = 'E:\Databases\CLR\PermissionsCLR.dll'

    CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY asymmetrickey1

    GRANT UNSAFE ASSEMBLY TO CLRLogin --GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin

    USE Warehouse

    CREATE ASSEMBLY SystemDirectoryServices FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll' WITH PERMISSION_SET = UNSAFE

    CREATE ASSEMBLY assembly1 FROM 'E:\Databases\CLR\PermissionsCLR.dll' WITH PERMISSION_SET = UNSAFE --EXTERNAL_ACCESS

    CREATE FUNCTION Permissions(@username nvarchar(100))

    RETURNS TABLE ([Property_Code] [nchar](5))

    AS EXTERNAL NAME assembly1.[PermissionsCLR.Permissions].InitMethod

    SELECT * FROM Permissions(dbo.UserName()) order by property_code


    • Proposed as answer by MusakkhirSayyed Thursday, October 16, 2014 12:10 PM
    Monday, December 7, 2009 11:03 AM