Brendan Tompkins [MVP]

See
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q299410 for more
detailed information on how to perform a SQL distributed query by using
ADSI.

How do you get this to work if you want to have different aspaths. Lets say you have an application where you can walk around in the "AD hierarki" and you choose to output a report...

Anders,

This can get tricky. Usually you will be able to add the OU= tag with the specific OU that you are interested in, i.e.

''LDAP://DC=whaever,DC=domain,DC=org,OU=webusers''

But, I've had difficulties getting this to work properly. Honestly, navigating LDAP is pretty difficult stuff, and requires a lot of trial and error. Good luck!

Anders,

You could also of course, provide a stored proc that takes the LDAP connection string as a paramater, and return the query result. Not as good for doing joins with other tables, but still will get you the data you need.

Thank you Brendan!
I was kind of thinking that way but havent used stored procedures. I will have to try to write one and test. Thanks again for your quick answer.

Brendan! You dont have any Example-SP that concats the (Input Parameter) LDAP string into the openquery statement - im having some trouble here... /Anders

Ive now done one working Stored Procedure but without any DB Table join...I cant join it - how do I do? Heres the procedure:


CREATE PROCEDURE [dbo].[FTADQuery] AS
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE FTADQuery @Ldap nvarchar(500),
@ObjectCategory nvarchar(10),
@ObjectClass nvarchar(10),
@Attribute nvarchar(500),
@Scoope nvarchar(10)
AS

DECLARE @strSQL1 nvarchar(2000)


SELECT @strSQL1 = 'SELECT * FROM OpenQuery(ADSI, ' + CHAR(39) + '<' + @Ldap + '>;(&(objectCategory=' + @ObjectCategory +')(objectClass=' + @ObjectClass + '));' + @Attribute + ';' + @Scoope + CHAR(39) + ')'

EXEC (@strSQL1)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Anders, since you're using Dynamic SQL, you can do this two ways, as far as I'm aware.

1) Select into a #TEMP table, and do your join that way.

2) Add your join to your original Dynamic SQL like this. Assuming your SQL table is called USERS, and the AD Login is USERS.AD_LOGIN

SELECT @strSQL1 = 'SELECT ADResults.*, USERS.* FROM OpenQuery(ADSI, ' + CHAR(39) + '<' + @Ldap + '>;(&(objectCategory=' + @ObjectCategory +')(objectClass=' + @ObjectClass + '));' + @Attribute + ';' + @Scoope + CHAR(39) + ') ADResults LEFT OUTER JOIN USERS ON ADResults.sAMAccountName COLLATE SQL_Latin1_General_CP1_CI_AS = USERS.AD_LOGIN'

Brendan - thanks alot for your help!

Getting the following error when attempting to run a query against AD

An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

Believe it is to do with (') apostrophe's However every resource I look at including yours uses them in construcint strings as you would in SQL Server, any thoughts would be much appreciated.

Same problem here. I've tried evry which way and I still get

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

I also get this error. Any idea what to do??

Anders, Cameron, this is a bit out of my range of understanding of the ADSDSOObject. I did find this post here, which may help:

http://www.dbforums.com/archive/index.php/t-958399.html

Also If you're using exec, print out the sql query by replacing the EXEC (@strSQL1) with PRINT @strSQL1

Then copy what is printed out and run it directly in SQL Query Analyzer. If you have syntax errors in Query Analyzer, you can ususally work them out easier and then make your changes to your stored proc later. This seems to work well for me when I'm having trouble with dynamic SQL.. Sorry if this is old hat for you...

If you're sure that the syntax is correct, it may be a delegation problem if the active directory is Windows 2000+ only.

Try the same command from the console of the SQL server or using remote desktop to log onto the SQL server.

Hi Guys, can I join the party?
I have a SQL Server 2000 sitting on a W2000 Domain but the SQL box it not an AD Server. When I run the code above I get an error while preparing a query for execution against OLE DB 'ADSDSObject'.
Can someone tell me what I need to put into whaever, domain and org?

Anders,
I messed around with trying to pass variables to the LDAP query within SQL, as I believe you were trying to do. I found if was possible if you nested the apostrophes just right. For example, if you have a variable called @adspath which contains a proper LDAP path you can pass it to the inner SQL query thus:

set @adsiquery='(SELECT adspath from openquery
(ADSI, ''select ADsPath from '''''+@adspath+''''' WHERE objectclass=''''person''''''))'
exec (@adsiquery)

Took a bit of apostrophe counting to get right, but it did work for me. Hope this is relevant.

If your ADSI linked server is correctly set up and you still get the following error,

An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].


then I suggest you check the account under which your SQL server instance is running. Likely it is something like the local administrator account. In that case, running under local system will fix the issue. You may also choose to run your instance under a domain account if you need access to network resources as well.

Best of luck.

-MD-

If you change your SQL Startup Service accout to the local account and run the procedure from the server, it will work. I am trying to find a solution for the client side and will let you know.

Found it... From the server side change the SQL Startup to your Domain Account and on the linked server security tab, change the security to 'Being made without using security context'... This should work

Does anyone know where to find a list of AD fields/attributes that are available through an ADSI query? I have found only partial references in examples, but no complete list. Also, I was getting the 0x80040e14 error, and rebooting the server fixed it.

Prepare returned 0x80040e14...
This error is a syntax error.
Just check your query sytax and should be fine.

Through the tool ADSIedit.msc delivered by the Exchange Server

This works fine:
select * from openquery(adsi, 'select * from ''LDAP://WS01''')

This gives the 0x80040e14 error:
select * from openquery(adsi, 'select * from ''LDAP://WS01/OU=Teleservice,DC=e-resultive,DC=nl''')

Using SQL to query ADSI is useful until you hit the 1000 record barrier set in place by the ADSDSOObject. From ASP you use the PageSize and AbsolutePage properties to get past this.

Have you been able to pass the 1000 mark from within SQL?

Hermann,

No, I haven't found a good way of getting around this. One way, admittedly a hack, is to find some way to divide up the result set so that each select returns less than 1000 records. Then you can union your results before returning them from SQL Server

Hope this helps... Brendan

Brendan

What you've said made me think of method do achieve more than 1000. I started of by returning records by passing A* through Z* to the ADSI link and adding it to a temp table. I soon realized that in a big corporation there may even be more than a 1000 users with CNs starting with for example A. The same might be the case for AB*, etc.

Well I've ended up with quite a long script to retrieve all objects which will be best if stached in a table once a day or every few hours and then create a view from there.

The script should be fine for medium to big companies (1000-10000+). The only drawback with the script is that when 1000 records or more are found for say A*, it causes a tedious querying process. In this case, it takes the last records added to the temp table and locate the second character and then step trhough the rest of A from for example AB-AZ.

All the characters specified in @vchChars is in the order in which AD will sort them to make the search process sequencial. You will however not be able to retrieve records starting with (, ), * or a single quote for the provider does not allow the syntax. Even when you say cn=/(* which is the escape sequence provided by Microsoft.

The script returned 30251 records in 09:57 minutes. The test DC was only a P4 1.4GHz
Test users was A0001 up to A0998 plus A01250 to A02350 and [B-Z]0001 up to [B-Z]0998. It only returns enabled users.

Well enough blabbing, here is the script:

declare
@vchChars varchar(100)
, @vchTest varchar(10)
, @intLength tinyint
, @intStep tinyint
, @intSubStep tinyint
, @intCompPos tinyint
, @intRowCount smallint
, @intCycleCount smallint
, @intMaxCompare smallint
, @bitStepNext bit
, @bitSubStepNext bit
, @nvchSQL nvarchar(4000)
, @nvarOU nvarchar(200)
, @nvchDC nvarchar(100)
, @nvchCN varchar(256)



set @nvchDC = 'DC=your_domain,DC=com'
set @nvarOU = 'OU=SQL Test' --set equal to '' if not needed
set @vchChars = '-_`0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
set @intMaxCompare = 3

set nocount on

if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.#LDAP'))
drop table #LDAP

create table [dbo].[#ldap] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[cn] [nvarchar] (256) collate sql_latin1_general_cp1_ci_as null ,
[adspath] [nvarchar] (256) collate sql_latin1_general_cp1_ci_as null
) on [primary]



if len(@vchChars) > 0
begin
set @intStep = 1
while @intStep <= len(@vchChars)
begin
set @bitStepNext = 0
set @intCycleCount = 0
set @intSubStep = 2 --start comparison on second character
set @intCompPos = 1
set @intRowCount = 0
set @bitSubStepNext = 0

while @bitStepNext = 0
begin
if @intCycleCount = 0
set @vchTest = substring(@vchChars, @intStep, 1)
else
begin
select top 1 @nvchCN = cn from #ldap order by row_id desc
if @bitSubStepNext = 0
set @intCompPos = charindex(substring(@nvchCN, @intSubStep, 1), @vchChars, 1)

set @vchTest = substring(@vchTest, 1, (@intSubStep - 1)) + substring(@vchChars, @intCompPos, 1)


set @bitSubStepNext = 0
end

set @nvchSQL = 'insert into #ldap ' +
'select cn, adspath ' +
'from openquery(ADSI,''<LDAP://' + @nvarOU + ',' + @nvchDC + '>;(&(objectCategory=Person)(!(UserAccountControl:1.2.840.113556.1.4.803:=2))(cn=' + @vchTest + '*));cn, adspath;subtree'') q '+
'where not exists (select adspath from #ldap ld where ld.adspath = q.adspath)' +
'order by cn'

exec sp_executesql @nvchSQL

set @intRowCount = @@rowcount
print @vchTest + ' : ' + convert(varchar, @intRowCount) + ' : ' + convert(varchar, @intCompPos) --keep print after the @@rowcount statement or it will reset @@rowcount

if (@intRowCount < 1000 and @intCycleCount = 0) or ((@intCompPos = len(@vchChars) and @intCycleCount > 0 and (@intSubStep = @intMaxCompare or (@intSubStep = 2 and @intRowCount = 0))))
set @bitStepNext = 1
else
begin
set @intCycleCount = @intCycleCount + 1
if @intRowCount < 1000
begin
if (@intCompPos = len(@vchChars))
begin
set @intSubStep = @intSubStep - 1
if @intSubStep < 2
set @intSubStep = 2
end
else
begin
set @bitSubStepNext = 1
set @intCompPos = @intCompPos + 1
end
end
else
begin
set @intCompPos = 1
set @intSubStep = @intSubStep + 1
if (@intCompPos = len(@vchChars) and @intSubStep > 3)
begin
set @intSubStep = @intSubStep - 1
if @intSubStep < 2
set @intSubStep = 2
end
end
end
end
set @intStep = @intStep + 1
end

set nocount off

select * from #ldap order by cn
end
else
print 'No characters specified'

I missed a comment for @intMaxCompare:

set @intMaxCompare = 3 --this will set the maximum chars to compare for the last char in @vchChars
--if not set, the script will go into a never ending loop (only if last char has more than a 1000 which is not likely for Z)

Hermann. Great work. Much better than my solution. We have about 1300 objects in our AD store, so this works for me:

SELECT TOP 100 PERCENT Rowset_1.*, sn + ', ' + givenName AS FULL_NAME
FROM OPENQUERY(ADSI,
'SELECT title, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn, mail, userAccountControl FROM ''LDAP://DC=our,DC=domain,DC=org'' WHERE objectCategory = ''Person'' AND objectClass=''user'' and userAccountControl>1000')
Rowset_1
WHERE sn IS NOT NULL
UNION
SELECT TOP 100 PERCENT Rowset_1.*, sn + ', ' + givenName AS FULL_NAME
FROM OPENQUERY(ADSI,
'SELECT title, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn, mail, userAccountControl FROM ''LDAP://DC=our,DC=domain,DC=org'' WHERE objectCategory = ''Person'' AND objectClass=''user'' and userAccountControl<1000')
Rowset_1

It was a nice challenge to find a solution for.
However, it would make more sense to write and schedule a small application to retrieve all the info into a SQL table for the use with views. Much faster! I'm not sure of the ADO.NET syntax, but with classic ADO it would require the use of objCommand.Properties(@Page Size@) = 1000

For more info, have a look at:
http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx

Chiao,
Hermann

I also have the same problem

Being annoyed about the Reporting Services Execution Log just bringing up the do

Being annoyed about the Reporting Services Execution Log just bringing up the do

Being annoyed about the Reporting Services Execution Log just bringing up the do

Being annoyed about the Reporting Services Execution Log just bringing up the do

Being annoyed about the Reporting Services Execution Log just bringing up the do

Being annoyed about the Reporting Services Execution Log just bringing up the do

Being annoyed about the Reporting Services Execution Log just bringing up the do

Being annoyed about the Reporting Services Execution Log just bringing up the do

Being annoyed about the Reporting Services Execution Log just bringing up the do

Being annoyed about the Reporting Services Execution Log just bringing up the do

Being annoyed about the Reporting Services Execution Log just bringing up the do

Being annoyed about the Reporting Services Execution Log just bringing up the do

I use youre query to read the AD, but I would also know or a account is dissabled. For this I'm using userAccountControl. Most results come with a userAccountControl of NULL... How can I resolve this???

that's a nice query Hermann...but, you can also resolve the issue by changing your MaxPageSize within AD, which is what is limiting you to 1000 rows being returned...That's the route I'm hoping we can go here. :)

MaxPageSize is set to 1000 for good reason.  It stops queries from relentlessly hammering a domain controller for the returned information.  Instead of raising this value, it's always better to find ways to return the data.

I need some help with the problem where the LDAP query works fine when logged in to the SQL server, but not when run from any other server.  I cannot use the solution suggested above of making SQL start under my domain account, as this violates policies and best practices.  I feel like this is a somewhat useless restriction posed by Microsoft (both products are theirs).  What is the benefit to the security of a given box to NOT allow an outbound LDAP query?  Help me out?  

thanks

Hermann's example was certainly a good start for me, but I needed to add other columns to my table (title, mail, etc).  So I included the new definitions, but then the procedure generates an error saying that the number of fields doesn't match what's expected.  ???  I don't see why or where the number of fields is limited.  Why does this procedure care?  I'm probably missing something very simple I'm sure...

Hi, I had the same problem, 'till I found out I was using 'SELECT name FROM ''ldap://... (ldap in lowercase). When I changed to uppercase (LDAP://) it worked !!! I did the test a few times, and the error came back.

Hope this will help...

Dear friens,

I'm having a problem with ADSI and SQL... Could you tell me, where is the error? The problem surge when I try to use parameter…

"

CREATE PROCEDURE TEST

AS

DECLARE @charVariable nvarchar(11);

DECLARE @SQLString NVARCHAR(500);

DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */

SET @SQLString = N'SELECT * FROM OPENQUERY(ADSI,''SELECT * FROM ''''LDAP://DC=GrupoCGD,DC=com'''' WHERE objectClass = ''''user'''' AND sAMAccountName=@UserID'')'

/* Specify the parameter format one time. */

SET @ParmDefinition = N'@UserID varchar(11)';

/* Execute the string with the first parameter value. */

SET @charVariable = 'rhs0002';

EXECUTE sp_executesql @SQLString, @ParmDefinition,

                     @userID = @charVariable;

"

ERROR is:

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=GrupoCGD,DC=com' WHERE objectClass = 'user' AND sAMAccountName=@UserID" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

FOR EXAMPLE THIS VIEW WORKS…

USE [dbGestaoDesktop]

GO

/****** Object:  View [dbo].[AD_VW_DIR_Users]    Script Date: 11/22/2006 11:53:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[AD_VW_DIR_Users]

AS

SELECT     TOP (100) PERCENT cn, createTimeStamp,department, extensionAttribute1

FROM         OPENQUERY(ADSI, '

SELECT createTimeStamp,cn, department, extensionAttribute1

FROM ''LDAP://OU=DSO,OU=Utilizadores,OU=Servicos-Centrais,OU=cgd,DC=GrupoCGD,DC=com''

WHERE objectclass=''user''

')

                     AS MyTable

WHERE     (NOT (cn IS NULL))

ORDER BY createTimeStamp

THANKS

Can this be modified to add NDS also as a linked server?

This is very useful but we are banging our heads against the keyboards here trying to puzzle out the way to get a view that lists the users in their respective roles.  We have a list of users, we have a list of roles, but never the twain shall meet, so to speak.  Right now we are doing some rather cumbersome business layer coding to do row level security, but it would be so much better to do it on the data layer so we don't have to clog up the network with all that stuff that the user's aren't allowed to see anyway.  It would also make synchronizing our DB security to AD security much easier.

To fix the error "An error occurred while preparing the query"

In the linked server security settings use "Be made using this security context", entered Remote login: domain\user; With password: password.

If you dont specify the domain before the user name, this will not work.

You are a awesome.  I have struggled with how to execute my stored procedure from within a stored procedure for 2 days, and didn't event think to make it a view.

The easiest way to get around the paging issue is to use the OLE stored procedures (sp_OA*):

/* BEGIN SAMPLE CODE */

DECLARE @OLEReturn INT -- OLE return value (used for error checking)

DECLARE @SQL VARCHAR (8000) -- Holds the text of the LDAP query (this could be an input parameter

-- if this code is made into a standalone stored procedure)

DECLARE @ADOcommprop INT -- ADO Command object properties pointer

DECLARE @ADOcommpropVal INT -- ADO Command object properties value pointer

-- Create the ADO connection object

EXEC @OLEreturn = sp_OACreate 'ADODB.Connection', @ADOconn OUT

-- Set the provider property to ADsDSOObject to point to Active Directory

EXEC @OLEreturn = sp_OASETProperty @ADOconn , 'Provider', 'ADsDSOObject'

-- Open the ADO connection

EXEC @OLEreturn = sp_OAMethod @ADOconn , 'Open'

-- Create the ADO command object

EXEC @OLEreturn = sp_OACreate 'ADODB.Command', @ADOcomm OUT

-- Set the ADO command object to use the connection object created first

EXEC @OLEreturn = sp_OASETProperty @ADOcomm, 'ActiveConnection', 'Provider=''ADsDSOObject'''

-- Get a pointer to the properties set of the ADO Command Object

EXEC @OLEreturn = sp_OAGetProperty @ADOcomm, 'Properties', @ADOcommprop out

-- Set the PageSize property

EXEC @OLEreturn = sp_OAMethod @ADOcommprop, 'Item', @ADOcommpropVal out, 'Page Size'

-- Set the PageSize value

EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, 'Value', '1000'

-- Destroy PageSize token

EXEC @OLEReturn = sp_OADestroy @ADOcommpropVal

-- Set the SearchScope property to ADS_SCOPE_SUBTREE to search the entire subtree

EXEC @OLEreturn = sp_OAMethod @ADOcommprop, 'Item', @ADOcommpropVal out, 'SearchScope'

-- Set the SearchScope value

EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, 'Value', '2' -- ADS_SCOPE_SUBTREE

-- Destroy SearchScope token

EXEC @OLEReturn = sp_OADestroy @ADOcommpropVal

-- Set the Asynchronous property to True

EXEC @OLEreturn = sp_OAMethod @ADOcommprop, 'Item', @ADOcommpropVal out, 'Asynchronous'

-- Set the Asynchronous value

EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, 'Value',True

-- Destroy Asynchronous token

EXEC @OLEReturn = sp_OADestroy @ADOcommpropVal

-- Destroy command properties token

EXEC @OLEReturn = sp_OADestroy @ADOcommprop

-- Create the ADO Recordset to hold the results of the LDAP query

EXEC @OLEreturn = sp_OACreate 'ADODB.Recordset', @ADOrs out

-- Set the SQL text of the command object

EXEC @OLEreturn = sp_OASETProperty @ADOcomm, 'CommandText', @SQL

-- Run the LDAP query and output the results to the ADO Recordset

EXEC @OLEreturn = sp_OAMethod @ADOcomm, 'Execute', @ADOrs OUT

/* END SAMPLE CODE */

I have used this very successfully in a company with over 20,000 logins, 17,000+ of which have the same 3 character prefix.

ACTIVE DIRECTORY RETRIEVAL FROM SQL: PART 1

Okay, so given the sample code above, here's a stored procedure you can use as a wrapper to simplify your ADO calls from within SQL:

/* BEGIN STORED PROCEDURE */

CREATE PROCEDURE SetupOLE

@SQL VARCHAR (8000),

@ADOconn INT OUTPUT, -- Returned handle to the ADO Connection object

@ADOcomm INT OUTPUT, -- Returned handle to the ADO Command object

@ADOrs INT OUTPUT -- Returned handle to the ADO Recordset object

AS

DECLARE @OLEReturn INT

DECLARE @ADOcommprop INT -- ADO Command object properties pointer

DECLARE @ADOcommpropVal INT -- ADO Command object properties value pointer

DECLARE @Section VARCHAR (255)

DECLARE @ErrorObj INT

SET @Section = 'Create the ADO connection object'

EXEC @OLEreturn = sp_OACreate 'ADODB.Connection', @ADOconn OUT

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOconn

GOTO ErrorHandler

END

SET @Section = 'Set the provider property to ADsDSOObject to point to Active Directory'

EXEC @OLEreturn = sp_OASETProperty @ADOconn , 'Provider', 'ADsDSOObject'

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOconn

GOTO ErrorHandler

END

SET @Section = 'Open the ADO connection'

EXEC @OLEreturn = sp_OAMethod @ADOconn , 'Open'

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOconn

GOTO ErrorHandler

END

SET @Section = 'Create the ADO command object'

EXEC @OLEreturn = sp_OACreate 'ADODB.Command', @ADOcomm OUT

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcomm

GOTO ErrorHandler

END

SET @Section = 'Set the ADO command object to use the connection object created first'

EXEC @OLEreturn = sp_OASETProperty @ADOcomm, 'ActiveConnection', 'Provider=''ADsDSOObject'''

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcomm

GOTO ErrorHandler

END

SET @Section = 'Get a pointer to the properties set of the ADO Command Object'

EXEC @OLEreturn = sp_OAGetProperty @ADOcomm, 'Properties', @ADOcommprop out

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcomm

GOTO ErrorHandler

END

SET @Section = 'Set the PageSize property'

EXEC @OLEreturn = sp_OAMethod @ADOcommprop, 'Item', @ADOcommpropVal out, 'Page Size'

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcommprop

GOTO ErrorHandler

END

SET @Section = 'Set the PageSize value'

EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, 'Value', '1000'

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcommpropVal

GOTO ErrorHandler

END

SET @Section = 'Destroy PageSize token'

EXEC @OLEReturn = sp_OADestroy @ADOcommpropVal

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcommpropVal

GOTO ErrorHandler

END

SET @Section = 'Set the SearchScope property to ADS_SCOPE_SUBTREE to search the entire subtree'

EXEC @OLEreturn = sp_OAMethod @ADOcommprop, 'Item', @ADOcommpropVal out, 'SearchScope'

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcommprop

GOTO ErrorHandler

END

SET @Section = 'Set the SearchScope value'

EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, 'Value', '2' -- ADS_SCOPE_SUBTREE

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcommpropVal

GOTO ErrorHandler

END

SET @Section = 'Destroy SearchScope token'

EXEC @OLEReturn = sp_OADestroy @ADOcommpropVal

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcommpropVal

GOTO ErrorHandler

END

SET @Section = 'Set the Asynchronous property to True'

EXEC @OLEreturn = sp_OAMethod @ADOcommprop, 'Item', @ADOcommpropVal out, 'Asynchronous'

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcommprop

GOTO ErrorHandler

END

SET @Section = 'Set the Asynchronous value'

EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, 'Value',True

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcommpropVal

GOTO ErrorHandler

END

SET @Section = 'Destroy Asynchronous token'

EXEC @OLEReturn = sp_OADestroy @ADOcommpropVal

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcommpropVal

GOTO ErrorHandler

END

SET @Section = 'Destroy command properties token'

EXEC @OLEReturn = sp_OADestroy @ADOcommprop

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcommprop

GOTO ErrorHandler

END

SET @Section = 'Create the ADO Recordset to hold the results of the LDAP query'

EXEC @OLEreturn = sp_OACreate 'ADODB.Recordset', @ADOrs out

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOrs

GOTO ErrorHandler

END

SET @Section = 'Set the SQL text of the command object'

EXEC @OLEreturn = sp_OASETProperty @ADOcomm, 'CommandText', @SQL

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcomm

GOTO ErrorHandler

END

SET @Section = 'Run the LDAP query and output the results to the ADO Recordset'

EXEC @OLEreturn = sp_OAMethod @ADOcomm, 'Execute', @ADOrs OUT

IF @OLEreturn <> 0

BEGIN

SET @ErrorObj = @ADOcomm

GOTO ErrorHandler

END

GOTO Finish

ErrorHandler:

EXEC GetOLEError @OLEReturn, @ErrorObj, @Section

Finish:

RETURN @OLEreturn

/* END STORED PROCEDURE */

In the next installment I'll show how to use this stored procedure to retrieve Active Directory users and groups and store them in staging tables ...

ACTIVE DIRECTORY RETRIEVAL FROM SQL: PART 2

Okay, so now you have the wrapper procedure to access your AD information. Next we'll use this wrapper to easily bring this data down to staging tables:

/* BEGIN STAGING TABLE DEFINITIONS */

CREATE TABLE [dbo].[<Active Directory login table>](

[ID] [int] IDENTITY (1,1) NOT NULL,

[Login] [varchar](512) NULL,

[DistName] [varchar](512) NULL,

[FirstName] [varchar](512) NULL,

[Initials] [varchar](512) NULL,

[LastName] [varchar](512) NULL,

[EMail] [varchar](255) NULL,

[CreatedDate] [datetime] NULL,

[ChangedDate] [datetime] NULL,

[AcctCtrl] [bigint] NULL,

[Title] [varchar](512) NULL,

[Dept] [varchar](512) NULL,

[Phone] [varchar](512) NULL,

[FullName] [varchar](512) NULL,

[Mgr] [varchar](512) NULL,

[LastLogon] [varchar](50) NULL,

[LastLogoff] [varchar](50) NULL,

[CN] [varchar](512) NULL,

[Info] [varchar](512) NULL,

[sid] [varbinary](85) NULL,

[PriGroupID] [int] NULL,

[objectClass] [varchar](255) NULL,

[objectCategory] [varchar](255) NULL,

CONSTRAINT [PK_<Active Directory login table>]

PRIMARY KEY CLUSTERED ([ID] ASC)

WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[<Active Directory group table>] (

[ID] [int] IDENTITY (1,1) NOT NULL,

[Login] [varchar](512) NULL,

[EMail] [varchar](255) NULL,

[AcctName] [varchar](512) NULL,

[DistName] [varchar](512) NULL,

[CreatedDate] [datetime] NULL,

[ChangedDate] [datetime] NULL,

[Mgr] [varchar](512) NULL,

[sid] [varbinary](85) NULL,

CONSTRAINT [PK_<Active Directory group table>]

PRIMARY KEY CLUSTERED ([ID] ASC)

WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[<AD group/user correlation table>](

[UserLogin] [varbinary](85) NOT NULL,

[GroupLogin] [varbinary](85) NULL

) ON [PRIMARY]

GO

/* END STAGING TABLE DEFINITIONS */

Now that the tables have been created, we need to create the stored procedure to bring the login and group information into these tables ...

ACTIVE DIRECTORY RETRIEVAL FROM SQL: PART 2

So we need to get the Active Directory information into the tables we have just created, and we want to be able to retrieve beyond the 1000 record limit without having to loop through different character patterns. We do this by using the sp_OA* stored procedures to use ADO. This gives us the ability to set the paging from within SQL Server (which means -- say it with me -- NO EXTERNAL COMPONENTS!).

/* BEGIN SAMPLE CODE */

DECLARE @ADOconn INT -- ADO Connection object

DECLARE @ADOcomm INT -- ADO Command object

--DECLARE @ADOcommprop INT -- ADO Command object properties pointer

--DECLARE @ADOcommpropVal INT -- ADO Command object properties value pointer

DECLARE @ADOrs INT -- ADO RecordSet object

DECLARE @OLEReturn INT -- OLE return value

--DECLARE @src varchar(255) -- OLE Error Source

--DECLARE @desc varchar(255) -- OLE Error Description

DECLARE @SQL VARCHAR (8000) -- LDAP query string

--DECLARE @Batch_ID INT

--DECLARE @Login VARBINARY (85)

--DECLARE @CN VARCHAR (512)

--DECLARE @CT INT

DECLARE @ADRoot VARCHAR (255)

-- Set the Active Directory root domain

SET @ADRoot = 'LDAP://DC=<Domain1>,DC=<com,net,etc.>'

-- Retrieve Active Directory users

SET @SQL = 'SELECT

Manager,

Name,

TelephoneNumber,

Department,

Title,

userAccountControl,

whenChanged,

whenCreated,

SN,

initials,

givenName,

distinguishedName,

samAccountName,

objectSid,

mail

FROM ''' + @ADRoot + '''

WHERE objectCategory = ''User'''

EXEC @OLEReturn = SetupOLE @SQL, @ADOConn OUT, @ADOcomm OUT, @ADOrs OUT

IF @OLEReturn <> 0

RETURN

TRUNCATE TABLE <Active Directory login table>

INSERT INTO <Active Directory login table> (EMail, sid, Login, DistName, FirstName, Initials, LastName, CreatedDate, ChangedDate, AcctCtrl, Title, Dept, Phone, FullName, Mgr)

EXEC @OLEReturn = sp_OAgetproperty @ADOrs, 'getrows'

IF @OLEReturn <> 0

BEGIN

PRINT @OLEReturn

RETURN

END

EXEC @OLEReturn = DestroyOLE @ADOconn, @ADOcomm, @ADOrs

IF @OLEReturn <> 0

RETURN

-- End Active Directory users

-- Retrieve Active Directory groups

SET @SQL = 'SELECT

objectSid,

managedBy,

whenChanged,

whenCreated,

distinguishedName,

name,

samAccountName,

mail

FROM ''' + @ADRoot + '''

WHERE objectCategory = ''Group'''

EXEC @OLEReturn = SetupOLE @SQL, @ADOConn OUT, @ADOcomm OUT, @ADOrs OUT

IF @OLEReturn <> 0

RETURN

TRUNCATE TABLE <Active Directory group table>

INSERT INTO <Active Directory group table> (EMail, Login, AcctName, DistName, CreatedDate, ChangedDate, Mgr, sid)

EXEC @OLEReturn = sp_OAgetproperty @ADOrs, 'getrows'

IF @OLEReturn <> 0

RETURN

EXEC @OLEReturn = DestroyOLE @ADOconn, @ADOcomm, @ADOrs

IF @OLEReturn <> 0

RETURN

-- End Active Directory groups

/* END SAMPLE CODE */

Now this is all well and good, but what if you want the Active Directory group membership information? Stay tuned ...

ACTIVE DIRECTORY RETRIEVAL FROM SQL: PART 3

Now, the more astute among you may have noticed that there were actually TWO "part 2" sections. You may be wondering as to the metaphysical implications of this.

Don't sweat it. It means nothing. It was just a typo, fer cryin' out loud. That being said ...

Our next task, now that we have successfully retrieved both user a group information, is to tie to two together. We do this by using the "memberof" property in LDAP.

"Well, if there's a <memberof> property, why didn't we just query it when we were retrieving the user information?" you mght ask, if you were here, but you're not, so I'll go ahead and assume that you DID ask and answer the question anyway.

The reasonm this doesn't work is that group membership in Active Directory is a many-to-many relationship (a user can be a member of 1 to n groups, and each group can have 0 to n users). So if you query the <memberof> property directly you will get a null result. This happens because AD returns the contents of this property as an array, and since SQL Server doesn't know what to do with arrays it simply throws up its hands and says "Ah, there ain't nuthin' there."

So what we have to do, as painful and annoying as it sounds, is to open up a cursor containing a list of all the AD groups we retrieved earlier, and retrieve AD users that are a member of each group.

We WILL be using a linked server here. There are a couple of reasons for this:

1) Most of the groups retrieved are in all likelihood very specialized and will have fewer than 1000 members. Using a linked server will increase performance.

2) The number of OLE handles is limited (mainly by memory usage). We could try to use the OLE calls for each group, and explicitly destroy each handle after each call. This should prevent the number of handles from exceeding system capacity, right?

That's what I thought, too. As it turns out, though, even if handles are explictly destroyed they remain in the cache UNTIL THE BATCH COMPLETES. If there are more than a couple hundred AD groups, the procedure will error out (can't remember the exact error, but trust me ... it won't work).

That being said, here's some sample code:

/* BEGIN SAMPLE CODE */

DECLARE @ADOconn INT -- ADO Connection object

DECLARE @ADOcomm INT -- ADO Command object

DECLARE @ADOrs INT -- ADO RecordSet object

DECLARE @OLEReturn INT -- OLE return value

DECLARE @SQL VARCHAR (8000) -- LDAP query string

DECLARE @Login VARBINARY (85)

DECLARE @CN VARCHAR (512)

DECLARE @CT INT

DECLARE @ADRoot VARCHAR (255)

-- Truncate the table used to hold user/group correlation information

TRUNCATE TABLE <User/group correlation table>

-- Create a temporary table to hold AD user SID values

CREATE TABLE #CT (CT VARBINARY (85))

-- Declare and open a cursor to step through the list of Active Directory groups

DECLARE curGroups CURSOR FOR

SELECT

sid, DistName

FROM <Active Directory group table>

ORDER BY Login

OPEN curGroups

FETCH NEXT FROM curGroups INTO @Login, @CN

WHILE @@FETCH_STATUS = 0

BEGIN

-- Empty the temp table

TRUNCATE TABLE #CT

-- Build a SQL statement to insert the SID values directly from the linked server into the temp table

SET @SQL = 'INSERT #CT

SELECT *

FROM OPENQUERY (

ADSI,

''SELECT objectSid

FROM ''''' + @ADRoot + '''''

WHERE objectCategory = ''''User''''

AND memberof=''''' + REPLACE (@CN, '''', '''''''''') + ''''''')'

EXEC (@SQL)

-- Select the number of records inserted. If this value is less than 1000 then there is no need

-- to execute the OLE calls, and we simply copy the values into the correlation table.

SELECT @CT = COUNT(*) FROM #CT

IF @CT < 1000

BEGIN

IF @CT <> 0

BEGIN

INSERT <User/group correlation table> (UserLogin, GroupLogin)

SELECT CT, @Login

FROM #CT

WHERE CT IS NOT NULL

END

END

ELSE

BEGIN

-- Execute the OLE calls

SET @SQL = 'SELECT objectSid FROM ''' + @ADRoot + ''' WHERE objectCategory = ''User'' AND memberof=''' + @CN + ''''

EXEC @OLEReturn = SetupOLE @SQL, @ADOConn OUT, @ADOcomm OUT, @ADOrs OUT

IF @OLEReturn <> 0

RETURN

INSERT INTO <User/group correlation table> (UserLogin)

EXEC @OLEReturn = sp_OAgetproperty @ADOrs, 'getrows'

-- If the @OLEReturn value is not zero, this indicates an error condition. However, one of these

-- conditions (800A0BCD, aka -2146825267) indicates that the procedure executed successfully but

-- that no rows were found that matched the criteria. Therefore, for our purposes, this is NOT an

-- error but a valid return condition.

IF (@OLEReturn <> 0) AND (@OLEReturn <> -2146825267)

RETURN

-- If rows were found we update the rows in the correlation table with the name of the AD group.

IF @OLEReturn <> -2146825267

UPDATE <User/group correlation table>

SET GroupLogin = @Login

WHERE GroupLogin IS NULL

-- If no rows were returned, we reset the @OLEReturn value to indicate that there was no error

-- condition encountered.

IF @OLEReturn = -2146825267

SET @OLEReturn = 0

IF @OLEReturn <> 0

GOTO CursorError

EXEC @OLEreturn = sp_OADestroy @ADOconn

IF @OLEReturn <> 0

GOTO CursorError

END

FETCH NEXT FROM curGroups INTO @Login, @CN

END

CursorError:

CLOSE curGroups

DEALLOCATE curGroups

DROP TABLE #CT

/* END SAMPLE CODE */

Dave,

Your scripts are great examples.  However, your last script "part 3" errors out before it populates the correlation table.  Thoughts?

Dave,

Got "part 3" working by adding:

SET @ADRoot = 'LDAP://DC=xyz,DC=abc,DC=com'

to just after the declaration section but now I'm stuck again with binary data in those columns rather than varchars.  Was this the intent of your scripts?

A query such as this would tie the users name to the group name.

SELECT u.Login AS UserName, ug.Login AS GroupName

FROM ADGroupUserCorrelationTable

INNER JOIN ADGroupTable ug

 ON GroupLogin = ug.sid

INNER JOIN ADLoginTable u

 ON UserLogin = u.sid

Hi,

Is it possible to Disable an account in AD by creating a Linked Server in SQL 2005??

I was able to create a view to get all the information that I need and I can see the 'accountExpires' information, but cannot change it. Any ideas on that??

Thanks,

I'm having a problem with error 7321:

An error occurred while preparing the query "SELECT samAccountName,

     givenName, sn, legacyExchangeDN

  FROM 'LDAP://askcts.com/OU=Users,OU=BHMRiverchase,OU=CTS Corporate,DC=askcts,DC=com'

  WHERE objectClass='Person' AND objectClass = 'User''

  WHERE objectClass='Person'

  AND objectClass = 'User'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

I don't know what's going on.  I disabled security context on the linked server, as others instructed.  I'm confident that it's not a syntax error because the guy next to me can run this query just fine.  The only thing I haven't checked is the whole local vs. domain account issue since I'm not sure where to look that up.  Anyone know how to fix the issue?

Thanks for the artical, First time go on my part.

Quick note on my network, Needed to change security on ADSI Linked server to login as Network Admin

Nick2345, it looks like you should be using objectCategory='Person' instead of objectClass='Person' .

objectClass='User' is correct.  You also have your WHERE twice.

Ha, I wish I'd seen this way earlier, I've been importing a csvde dump from a text file for ages.. Thanks!

Hello Brendon

Thanks for the info regarding AD.

I'm using the following query

SELECT * FROM OpenQuery

(ADSI,

'SELECT objectSid,

                title,

displayName,

sAMAccountName,

givenName,

telephoneNumber,

facsimileTelephoneNumber,

mail,

userAccountControl,

       company,

department,

name,

sn

FROM ''LDAP://DC=nr,DC=ad,DC=xyzco,DC=com''

WHERE     objectCategory=''Person''

     and objectClass = ''User''

ORDER BY name')

This works fine, but only for 1000 objects, I know a lot of people have been talking about this but is'nt there a simple property or parameter I can use for this in the above code

Make it easy,

See muy Blog at:

eidermauricio.blogspot.com/.../consultas-contra-el-directorio-activo_31.html

I made an article that guide you step by step

eider d colombia con gusto

We have around 30,000 people, so I've extended Brendan's solution to 3 levels, and less coding. Its probably a bit slower, but captures everyone in our organisation.

-The insertion/update/deleting to an ADcopy table I do in another set of code not included here.

-There are more fields I try to capture from AD than Brendan's solution.

Any comments welcome...

declare

@AlphaChars varchar(60)

, @search varchar(10)

, @searchLevel1 varchar(1)

, @searchLevel2 varchar(1)

, @searchLevel3 varchar(1)

, @countLevel1 int

, @countLevel2 int

, @countLevel3 int

, @intRowCount int

, @SQL nvarchar(4000)

, @nvarOU nvarchar(200)

, @nvchDC nvarchar(100)

, @nvchCN varchar(256)

set @nvchDC = 'DC=your_domain,DC=com'

set @nvarOU = 'OU=SQL Test' --set equal to '' if not needed

-- any chars, but the first char must be a space

set @AlphaChars = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ.-_`0123456789'

set nocount on

if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.#LDAP'))

drop table #LDAP

create table [dbo].[#ldap] (

[row_id] [int] IDENTITY (1, 1) NOT NULL ,

sAMAccountName [nvarchar] (256) collate sql_latin1_general_cp1_ci_as null,  

[cn] [nvarchar] (256) collate sql_latin1_general_cp1_ci_as null ,

[nvarchar] (256) collate sql_latin1_general_cp1_ci_as null ,

displayName [nvarchar] (256) collate sql_latin1_general_cp1_ci_as null,

givenName [nvarchar] (256) collate sql_latin1_general_cp1_ci_as null,

telephoneNumber [nvarchar] (256) collate sql_latin1_general_cp1_ci_as null,

[adspath] [nvarchar] (256) collate sql_latin1_general_cp1_ci_as null,

[homedirectory] [nvarchar] (256) collate sql_latin1_general_cp1_ci_as null,

[mail] [nvarchar] (256) collate sql_latin1_general_cp1_ci_as null,

[memberof] [nvarchar] (256) collate sql_latin1_general_cp1_ci_as null,

[primarygroupid] [int],

createTimeStamp datetime

)  ON [PRIMARY]

-- start on non space char

set @countLevel1 =2

-- first level loop

while @countLevel1 <= len(@AlphaChars)

begin

-- get first level char

set @searchLevel1=substring(@AlphaChars, @countLevel1, 1)

-- reset start on space

set @countLevel2 =1

-- second level loop

while @countLevel2 <= len(@AlphaChars)

begin

-- reset start on space

set @countLevel3 =1

-- third level loop

while @countLevel3 <= len(@AlphaChars)

begin

-- setup the string to search for. By using the trim function we can form each level depending on no records

-- eg A 99, B 1000 > BA 9, BB 20 etc

-- trim the spaces forming just A, B, C ; AA, AB for search etc

set @searchLevel1=substring(@AlphaChars, @countLevel1, 1)

set @searchLevel2=rtrim(substring(@AlphaChars, @countLevel2, 1))

set @searchLevel3=rtrim(substring(@AlphaChars, @countLevel3, 1))

set @search=@searchLevel1 + @searchLevel2 + @searchLevel3

set @SQL ='insert into #ldap (cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath,  homedirectory, mail, primarygroupid, createTimeStamp) ' +

'select cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath,  homedirectory, mail, primarygroupid, createTimeStamp ' +

'from openquery(ADSI,''<LDAP://root>;(&(objectCategory=Person)(!(UserAccountControl:1.2.840.113556.1.4.803:=2))(sAMAccountName=' +

@search + '*));cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, adspath, primarygroupid, createTimeStamp;subtree'') q '+

'where not exists (select adspath from #ldap ld where ld.adspath collate sql_latin1_general_cp1_ci_as= q.adspath) ' +

'order by sAMAccountName'

exec sp_executesql @SQL

set @intRowCount = @@rowcount

-- prints what string is being searched for : no of inserts

print @search + ' : ' + convert(varchar, @intRowCount)

-- if searched on @searchLevel1 and under 1000 then everything is fine so skip search2 to next search1 eg A > B

if @intRowCount < 1000 and @searchLevel2='' set @countLevel2=@countLevel2 +100

-- if searched on @searchLevel2 and under 1000 then everything is fine so skip to next search2 eg AA > AB

if @intRowCount < 1000 and @searchLevel3='' set @countLevel3=@countLevel3 +100

-- else over 1000 so increment third level

set @countLevel3=@countLevel3 + 1

end

-- increment next second level char

set @countLevel2=@countLevel2 +1

end

-- increment next first level char

set @countLevel1=@countLevel1 +1

end

set nocount off

Dave...thanks for the code.  Everything works fine until I try to add the accountExpires field?  I know that this is 64bit value, and I have done enough research to translate it into a usuable format.  I can pull the field whenever I use the linked server and not the above code, but of course i am then limited to 1000 records.

Does anyone know of a way to retrieve the accountExpires field via Dave's code?

Any help would be appreciated.

Wow!

I have been looking for this info for about a month now.

I'm a novice so I need to digest more but pretty much along the lines of what I have been trying to do.  Thanks much for all this info folks