May 29, 2019 at 5:28 pm
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.
May 29, 2019 at 5:40 pm
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:
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
May 29, 2019 at 6:24 pm
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
May 29, 2019 at 6:35 pm
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
May 29, 2019 at 6:35 pm
You need to put the source code for the assembly into your database project. It is part of the build.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 29, 2019 at 7:06 pm
@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?
May 29, 2019 at 7:06 pm
Duplicate comment.
May 29, 2019 at 7:52 pm
@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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 29, 2019 at 8:23 pm
@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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
May 29, 2019 at 9:21 pm
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply