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

FAQ: How do I query Active Directory from SQL Server?

Euan Garden's BLOG — Thu, 22 Mar 2007 22:52:47 GMT

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

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

Mark Reen — Wed, 25 Apr 2007 20:29:34 GMT

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)

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

Igor Kovalenko — Wed, 25 Apr 2007 22:04:42 GMT

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.

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

Des Jacobsen — Fri, 25 May 2007 09:00:15 GMT

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.

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

Jian — Thu, 31 May 2007 22:04:53 GMT

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

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

Igor Kovalenko — Thu, 31 May 2007 22:29:50 GMT

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

-- 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

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

davidn@magenic.com — Fri, 21 Sep 2007 18:13:07 GMT

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:\MyApp\Bin\Debug\MyAssembly.dll

C:\MyApp\Bin\Debug\MyAssembly.dll.config

Thanks,

David.

DavidN@magenic.com

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

Igor Kovalenko — Sun, 23 Sep 2007 22:02:45 GMT

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:\MyApp\Bin\Debug\MyAssembly.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:\MyApp\Bin\Debug\.

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

Scott Marquardt — Thu, 04 Oct 2007 21:29:00 GMT

Very nice indeed.

Has anyone cobbled together a similar attribute-writable toolkit?

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

Scott Marquardt — Wed, 10 Oct 2007 04:18:23 GMT

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?

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

gjohnson — Tue, 30 Oct 2007 21:05:54 GMT

Scott did you find a solution to the null issue?

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

Scott Marquardt — Fri, 02 Nov 2007 00:55:16 GMT

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!

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

Scott Marquardt — Tue, 06 Nov 2007 19:59:04 GMT

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

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

Taylor Gerring — Thu, 06 Dec 2007 00:00:43 GMT

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.

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

Claudia — Mon, 10 Dec 2007 15:39:20 GMT

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!

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

Igor Kovalenko — Mon, 10 Dec 2007 23:22:21 GMT

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

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

Scott Marquardt — Wed, 12 Dec 2007 00:55:04 GMT

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

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

BJ — Mon, 14 Jan 2008 18:38:16 GMT

(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

-- 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

Any ideas?

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

Scott Marquardt — Thu, 23 Oct 2008 20:36:03 GMT

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?

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

Igor Kovalenko — Thu, 23 Oct 2008 20:52:14 GMT

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.