SQL71521:A method with a matching signature was not found in the loaded assembly

  • Michael Freel

    SSC Enthusiast

    Points: 129

    I have recently inherited a SQL 2012 database that contains assemblies used to query Active Directory.  In my efforts to add the database to source control and deployable via dacpac,  I have been unable to get past a build error for a stored procedure referencing the assemblies.  I'm building the SSDT project using VS2017 and SQL Server Data Tools 15.1.61903.01040.  The stored procedure works fine on the server.  I'm aware you can avoid the assembly for this purpose and query Active Directory directly via linked server but this code is currently in production so I'd like to have it in source control.

    This is the procedure:

    CREATE PROCEDURE [dbo].[usp_GetUserADGroups]

    @Login NVARCHAR (100)

    AS EXTERNAL NAME [ActiveDirectory].[ActiveDirectoryLibrary.ADFunctions].[GetUserADGroups]

    The build error is:

    Error SQL71521: A method with a matching signature was not found in the loaded assembly.

    Other details:

    The referenced assembly is set to build and the reference tied to it is set to Model Aware = True.

     

    I've been unsuccessful trying to find a solution on the web so if anyone could offer any insight it would be much appreciated.

     

  • Solomon Rutzky

    SSCoach

    Points: 16129

    Do you have the source code of the Assembly? This error is coming from SSDT, not from SQL Server, so either it doesn't agree with the signature based on the .NET code in the same project, or basic on what it can inspect from an Assembly that it extracted from the server (if you don't have the original code and did a DB import).

    If you script the usp_GetUserADGroups stored procedure in SSMS, does the definition match what you posted in the original message here? You are looking for the input parameter datatype (will either be SqlString or SqlChars) and the namespace name, class name, and method name of this method. Based on the EXTERNAL NAME you posted, it should be:

    • Assembly Name (in sys.assemblies) = ActiveDirectory
    • namespace name = ActiveDirectoryLibrary
    • class name = ADFunctions
    • method name = GetUserADGroups

     

     

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Michael Freel

    SSC Enthusiast

    Points: 129

    Thank you for the response.  The definition of the stored procedure on the server adds one line:

    CREATE PROCEDURE [dbo].[usp_GetUserADGroups]

    @Login[nvarchar](100)

    WITH EXECUTE AS CALLER

    AS

    EXTERNAL NAME [ActiveDirectory].[ActiveDirectoryLibrary.ADFunctions].[GetUserADGroups]

    GO

    The project definition was extracted from that server.  The results of select * from sys.assemblies where name = 'ActiveDirectory' and

    select * from sys.assembly_modules where assembly_class = 'ActiveDirectoryLibrary.ADFunctions' are:

    name principal_id assembly_id clr_name permission_set permission_set_desc is_visible create_date modify_date is_user_defined

    ActiveDirectory 1 65568 activedirectorylibrary, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil 3 UNSAFE_ACCESS 1 2017-09-19 03:15:37.603 2017-09-19 03:15:37.603 1

    object_id assembly_id assembly_class assembly_method null_on_null_input execute_as_principal_id

    919674324 65568 ActiveDirectoryLibrary.ADFunctions IsUserInGroup 0 NULL

    935674381 65568 ActiveDirectoryLibrary.ADFunctions IsUserInGroup 0 NULL

    999674609 65568 ActiveDirectoryLibrary.ADFunctions GetUserADGroups 0 NULL

    1015674666 65568 ActiveDirectoryLibrary.ADFunctions GetUserADGroups 0 NULL

    1079674894 65568 ActiveDirectoryLibrary.ADFunctions IsUserInGroup 0 NULL

  • Michael Freel

    SSC Enthusiast

    Points: 129

    It also references System.DirectoryServices created from:

    CREATE ASSEMBLY [System.DirectoryServices]

    AUTHORIZATION [dbo]

    FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.DirectoryServices.dll'

    WITH PERMISSION_SET = UNSAFE

  • Phil Parkin

    SSC Guru

    Points: 243686

    You need to put the source code for the assembly into your database project. It is part of the build.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Michael Freel

    SSC Enthusiast

    Points: 129

    @Phil Parkin When you say put the source code into the project, do you mean the dll?  They exist in the assemblies folder.  Or do you mean the actual project that builds the dll?

     

  • Michael Freel

    SSC Enthusiast

    Points: 129

    Duplicate comment.

    • This reply was modified 4 months, 2 weeks ago by  Michael Freel.
  • Phil Parkin

    SSC Guru

    Points: 243686

    Michael Freel wrote:

    @Phil Parkin When you say put the source code into the project, do you mean the dll?  They exist in the assemblies folder.  Or do you mean the actual project that builds the dll?  

    I have only ever used CLR assemblies which I have written myself (or copied from someone cleverer!). In these cases, the actual source code was in the database project and the build process would also handle the creation of the assembly.

    I have no experience of database projects which reference third party assemblies & therefore cannot help you with that part. I would be interested to hear how you solve this.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Solomon Rutzky

    SSCoach

    Points: 16129

    Michael Freel wrote:

    @Phil Parkin When you say put the source code into the project, do you mean the dll?  They exist in the assemblies folder.  Or do you mean the actual project that builds the dll?  

     

    The DLL / assembly is not source code, it is the compiled / runnable form of the code.

    Having the original C# or VB.NET (there are a few other languages, but those 2 are by far the most common) code is ideal, of course. But, SSDT does allow for importing a DB, including any assemblies in it, so I have to believe that this scenario (i.e. inspecting the assembly to get the signature(s)) has been dealt with before.

     

    Have you tried changing the "Model Aware" and "Build Action" options to see if that helped? I would at least try making Model Aware "false", and then try "Build Action" of "None" with both values for "Model Aware"...any change in the outcome?

     

    The "EXECUTE AS CALLER" is the default, so I wouldn't worry about that minor difference. Besides, I don't think that is considered part of the signature. It's a property of the stored procedure.

     

    I do find it interesting that one method shows up twice, and another method shows up 3 times, in sys.assembly_modules. Execute the following:

    SELECT OBJECT_NAME(am.[object_id]) AS [ModuleName], *
    FROM sys.assembly_modules am
    LEFT JOIN sys.parameters pm
    ON pm.[object_id] = am.[object_id]
    WHERE am.[assembly_class] = N'ActiveDirectoryLibrary.ADFunctions';

    Does it return anything helpful?

     

    Take care,

    Solomon...

     

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Michael Freel

    SSC Enthusiast

    Points: 129

    Solomon Rutzky wrote:

    SELECT OBJECT_NAME(am.[object_id]) AS [ModuleName], *
    FROM sys.assembly_modules am
    LEFT JOIN sys.parameters pm
    ON pm.[object_id] = am.[object_id]
    WHERE am.[assembly_class] = N'ActiveDirectoryLibrary.ADFunctions';

    This query returns a list of user defined functions (which do not complain on build so far) and the two stored procedures that give the same build error (SQL71521:A method with a matching signature was not found in the loaded assembly).

    Here's the row relevant to the procedure:

    ModuleName object_id assembly_id assembly_class assembly_method null_on_null_input execute_as_principal_id object_id name parameter_id system_type_id user_type_id max_length precision scale is_output is_cursor_ref has_default_value is_xml_document default_value xml_collection_id is_readonly

    usp_GetUserADGroups 1950629992 65547 ActiveDirectoryLibrary.ADFunctions GetUserADGroups 0 NULL 1950629992 @Login 1 231 231 200 0 0 0 0 0 0 NULL 0 0

    As to putting Model Aware to False, that then gives me reference errors for the objects that call the assembly.  I have considered setting them all (assemblies, sprocs, and functions) to Build Action of None but then the code wouldn't be deployed if it ever changes without manual effort.

    As an exercise, I set the references to Model Aware = False and the Assemblies to Build Action = None.  I then needed to set to Build Action = None on all the dependent stored procedures and user defined functions.  I was then able to successfully build the project.  When I reverse those changes, the error returns.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply