How to avoid 1000 rows limitation when querying active directory (AD) from SQL 2005 with using custom code.


As all of you know it is possible to query active directory from SQL Server with using ADSI provider as linked server. This solution works fine until you will have a lot of users in active directory. According to best practice guide windows system engineers always configured AD to return no more then 1000 rows per one query. Of course you can avoid this limitation too (with using range keyword or some other ways, as for me require deep AD knowledge). Some other extremily complicated T-SQL scripts can be found in internet. My current post’s goal is to show two main things:


1. how to register a couple of assemblies (your own and related system assemblies) in SQL Server with UNSAFE permission WITHOUT setting TRUSTWORTHY ON for your database according to best practice security guide. Security check will passed in this way according to certificate permissions.


2. how to create a simple CLR procedures for quirying AD as any other SQL datasource without creating linked server.


You can find MSADHelper2.rar project attached. It contains project files; you can extract MSADHelper.dll assembly from here, or build a new one by yourself (in that case please generate a new strong key, my is not included in project files).


P.S. I am not an expert in C#, so you can improve this source as you wish to add some dispose or other required methods.


Here is installation script:


SET NOCOUNT ON
GO
USE Master
GO


–sp_configure ‘clr enabled’, 1
–reconfigure with override


–ALTER DATABASE <your_database_name> SET TRUSTWORTHY OFF
–GO


— create keys from assembly 


CREATE ASYMMETRIC KEY MSADHelperAsKey FROM EXECUTABLE FILE = ‘C:\distrib\ADSI\MSADHelper2.dll’ — specify correct path here.
GO
CREATE ASYMMETRIC KEY SystemDirectoryServicesKey FROM EXECUTABLE FILE = ‘C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll’
GO
–SELECT * FROM sys.asymmetric_keys
–GO


— create logins for special goals
CREATE LOGIN SQLCLRHelperLogin FROM ASYMMETRIC KEY MSADHelperAsKey
GO
CREATE LOGIN SQLCLRSysDirServLogin FROM ASYMMETRIC KEY SystemDirectoryServicesKey
GO


— grant necessary (UNSAFE) permissions
GRANT UNSAFE ASSEMBLY TO SQLCLRHelperLogin
GO
GRANT UNSAFE ASSEMBLY TO SQLCLRSysDirServLogin
GO


———————————————————————–

— **************************************************************

———————————————————————–


USE <your_database_name>
GO


CREATE ASSEMBLY [System.DirectoryServices]
FROM ‘C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll’
WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY [MsForClient.SqlServer.SqlClrToolkit.MSADHelper]
FROM ‘C:\distrib\ADSI\MSADHelper2.dll’ — please specify correct path here
WITH PERMISSION_SET = UNSAFE
GO


–Have a look at the assembly within the database
–SELECT * FROM sys.assemblies
–SELECT * FROM sys.assembly_files
GO


–Create procedures from the assembly


— return list of registered providers (from registry)
CREATE PROCEDURE dbo.usp_GetListOfRegisteredDirectoryProviders
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetListOfRegisteredDirectoryProviders
GO


— test methods – is it possible to create AD object? 


CREATE PROCEDURE dbo.usp_TryAuthenticate(@pAdsiPath nvarchar(4000), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_TryAuthenticate
GO


— by default any method will execute under SQL Server Service account. If this account don’t have enought privileges – please specify another one as @pUserName and


— @pPassword to access AD


CREATE PROCEDURE dbo.usp_TryAuthenticateAsUser(@pAdsiPath nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_TryAuthenticateAsUser
GO


— enum AD structure – be carefull, to much info!


CREATE PROCEDURE dbo.usp_FillInfoByPath(@pAdsiPath nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_FillInfoByPath
GO


— get AD object properties


CREATE PROCEDURE dbo.usp_GetNodeProperties(@pAdsiPath nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetNodeProperties
GO


— extract user list


CREATE PROCEDURE dbo.usp_GetUserList(@pAdsiPath nvarchar(4000), @pOutputFieldList NVarChar(2000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int, @pScope nvarchar(10))
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetUserList
GO


— extract group list


CREATE PROCEDURE dbo.usp_GetGroupList(@pAdsiPath nvarchar(4000), @pOutputFieldList NVarChar(2000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int, @pScope nvarchar(10))
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetGroupList
GO


— extract list of computers


CREATE PROCEDURE dbo.usp_GetComputerList(@pAdsiPath nvarchar(4000), @pOutputFieldList NVarChar(2000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int, @pScope nvarchar(10))
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetComputerList
GO


— extract members of AD group


CREATE PROCEDURE dbo.usp_GetGroupMembers(@pAdsiPath nvarchar(4000), @pOutputFieldList NVarChar(2000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetGroupMembers
GO


— enum user membership


CREATE PROCEDURE dbo.usp_GetUserMembership(@pAdsiPath nvarchar(4000), @pOutputFieldList NVarChar(2000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetUserMembership
GO


— execute & test section


EXEC dbo.usp_GetListOfRegisteredDirectoryProviders
GO


— try to check – if net framework carefully installed and AD objects created without any issue
DECLARE @pStr nvarchar(4000)
SET @pStr = N’LDAP://DC=HQ,DC=corp,DC=xxxxxxxx,DC=ru’
EXEC dbo.usp_TryAuthenticate @pStr, 1
GO
— try to run under another account


DECLARE @pStr nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100)
SELECT @pStr = N’LDAP://DC=HQ,DC=corp,DC=xxxxxxxx,DC=ru’, @pUserName = N’SQLSvc’, @pPassword = N’xxxxxxx’


EXEC dbo.usp_TryAuthenticateAsUser @pStr, @pUserName, @pPassword, 1
GO


— extract AD info as relational tree (use ID & ParentID to build object tree)
DECLARE @pStr nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100)
SET @pStr = N’LDAP://DC=HQ,DC=corp,DC=xxxxxxx,DC=ru’


EXEC dbo.usp_FillInfoByPath @pStr, @pUserName, @pPassword, 0
GO


— get all properites of object
DECLARE @pStr nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100)
–SET @pStr = N’LDAP://CN=User01,CN=Users,DC=HQ,DC=corp,DC=xxxxxxx,DC=ru’
SET @pStr = N’LDAP://CN=Domain Admins,CN=Users,DC=HQ,DC=corp,DC=xxxxxxxxxx,DC=ru’


EXEC dbo.usp_GetNodeProperties @pStr, @pUserName, @pPassword, 0
GO
—- get user list and attributes


DECLARE @pStr nvarchar(4000), @pOutputFieldList nvarchar(2000), @pUserName sysname, @pPassword nvarchar(100)
— to extract users from exact OU
— ‘LDAP://OU=TestGroup,DC=HQ,DC=corp,DC=xxxxxxxx,DC=ru’
— all users from AD
— @pStr = N’LDAP://DC=HQ,DC=corp,DC=xxxxxxx,DC=ru’
SELECT @pStr = N’LDAP://DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru’,
 @pOutputFieldList = ‘cn,name,description,distinguishedName,whenChanged,primaryGroupID,memberOf’, @pUserName = ”, @pPassword = N”


EXEC dbo.usp_GetUserList @pStr, @pOutputFieldList, @pUserName, @pPassword, 0, ‘Subtree’
GO


—- extract group list
DECLARE @pStr nvarchar(4000), @pOutputFieldList nvarchar(2000), @pUserName sysname, @pPassword nvarchar(100)
— to extract groups from exact group
— ‘LDAP://CN=Domain Admins,CN=Users,DC=HQ,DC=corp,DC=xxxxxxx,DC=ru’
— all groups from AD
— @pStr = N’LDAP://DC=HQ,DC=corp,DC=xxxxxx,DC=ru’
SELECT @pStr = N’LDAP://DC=HQ,DC=corp,DC=xxxxxx,DC=ru’,
 @pOutputFieldList = ‘cn,name,description,distinguishedName,whenChanged’, @pUserName = ”, @pPassword = N”


EXEC dbo.usp_GetGroupList @pStr, @pOutputFieldList, @pUserName, @pPassword, 0, ‘Subtree’
GO


—- get computer list
DECLARE @pStr nvarchar(4000), @pOutputFieldList nvarchar(2000), @pUserName sysname, @pPassword nvarchar(100)
SELECT @pStr = N’LDAP://DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru’,
 @pOutputFieldList = ‘cn,name,distinguishedName,whenChanged’, @pUserName = ”, @pPassword = N”


EXEC dbo.usp_GetComputerList @pStr, @pOutputFieldList, @pUserName, @pPassword, 0, ‘Subtree’
GO


——- get group members
DECLARE @pStr nvarchar(4000), @pOutputFieldList nvarchar(2000), @pUserName sysname, @pPassword nvarchar(100)
–SELECT @pStr = N’LDAP://CN=Domain Admins,CN=Users,DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru’,
— @pOutputFieldList = ‘sAMAccountName’
SELECT @pStr = N’LDAP://CN=Administrators,CN=Users,DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru’,
 @pOutputFieldList = ‘Name’, @pUserName = ”, @pPassword = N”


EXEC dbo.usp_GetGroupMembers @pStr, @pOutputFieldList, @pUserName, @pPassword, 0
GO


—– enlist user membership
DECLARE @pStr nvarchar(4000), @pOutputFieldList nvarchar(2000), @pUserName sysname, @pPassword nvarchar(100)
SELECT @pStr = N’LDAP://CN=Administrator,CN=Users,DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru’,
 @pOutputFieldList = ‘Name’, @pUserName = ”, @pPassword = N”


EXEC dbo.usp_GetUserMembership @pStr, @pOutputFieldList, @pUserName, @pPassword, 0
GO



— Here is a section of cleanup script


USE <your_database_name>
GO


— delete procedures
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetListOfRegisteredDirectoryProviders]’) AND type in (N’P’, N’PC’))
 DROP PROCEDURE [dbo].[usp_GetListOfRegisteredDirectoryProviders]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_TryAuthenticate]’) AND type in (N’P’, N’PC’))
 DROP PROCEDURE [dbo].[usp_TryAuthenticate]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_TryAuthenticateAsUser]’) AND type in (N’P’, N’PC’))
 DROP PROCEDURE [dbo].[usp_TryAuthenticateAsUser]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_FillInfoByPath]’) AND type in (N’P’, N’PC’))
 DROP PROCEDURE [dbo].[usp_FillInfoByPath]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetNodeProperties]’) AND type in (N’P’, N’PC’))
 DROP PROCEDURE [dbo].[usp_GetNodeProperties]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetUserList]’) AND type in (N’P’, N’PC’))
 DROP PROCEDURE [dbo].[usp_GetUserList]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetGroupList]’) AND type in (N’P’, N’PC’))
 DROP PROCEDURE [dbo].[usp_GetGroupList]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetComputerList]’) AND type in (N’P’, N’PC’))
 DROP PROCEDURE [dbo].[usp_GetComputerList]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetGroupMembers]’) AND type in (N’P’, N’PC’))
 DROP PROCEDURE [dbo].[usp_GetGroupMembers]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetUserMembership]’) AND type in (N’P’, N’PC’))
 DROP PROCEDURE [dbo].[usp_GetUserMembership]
GO


— delete assemblies
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N’MsForClient.SqlServer.SqlClrToolkit.MSADHelper’)
 DROP ASSEMBLY [MsForClient.SqlServer.SqlClrToolkit.MSADHelper]
GO


IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N’System.DirectoryServices’)
 DROP ASSEMBLY [System.DirectoryServices]
GO


USE Master
GO


— drop logins


IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N’SQLCLRSysDirServLogin’)
 DROP LOGIN [SQLCLRSysDirServLogin]
GO


IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N’SQLCLRHelperLogin’)
 DROP LOGIN [SQLCLRHelperLogin]
GO


— drop keys 


IF EXISTS(SELECT * FROM sys.asymmetric_keys WHERE [name] = ‘MSADHelperAsKey’)
 DROP ASYMMETRIC KEY MSADHelperAsKey
GO


IF EXISTS(SELECT * FROM sys.asymmetric_keys WHERE [name] = ‘SystemDirectoryServicesKey’)
 DROP ASYMMETRIC KEY SystemDirectoryServicesKey
GO


— Seems to me finished :-)


Dec 08, 2007 :


i updated source code to fix bug – many thanks to Taylor Gerring

MSADHelper2.rar

Comments (22)

  1. There are several ways but the most commonly used approach of using linked servers hits ona 1000 row

  2. Mark Reen says:

    I have been trying to use your process to query AD. When I first loaded everthing, It work fine. Then

    after about 4 hours it all stopped working. I am getting the error below. Any ideas on how to fix this issue?

    Mark R.

    Msg 6522, Level 16, State 1, Procedure usp_TryAuthenticate, Line 0

    A .NET Framework error occurred during execution of user-defined routine or aggregate "usp_TryAuthenticate":

    System.Runtime.InteropServices.COMException: The server is not operational.

    System.Runtime.InteropServices.COMException:

      at MsForClient.SqlServer.SqlClrToolkit.MSADHelper.usp_TryAuthenticate(String ProviderString, Int32 Secure)

  3. Igor Kovalenko says:

    Mark,

    this may only meen that your domain controller (AD) is unavailable ("Server is not operational"). Please check for network issues or possible AD issues. Buy the way tomorrow i am planning to make a deep test in special environment under pressure to reproduce the behavior to fall into situation as you described.

  4. Des Jacobsen says:

    Can I sugest you need one additional step in this procedure.

    Before you try to create the ASYMMETRIC KEYmake sure that the SQL Server service account has permision to the directory that your assemply is stored in. If you dont you get an error.

    The certificate, asymetric key, or private key file does not exist or has invalid format.

  5. Jian says:

    Thanks for this artical.  I am wondering if there is a way to get the AzMan store in AD or even directly authorize the user in Stored Proc.

    Thanks in advance!

    Jian

  6. Igor Kovalenko says:

    I may miss something but as for me it will be difficult to directly authorize users in stored proc. such kind of stored procedures. By default it works under security context of sql server account.

    You may try to use another approach. Firstly, get calling user windows loginname. Second. extract the members of the group which authorized user should belong to. the second, enum all members of the group to check if user is a member of the group.

    Something like this

    CREATE PROCEDURE usp_before_calling_AD

     @param1, @param2, @param 3 etc

    AS

    — get original caller

    DECLARE @usr as sysname

    SET @usr = ORIGINAL_LOGIN()

    — than extract your secret group members from AD:

    CREATE TABLE #temp (

       cn sysname )

    SET @pStr = N’LDAP://DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru’

    SET @pOutputFieldList = ‘cn’

    INSERT INTO #temp

    EXEC dbo.usp_GetUserList @pStr, @pOutputFieldList, NULL, NULL, 0, ‘Subtree’

    — and last step

    IF EXISTS(SELECT TOP 1 1 FROM #temp whete cn = @usr)

         SELECT ‘allowed user’

    ELSE

         SELECT ‘deny logon’

    RETURN

  7. Hi Igor,

    I was able to use your toolkit almost 100% outofthebox.  I do have a question.  My company required the all the LDAP paths to be passed store in a config file…How I make it read AD info from a MyAssembly.dll.config?

    Where is the physical location that I need to deploy my config file if my dll/config are residing in this structure

    C:MyAppBinDebugMyAssembly.dll

    C:MyAppBinDebugMyAssembly.dll.config

    Thanks,

    David.

    DavidN@magenic.com

  8. Igor Kovalenko says:

    Truly says i have no idea how to deploy this assembly with config file into SQL. Anyway you can try to use the following:

    – change source code and drop all parameters because of unnecessary.

    – define module internal variables (like former parameters)

    – to initiate parameters

        – open C:MyAppBinDebugMyAssembly.dll.config as simple xml file (with using System.XML XmlReader or XmlTextReader)

        – parse config file to extract necessary parameter’s value

    – call AD when ready.

    In this case you’ll call EXEC dbo.usp_GetUserList without parameters. Even more you can impersonate callers within function to read the different config files. The only check for appropriate permissions for folder C:MyAppBinDebug.

  9. Scott Marquardt says:

    Very nice indeed.

    Has anyone cobbled together a similar attribute-writable toolkit?

  10. Scott Marquardt says:

    Nuts.

    I’m finding a problem with this, and I can’t identify a culprit in the code.

    I have some schema extensions. When I run the user list and the attributes are returned, one of these custom attributes returns fine, until it comes to users for whom the attribute is a null. At that point, the populated attribute of the immediately prior user ends up being returned for all subsequent users for whom the attribute is a null. It’s as if there’s a variable somewhere that’s not getting reset, in the code. But I can’t find anything like that at all in the code.

    When the results get to another user with a populated attribute, things return to normal until another user with that attribute null comes along — then that user and any subsequent users with that attribute null "inherit" the previous user’s attribute.

    OK, more info. It does this with any attribute, not just custom attributes. I figured as much, but wanted to check.

    Could it be lines 651 to 657 in ADInfo?

  11. gjohnson says:

    Scott did you find a solution to the null issue?  

  12. Scott Marquardt says:

    No, alas. No joy. In fact, I nearly forgot about the bug until just this moment, when I returned to the method and was puzzled by something. Which led me back here in hopes that someone had resolved the bug, only to find that others wonder whether *I* had.   😉

    Darn the luck for us both!

  13. Scott Marquardt says:

    I still haven’t figured out why the code is doing this, though I’m beginning to suspect it ain’t the code.   (!)

  14. Taylor Gerring says:

    Scott, I think I have a solution to your problem! Around line 656 in ADInfo.cs you will see something like:

    //start code

    if (PropertyValues.Length > 0)

       record.SetSqlString(Count + 1, PropertyValues.Substring(0, PropertyValues.Length – 1));

    //end code

    Add the below ELSE to it, so the whole block looks like below:

    //start code

    if (PropertyValues.Length > 0)

       record.SetSqlString(Count + 1, PropertyValues.Substring(0, PropertyValues.Length – 1));

    else

       record.SetSqlString(Count + 1, SqlString.Null);

    //end code

    Re-build the DDL, run an ALTER ASSEMBLY to register the new DLL with SQL Server, and try your problematic query again. Instead of getting duplicate rows, this change now gives me a SQL NULL, as one would expect.

  15. Claudia says:

    This works very well, but I still have one Problem: I want to read also the Object ID which is a Byte Array in AD. How can I convert this in a varchar Format?

    Thank you for your help!

  16. Igor Kovalenko says:

    Claudia, I have no idea. I only know how to convert BigInt (like LastLogon) to date.

  17. Scott Marquardt says:

    Taylor, that’s great. Worked perfectly. Thanks so much!

  18. BJ says:

    (5 row(s) affected)

    Msg 6522, Level 16, State 1, Procedure usp_TryAuthenticate, Line 0

    A .NET Framework error occurred during execution of user defined routine or aggregate ‘usp_TryAuthenticate’:

    System.DirectoryServices.DirectoryServicesCOMException: A referral was returned from the server.

    System.DirectoryServices.DirectoryServicesCOMException:

      at MsForClient.SqlServer.SqlClrToolkit.MSADHelper.usp_TryAuthenticate(String ProviderString, Int32 Secure)

    .

    I am getting this when I tried to run the section that follows to test:

    EXEC dbo.usp_GetListOfRegisteredDirectoryProviders

    GO

    — try to check – if net framework carefully installed and AD objects created without any issue

    DECLARE @pStr nvarchar(4000)

    SET @pStr = N’LDAP://DC=HQ,DC=corp,DC=wmdomain.local,DC=ru’

    EXEC dbo.usp_TryAuthenticate @pStr, 1

    GO

    Any ideas?

  19. Scott Marquardt says:

    Whokay, here’s a new problem:

    http://www.sqlskills.com/blogs/bobb/post/How-do-you-shutdown-a-running-SQLCLR-appdomain.aspx

    Thus:

    >[Error] Script lines: 1-41 ————————-

    A .NET Framework error occurred during execution of user-defined routine or aggregate "usp_GetUserList":

    System.IO.FileLoadException: Could not load file or assembly ‘System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)

    System.IO.FileLoadException:

      at MsForClient.SqlServer.SqlClrToolkit.MSADHelper.usp_GetUserList(String ProviderStringPath, String OutputFieldList, String UserName, String Password, Int32 Secure, String Scope)

    Any ideas on fixing this? Alter Assembly doesn’t seem to do the trick. Total dropping and recreation doesn’t, either.

    Is there an approved assembly that could be referenced without code changes?

  20. Igor Kovalenko says:

    Scott, i am so sorry, on my mind something wrong EITHER with Net Framework OR with assembly’s strong key. As for me the best choice will be to drop assembly from SQL Server & GAC, rebuild source code locally, sign it with strong key, and register assembly to SQL Server again. GAC is not necessary. Now i am not an MSFT; have no chance to install Visual Studio to debug issues.

    Sorry again.

  21. Philippe says:

    Igor, thanks for this, it worked very well and I have been looking for something like that for quite some time. I have build a ssis package on the top of it that will populate tables with the data I need daily.

    There are a couple things like isDeleted or lockedOut that I cannot get, it returns NULLS but anyway, after automating the distinguishedName parameter and building a loop to get groups members for my groups, I get the bulk of what I need.

  22. Petar K. Georgiev says:

    GetUserList and other function can be tuned!

    1) DirectoryEntry de = result.GetDirectoryEntry(); line in each loop is not needed!

    The "result" object contains the property values already.

    result.GetDirectoryEntry() performs another network trip to the LDAP server!

    REPLACE:

     foreach (object Value in de.Properties[ADFieldList[Count]])

    WITH

     foreach (object Value in result.Properties[ADFieldList[Count]])

    Old way: I can list 10000 users for about 20-30 seconds.

    New way: the same happens for about 1-2 seconds!!! :)

    2) GetUserMembership returns only direct membership. You can use LDAP_MATCHING_RULE_IN_CHAIN to find all groups the user is member of (direct and indirect). Plus, this search is done by the LDAP server.

    msdn.microsoft.com/…/aa746475(v=vs.85).aspx