다음을 통해 공유

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

  • Anonymous
    March 22, 2007
    There are several ways but the most commonly used approach of using linked servers hits ona 1000 row

  • Anonymous
    April 25, 2007
    The comment has been removed

  • Anonymous
    April 25, 2007
    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.

  • Anonymous
    May 24, 2007
    The comment has been removed

  • Anonymous
    May 31, 2007
    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

  • Anonymous
    May 31, 2007
    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

  • Anonymous
    September 21, 2007
    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

  • Anonymous
    September 23, 2007
    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.

  • Anonymous
    October 04, 2007
    Very nice indeed. Has anyone cobbled together a similar attribute-writable toolkit?

  • Anonymous
    October 09, 2007
    The comment has been removed

  • Anonymous
    October 30, 2007
    Scott did you find a solution to the null issue?  

  • Anonymous
    November 01, 2007
    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!

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

  • Anonymous
    December 05, 2007
    The comment has been removed

  • Anonymous
    December 09, 2007
    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!

  • Anonymous
    December 10, 2007
    Claudia, I have no idea. I only know how to convert BigInt (like LastLogon) to date.

  • Anonymous
    December 11, 2007
    Taylor, that's great. Worked perfectly. Thanks so much!

  • Anonymous
    January 14, 2008
    The comment has been removed

  • Anonymous
    October 23, 2008
    The comment has been removed

  • Anonymous
    October 23, 2008
    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.

  • Anonymous
    September 14, 2010
    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.

  • Anonymous
    August 05, 2014
    The comment has been removed