Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Create a SQL Server View of your AD Users

I posted about this earlier today, and the solution was so simple and straight-forward that I’m posting the solution in a separate post.  So, to create a view in SQL Server of your AD users, simply do this:


Step 1: Create a linked server to your Active Directory


sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’, ‘ADSDSOObject’, ‘adsdatasource’


Step 2: Create a view in SQL server using OPENQUERY to select from Active Directory


CREATE  VIEW dbo.vw_AD_USER_INFO
AS


SELECT * FROM OpenQuery(ADSI, ‘SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ”LDAP://DC=whaever,DC=domain,DC=org” where objectClass = ”User”’)


GO


Now, this might not exactly work for you if your AD Schema is different than ours, etc, but for me it worked like a charm, and the general concept should apply to your LDAP server.


-Brendan

About Brendan Tompkins

Brendan runs CodeBetter.Com. He was twice awarded MVP for Microsoft .NET, and is a founder and the CTO of Quick180.Com More about Brendan at https://www.linkedin.com/codebetter
This entry was posted in SQL. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • sudhakar

    Hi

    I got below error with result(11,901 rows).I got more than 1000 rows but some users are missing.
    Msg 7330, Level 16, State 2, Line 1

    Cannot fetch a row from OLE DB provider “ADsDSOObject” for linked server “ADSI”.

    Please help me to any one…….

    Thanks,
    Sudhakar

  • kyran

    Hello, I have a SQL job that runs a vbscript which queries the AD domain, pulls user attributes, all standard items. I need to add something so that it will pull the managers “sAMAccountName” for each of the users. I have read about the get commands, but I’m not any good with vbscripting, and just ok with powershell, but I have no option to trying to convert to posh.

    Can anyone tell me how I would add something to get the samaccountname of the manager for each user, where it would go within the current script, and then I think I just add a sql recordset item for the samaccountname.

    Thank You!
    Kyran

  • Jack

    http://support.microsoft.com/kb/269181 answers my question – Hermann’s code checks that UF_ACCOUNTDISABLED
    is not true

  • Jack

    It’s a long time ago, but do you remember the significance of the following bit of code in the openquery statement?
    (!(UserAccountControl:1.2.840.113556.1.4.803:=2))

    Your code works like a charm, but I would like to know the difference between 2 and not 2!

  • http://pulse.yahoo.com/_ZRPZRETDVQZ7R2MEBUIDBAHOYM Paul

    Could you translate that article into English please?

  • NJ

    Hi Dave,

    Today I implemented your scripts today and they work perfect! I only have to find out how to create sProc’s “GetOLEError” and “DestryOLE” because they are called but not created in the scripts.
    Thanks a lot!

    Cheers, 
    NJ

  • Pravesh Singh

    This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy.  I have found another nice post related to this post over the internet which also explained very well. For more details you may check that through this url…

    http://mindstick.com/Articles/bfb2480d-d4e3-4eed-b7cc-66d32a60e007/?View%20in%20SQL%20Server

    Thanks
     

  • http://codebetter.com Brendan Tompkins
  • Gmlinek

    Brendan,
    This is nice, but my problem is I need to recover information and dates as to when a computer last logged onto the domain. as well as the user utilizing the computer. Now the computers are in one OU and the users are in another, and the “LastLogon” is displayed in nano seconds. Can you assist me in my problem?

  • http://raoulteeuwen.blogspot.com Raoul Teeuwen

    Hi.

    Thanks for all the info. I’ve tried Dave’s code. I’m running a SQL 2000-machine. And i want to know what users are in what groups within the AD.

    Runing Dave’s code, I ran into a problem that GetOleError isn’t available. After taking that out of the equasion, i run into a problem within SetupOLE, at the line “EXEC @OLEreturn = sp_OAMethod @ADOcomm, ‘Execute’, @ADOrs OUT”. At the moment of the error

    ADOcomm has a value of: 33488638
    ADOrs has a value of: 50265854

    When i run the command manually with these values:

    EXEC @OLEreturn = sp_OAMethod 33488638, ‘Execute’, 50265854 OUT

    i get: “Cannot use the OUTPUT option when passing a constant to a stored procedure.”.

    When i remove the OUT option running:

    EXEC @OLEreturn = sp_OAMethod 33488638, ‘Execute’, 50265854

    I get “sp_OACreate has not yet been called successfully for this command batch.”.

    When i edit SetupOLE so the OUT-parameter is removed, i immediately run into the 1st error (while, with the OUT-parameter active, it takes some seconds before the error shows)

    Any ideas?

  • http://www.r2d2.de Roland A.

    Hi Folks Pretty coll that you all posted this solutions, for the owner of the Page: Your article was good for the beginning… BUT you could just expand your article with the Added Informations if you have the Time… its a mess to scroll up and down…

    however, its very good to have at least a few people who are also working on SQL Server and try to retrieve the ad data that way.

    cheers

  • dinorex

    CatCat, that’s your permission problem…

    In fact, this solution is CORRECT :)

  • John

    This may help someone…
    This query pulls all AD accounts expiring in 7 days, along with their managers email address (so you can CC them)

    SELECT Rowset_1.GIVENNAME, Rowset_1.SAMACCOUNTNAME, Rowset_1.MAIL, Rowset_1.NAME, Rowset_1.MANAGER, DERIVEDTBL.MGRMAIL AS MGRMAIL, ROUND((CAST(Rowset_1.AccountExpires AS BigInt)
    / 10000000 – 11644560000 – CAST(DATEDIFF(SECOND, ‘19700101’, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) AS VARCHAR(64))) / 60 / 60 / 24, 0)
    AS EXPIRYDAYS
    FROM OPENQUERY(ADSI,
    ‘ SELECT GivenName, samAccountName, mail, name,manager, AccountExpires FROM ”DC=[your_domain],DC=[com]” where objectClass = ”User” and UserAccountControl <> 514 and accountExpires <> 0 and accountExpires <> 9223372036854775807 ‘)
    Rowset_1 LEFT OUTER JOIN
    (SELECT adspath, mail AS MgrMail
    FROM OPENQUERY(ADSI, ‘SELECT ADSpath, mail FROM ”LDAP://DC=[your_domain],DC=[com]”’) Rowset_1) DERIVEDTBL ON
    ‘LDAP://’ + Rowset_1.manager = DERIVEDTBL.adspath
    WHERE (ROUND((CAST(Rowset_1.AccountExpires AS BigInt) / 10000000 – 11644560000 – CAST(DATEDIFF(SECOND, ‘19700101’, DATEADD(dd, DATEDIFF(dd, 0,
    GETDATE()), 0)) AS VARCHAR(64))) / 60 / 60 / 24, 0) = 7)
    ORDER BY Rowset_1.AccountExpires

  • John

    This may help someone…
    This query pulls all AD accounts expiring in 7 days, along with their managers email address (so you can CC them)

    SELECT Rowset_1.GIVENNAME, Rowset_1.SAMACCOUNTNAME, Rowset_1.MAIL, Rowset_1.NAME, Rowset_1.MANAGER, DERIVEDTBL.MGRMAIL AS MGRMAIL, ROUND((CAST(Rowset_1.AccountExpires AS BigInt)
    / 10000000 – 11644560000 – CAST(DATEDIFF(SECOND, ‘19700101’, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) AS VARCHAR(64))) / 60 / 60 / 24, 0)
    AS EXPIRYDAYS
    FROM OPENQUERY(ADSI,
    ‘ SELECT GivenName, samAccountName, mail, name,manager, AccountExpires FROM ”DC=[your_domain],DC=[com]” where objectClass = ”User” and UserAccountControl <> 514 and accountExpires <> 0 and accountExpires <> 9223372036854775807 ‘)
    Rowset_1 LEFT OUTER JOIN
    (SELECT adspath, mail AS MgrMail
    FROM OPENQUERY(ADSI, ‘SELECT ADSpath, mail FROM ”LDAP://DC=[your_domain],DC=[com]”’) Rowset_1) DERIVEDTBL ON
    ‘LDAP://’ + Rowset_1.manager = DERIVEDTBL.adspath
    WHERE (ROUND((CAST(Rowset_1.AccountExpires AS BigInt) / 10000000 – 11644560000 – CAST(DATEDIFF(SECOND, ‘19700101’, DATEADD(dd, DATEDIFF(dd, 0,
    GETDATE()), 0)) AS VARCHAR(64))) / 60 / 60 / 24, 0) = 7)
    ORDER BY Rowset_1.AccountExpires

  • 0xG

    objectClass = ”User”’
    This is ambigious becauser I get both users and computers in my view.
    objectCategory=”user” seens to be more reliable.

    Strangely, I can add objectCategory to my SELECT list, but not objectClass – any ideas why?

  • mat

    Hi all,

    we have two trusted domains (query from SQL.dom1.com to AD.dom2.com) and we are using “Be made using this security context” setting with domain account from dom2.com. But we still get the same error. Any idea?

    The OLE DB provider “ADsDSOObject” for linked server “ADSI_CORP” reported an error. The provider indicates that the user did not have the permission to perform the operation.

  • Joshua Perry

    I pull 50,000 users every day. I use a vbscript to get around the 1000 record limit and write to the SQL server. This also gives me a static table instead of a view. The adspath gets stored in the table so I can pull different OUs. It would look like this:

    LDAP://dcserver/OU=users,DC=domain,DC=forest,DC=tld

    USE [DATABASE]
    GO
    /****** Object: Table [dbo].[ActiveDirectory] ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[ActiveDirectory](
    [manager] [varchar](1024) NULL,
    [company] [varchar](1024) NULL,
    [department] [varchar](1024) NULL,
    [title] [varchar](1024) NULL,
    [facsimileTelephoneNumber] [varchar](1024) NULL,
    [info] [varchar](1024) NULL,
    [ipPhone] [varchar](1024) NULL,
    [mobile] [varchar](1024) NULL,
    [pager] [varchar](1024) NULL,
    [homePhone] [varchar](1024) NULL,
    [HomeDrive] [varchar](1024) NULL,
    [HomeDirectory] [varchar](1024) NULL,
    [ScriptPath] [varchar](1024) NULL,
    [ProfilePath] [varchar](1024) NULL,
    [userWorkstations] [varchar](1024) NULL,
    [samAccountName] [varchar](50) NULL,
    [userPrincipalName] [varchar](50) NULL,
    [varchar](1024) NULL,
    [postalCode] [varchar](1024) NULL,
    [st] [varchar](1024) NULL,
    [l] [varchar](1024) NULL,
    [streetAddress] [varchar](1024) NULL,
    [mail] [varchar](1024) NULL,
    [telephoneNumber] [varchar](1024) NULL,
    [physicalDeliveryOfficeName] [varchar](1024) NULL,
    [displayName] [varchar](1024) NULL,
    [sn] [varchar](1024) NULL,
    [initials] [varchar](1024) NULL,
    [givenName] [varchar](1024) NULL
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

    USE [DATABASE]
    GO
    /****** Object: Table [dbo].[OU] ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[AccountsOUs](
    [ADsPath] [ntext] NULL,
    [rec_id] [int] IDENTITY(1,1) NOT NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    ==================================================================

    Option Explicit

    Dim adoCommand, _
    adoConnection, _
    strBase, _
    strFilter, _
    strAttributes

    Dim objRootDSE, _
    strDNSDomain, _
    strQuery, _
    adoRecordset

    Dim givenName, _
    initials, _
    sn, _
    displayName, _
    physicalDeliveryOfficeName, _
    telephoneNumber, _
    mail, _
    streetAddress, _
    l, _
    st, _
    postalCode, _
    c, _
    userPrincipalName, _
    samAccountName, _
    userWorkstations, _
    ProfilePath, _
    ScriptPath, _
    HomeDirectory, _
    HomeDrive, _
    homePhone, _
    pager, _
    mobile, _
    ipPhone, _
    info, _
    facsimileTelephoneNumber, _
    title, _
    department, _
    company, _
    manager

    Dim sqlCommand, _
    sqlConnection

    Dim adPath, _
    adSql, _
    adConnection

    ‘ Setup ADO objects.

    Set adoCommand = CreateObject(“ADODB.Command”)
    Set adoConnection = CreateObject(“ADODB.Connection”)

    set sqlConnection = CreateObject(“ADODB.Connection”)
    Set sqlCommand = CreateObject(“ADODB.Command”)

    Set adPath = CreateObject(“ADODB.Recordset”)
    Set adConnection = CreateObject(“ADODB.Connection”)
    set adSql= CreateObject(“ADODB.Command”)

    adSql.CommandText = “TRUNCATE TABLE ActiveDirectory; SELECT adspath FROM OU WHERE rec_id = 1″
    adConnection.Open “Driver={SQL Server};server=SERVER,PORT;” & _
    “database=DATABASE;uid=USER;pwd=PASSWORD;”
    adSql.ActiveConnection = adConnection
    Set adPath = adSql.Execute

    Do Until adpath.EOF
    adoConnection.Provider = “ADsDSOObject”
    adoConnection.Open “Active Directory Provider”
    adoCommand.ActiveConnection = adoConnection

    ‘ Search entire Active Directory domain.

    strDNSDomain = adPath(“ADsPath”)
    strBase = “< "& strDNSDomain &">”

    ‘ Filter on user objects.
    strFilter = “(&(objectClass=user))”

    ‘ Comma delimited list of attribute values to retrieve.
    strAttributes = “givenName,” & _
    “initials,” & _
    “sn,” & _
    “displayName,” & _
    “physicalDeliveryOfficeName,” & _
    “telephoneNumber,” & _
    “mail,” & _
    “streetAddress,” & _
    “l,” & _
    “st,” & _
    “postalCode,” & _
    “c,” & _
    “userPrincipalName,” & _
    “samAccountName,” & _
    “userWorkstations,” & _
    “ProfilePath,” & _
    “ScriptPath,” & _
    “HomeDirectory,” & _
    “HomeDrive,” & _
    “homePhone,” & _
    “pager,” & _
    “mobile,” & _
    “ipPhone,” & _
    “info,” & _
    “facsimileTelephoneNumber,” & _
    “title,” & _
    “department,” & _
    “company,” & _
    “manager”

    ‘ Construct the LDAP syntax query.
    strQuery = strBase & “;” & strFilter & “;” & strAttributes

    adoCommand.CommandText = strQuery
    adoCommand.Properties(“Page Size”) = 1000
    adoCommand.Properties(“Timeout”) = 30
    adoCommand.Properties(“Cache Results”) = False

    ‘ Run the query.
    Set adoRecordset = adoCommand.Execute

    ‘ Enumerate the resulting recordset.
    Do Until adoRecordset.EOF

    ‘ Retrieve values and display.
    givenName= adoRecordset.Fields(“givenName”).Value
    initials= adoRecordset.Fields(“initials”).Value
    sn=adoRecordset.Fields(“sn”).Value
    displayName= adoRecordset.Fields(“displayname”).Value
    physicalDeliveryOfficeName = adoRecordset.Fields(“physicalDeliveryofficename”).Value
    telephoneNumber= adoRecordset.Fields(“telephonenumber”).Value
    mail=adoRecordset.Fields(“mail”).Value
    streetAddress=adoRecordset.Fields(“streetaddress”).Value
    l=adoRecordset.Fields(“l”).Value
    st=adoRecordset.Fields(“st”).Value
    postalCode=adoRecordset.Fields(“postalcode”).Value
    c=adoRecordset.Fields(“c”).Value
    userPrincipalName=adoRecordset.Fields(“userprincipalname”).Value
    samAccountName=adoRecordset.Fields(“samAccountName”).Value
    userWorkstations=adoRecordset.Fields(“userworkstations”).Value
    ProfilePath=adoRecordset.Fields(“profilepath”).Value
    ScriptPath=adoRecordset.Fields(“Scriptpath”).Value
    HomeDirectory=adoRecordset.Fields(“Homedirectory”).Value
    HomeDrive=adoRecordset.Fields(“homedrive”).Value
    homePhone=adoRecordset.Fields(“homephone”).Value
    pager=adoRecordset.Fields(“pager”).Value
    mobile=adoRecordset.Fields(“mobile”).Value
    ipPhone=adoRecordset.Fields(“ipphone”).Value
    info=adoRecordset.Fields(“Info”).Value
    facsimileTelephoneNumber=adoRecordset.Fields(“facsimileTelephonenumber”).Value
    title=adoRecordset.Fields(“title”).Value
    department=adoRecordset.Fields(“department”).Value
    company=adoRecordset.Fields(“company”).Value
    manager=adoRecordset.Fields(“manager”).Value

    ‘ Wscript.echo “NT Name: ” & strName & “, Common Name: ” & strCN
    SQLConnection.Open “Driver={SQL Server};server=SERVER,PORT;” & _
    “database=DATABASE;uid=USER;pwd=PASSWORD;”
    Set sqlCommand.ActiveConnection = sqlConnection

    SQLCommand.CommandText = “SET QUOTED_IDENTIFIER OFF ” & _
    “INSERT INTO ” & _
    “ActiveDirectory ” & _
    “(samAccountName,” & _
    “givenName,” & _
    “initials,” & _
    “sn,” & _
    “displayName,” & _
    “physicalDeliveryOfficeName,” & _
    “telephoneNumber,” & _
    “mail,” & _
    “streetAddress,” & _
    “l,” & _
    “st,” & _
    “postalCode,” & _
    “c,” & _
    “userPrincipalName,” & _
    “userWorkstations,” & _
    “ProfilePath,” & _
    “ScriptPath,” & _
    “HomeDirectory,” & _
    “HomeDrive,” & _
    “homePhone,” & _
    “pager,” & _
    “mobile,” & _
    “ipPhone,” & _
    “info,” & _
    “facsimileTelephoneNumber,” & _
    “title,” & _
    “department,” & _
    “company,” & _
    “manager) ” & _
    “VALUES(” & _
    chr(34) & sAMAccountName & chr(34) & “,” & _
    chr(34) & givenName & chr(34) & “,” & _
    chr(34) & initials & chr(34) & “,” & _
    chr(34) & sn & chr(34) & “,” & _
    chr(34) & displayName & chr(34) & “,” & _
    chr(34) & physicalDeliveryOfficeName & chr(34) & “,” & _
    chr(34) & telephoneNumber & chr(34) & “,” & _
    chr(34) & mail & chr(34) & “,” & _
    chr(34) & streetAddress & chr(34) & “,” & _
    chr(34) & l & chr(34) & “,” & _
    chr(34) & st & chr(34) & “,” & _
    chr(34) & postalCode & chr(34) & “,” & _
    chr(34) & c & chr(34) & “,” & _
    chr(34) & userPrincipalName & chr(34) & “,” & _
    chr(34) & userWorkstations & chr(34) & “,” & _
    chr(34) & ProfilePath & chr(34) & “,” & _
    chr(34) & ScriptPath & chr(34) & “,” & _
    chr(34) & HomeDirectory & chr(34) & “,” & _
    chr(34) & HomeDrive & chr(34) & “,” & _
    chr(34) & homePhone & chr(34) & “,” & _
    chr(34) & pager & chr(34) & “,” & _
    chr(34) & mobile & chr(34) & “,” & _
    chr(34) & ipPhone & chr(34) & “,” & _
    chr(34) & info & chr(34) &”,” & _
    chr(34) & facsimileTelephoneNumber & chr(34) & “,” & _
    chr(34) & title & chr(34) & “,” & _
    chr(34) & department & chr(34) & “,” & _
    chr(34) & company & chr(34) & “,” & _
    chr(34) & manager & chr(34) & “)”

    sqlCommand.CommandType = 1
    sqlCommand.Execute
    sqlConnection.Close

    ‘ Move to the next record in the recordset.
    adoRecordset.MoveNext

    Loop

    adoRecordset.Close
    adoConnection.Close

    ‘ Move to the next record in the recordset.
    adPath.MoveNext

    loop

    ‘ Clean up.

    adPath.Close
    adConnection.Close

  • Gary

    For those of you have issues with the 1000 row limit you can change the default maxPageSize on the domain controller using NTDSUtil. You still may have an issue if for performance issues you can’t set it above say 10000, I’m not sure how high you want to go. I used 10000 and now I can query all my users without any workaround. I’m surprised I haven’t seen this mentioned specifically when people have questions about the 1000 row limit.

    Cheers

    For more information about how to modify the server limit for maxPageSize by using NTDSUtil, click the following article number to view the article in the Microsoft Knowledge Base:
    315071 (http://support.microsoft.com/kb/315071/) How to view and set LDAP policy in Active Directory by using Ntdsutil.exe

  • Mike

    Great lessons! But could someone PLEASE show me how to add Active Directory Users to a table with Insert, Update and Delete? I’m looking for a Stored Procedure that does this once a day.

    Thanks,

  • MJTech

    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

  • Joseph Brown

    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.

  • Richard Beal

    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 ,
    [sn] [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,”;(&(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

  • http://eidermauricio.blogspot.com eider mauricio aristizabal erazo

    Make it easy,
    See muy Blog at:
    http://eidermauricio.blogspot.com/2007/10/consultas-contra-el-directorio-activo_31.html
    I made an article that guide you step by step
    eider d colombia con gusto

  • Rashid

    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

  • DH

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

  • mmoore225

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

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

  • kevin

    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

    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?

  • Need Help!!

    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,

  • Ed

    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

  • Steve Moreno

    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?

  • Steve Moreno

    Dave,

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

  • Dave Naples

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

    — 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
    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 (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 (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
    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 Naples

    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=,DC=

    — 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

    INSERT INTO (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

    INSERT INTO (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 …

  • Dave Naples

    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].[](
    [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_
    ]
    PRIMARY KEY CLUSTERED ([ID] ASC)
    WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    CREATE TABLE [dbo].[
    ] (
    [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_
    ]
    PRIMARY KEY CLUSTERED ([ID] ASC)
    WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    CREATE TABLE [dbo].[](
    [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 …

  • Dave Naples

    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 …

  • Dave Naples

    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.

  • Derek Czarny

    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.

  • Buzz

    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.

  • Charlie Asbornsen

    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.

  • James

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

  • PedroPN

    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

  • eeBee

    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…

  • Texrat

    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…

  • Jeremy

    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

  • http://marcusoh.blogspot.com marcus oh

    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.

  • Mike

    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. :)

  • SjoerdvW

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

  • Sampath

    I also have the same problem

  • Hermann Croucamp

    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

  • Brendan Tompkins

    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

  • Hermann Croucamp

    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 Croucamp

    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’

  • Brendan Tompkins

    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

  • Hermann Croucamp

    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?

  • Christiaan

    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”’)

  • Jan Mlekusch

    Through the tool ADSIedit.msc delivered by the Exchange Server

  • Piotr Szwajkowski

    Prepare returned 0x80040e14…

    This error is a syntax error.

    Just check your query sytax and should be fine.

  • Drew

    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.

  • Deathagen

    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

  • Deathagen

    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.

  • Martin Douglas

    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-

  • fouber

    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.

  • Steve Minns

    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?

  • Jahyen

    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.

  • Brendan Tompkins

    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…

  • Anders

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

  • Cameron Beatley

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

  • Neil Evans

    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.

  • Anders

    Brendan – thanks alot for your help!

  • Brendan Tompkins

    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’

  • Anders Jägard

    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

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

  • Anders Jägard

    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 Tompkins

    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.

  • Brendan Tompkins

    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 Jägard

    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…

  • Brendan Tompkins

    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.

  • catcat

    In Step 1:

    I got the follow error:

    ??? OLE DB ???? ‘ADSDSOObject’ ????????

    OLE DB ????[OLE/DB Provider ‘ADSDSOObject’ ICommandPrepare::Prepare returned 0x80040e14]?