SQL SERVER – How to Query Active Directory Data Using ADSI / LDAP Linked Server

There are situations when you need to integrate SQL Server with other product. Once of the classic example was seen during my last visit to a client.  They wanted list of email addresses and phone numbers for all users in the company to be fetched by Active Directory. I have told them that SQL can read that data via linked server. Here are the steps to learn how to query active directory data.

Create Linked Server

We need to use Active Directory Service Interfaces (ADSI) linked server.  You need to provide a domain account which can query AD.

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'hadomain\administrator',@rmtpassword='@very$tr0ngP@$$w0rd1234'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

As highlighted above, you need to change @rmtuser and @rmtpassword variables to a login and password that has access to your Active Directory in the second statement.

Find LDAP URL

Below is the query which I ran to find next query.

SELECT * FROM OpenQuery (
ADSI,
'SELECT *
FROM ''LDAP://hadomain.com/DC=hadomain,DC=com''
WHERE objectClass = ''User''
')

SQL SERVER - How to Query Active Directory Data Using ADSI / LDAP Linked Server ADSI-01
We ran above query to find LDAP clause which we need to get details.

Fire the query

Let’s say we want to get more details about the SQL Service account, we can pick up the LADP URL from previous output.

SELECT * FROM OpenQuery
(
ADSI,
'SELECT pager, displayName, telephoneNumber, sAMAccountName,
mail, mobile, facsimileTelephoneNumber, department, physicalDeliveryOfficeName, givenname
FROM  ''LDAP://hadomain.com/CN=SQLService,CN=Users,DC=HADOMAIN,DC=COM''
WHERE objectClass =  ''User''
') AS tblADSI

Here is the output

SQL SERVER - How to Query Active Directory Data Using ADSI / LDAP Linked Server ADSI-02

As we can see query will return Active Directory’s attributed. Also note, that when we query Active Directory it actually creates the SELECT statement backwards. We started the SELECT statement with SELECT pager… but in the results pane it displayed pager last as shown above.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – How to Add Multiple New Columns to Table with Default Values?
Next Post
SQL SERVER – Finding If Status of Bulk Logging Enabled or Not From Logs

Related Posts

20 Comments. Leave new

  • Michael Buescher
    March 31, 2016 1:42 am

    When I used it a while ago it is, in addition to your article, worth to mention the 1000 rows limit of the ADSI connector. I had to create a loop to get the rows in “1k packages”.

    Reply
  • Ricardo Lourenco
    March 31, 2016 6:36 pm

    Hi.
    How can we query AD without Linked Server?
    Thank you

    Reply
  • ติ๊งต๊องไงจะใครล่ะ? (@lovelygirl_byun)
    September 26, 2016 12:17 pm

    Hi.
    when I add description field. It have show “Msg 7346, Level 16, State 2, Line 1
    Cannot get the data of the row from the OLE DB provider “ADSDSOObject” for linked server “ADSI”. Could not convert the data value due to reasons other than sign mismatch or overflow.”

    this my sql command
    SELECT * FROM OpenQuery (
    ADSI,
    ‘SELECT description,displayName
    FROM ”LDAP://aagroup.redicons.local/OU=TAA,OU=AirAsia,DC=aagroup,DC=redicons,DC=local”
    WHERE objectClass = ”User”
    ‘)

    Thank you

    Reply
  • I have the same problem as ติ๊งต๊องไงจะใครล่ะ? (@lovelygirl_byun). Any thoughts on how to retrieve the “description” field?

    Reply
  • Ninfa Hernandez
    March 20, 2018 7:21 pm

    Thank you. I am able to query based on sAMAccountName, but would like to run the query against a temporary table list of accounts on my local SQL Server. Do you have any example of that?

    Reply
  • David Katanski
    May 18, 2018 7:14 am

    How do I un-link the Server? If I restart the server or DB engine is the connection broken?

    Reply
  • thank you for the info. I’m trying to get a list of groups for a specific user but having hard time to convert a dsquery or adfind syntax into the OpenQuery. Can you show me how to pull just the AD Group Names (aka memberOf) for a given user?
    Thanks

    Reply
  • How can we make this work with eDirectory? The linked server and the first query are working for me. I modified the second query to select eDirectory attributes instead of Active Directory attributes but when I try to select CN the second query gives an error “Could not convert the data value due to reasons other than sign mismatch or overflow.” Maybe there’s a way to convert the output and/or strip-out problematic sections of the data.

    Reply
  • Hi – Is there a way to pull the attribute otherMailbox from the Active Directory, like it’s done by powershell?

    Reply
  • Warren J Meyer
    March 1, 2019 11:40 pm

    I also cannot pull “memberof”. Is there a workaround for this?

    Reply
  • Sorry to resurrect an old thread, but can this be set up to use the user’s session credentials.

    Reply
    • FYI I will not use the ADSI output list as credentials, instead i will just dump it as user / resource list to identify your own coded userID / resourceID if you need so

      Reply
  • These instructions are fine as long as the AD returns 9901 rows or less, but after that you need to divide the query into smaller pieces.
    This example divides internal employees into three segments that all fit well within this AD row limit.
    Also we filter out non-active employees.

    SELECT *, 1 AS [Internal] FROM OpenQuery (
    ADSIlim,
    ‘SELECT sAMAccountName, employeeNumber, givenName, mail, company, department, manager, ADsPath
    FROM ”LDAP://OU=User_Accounts,OU=COMPANY,DC=DOMAIN3,DC=DOMAIN2,DC=DOMAIN1”
    WHERE objectCategory = ”Person” AND objectClass = ”User” AND ”userAccountControl:1.2.840.113556.1.4.803:”2 AND SAMAccountName < ''Jzz''
    ')
    UNION ALL
    SELECT *, 1 AS [Internal] FROM OpenQuery (
    ADSIlim,
    'SELECT sAMAccountName, employeeNumber, givenName, mail, company, department, manager, ADsPath
    FROM ''LDAP://OU=User_Accounts,OU=COMPANY,DC=DOMAIN3,DC=DOMAIN2,DC=DOMAIN1''
    WHERE objectCategory = ''Person'' AND objectClass = ''User'' AND ''userAccountControl:1.2.840.113556.1.4.803:''2 AND SAMAccountName >= ”Jzz” AND sAMAccountName < ''Pzz''
    ')
    UNION ALL
    SELECT *, 1 AS [Internal] FROM OpenQuery (
    ADSIlim,
    'SELECT sAMAccountName, employeeNumber, givenName, mail, company, department, manager, ADsPath
    FROM ''LDAP://OU=User_Accounts,OU=COMPANY,DC=DOMAIN3,DC=DOMAIN2,DC=DOMAIN1''
    WHERE objectCategory = ''Person'' AND objectClass = ''User'' AND ''userAccountControl:1.2.840.113556.1.4.803:''2 AND SAMAccountName >= ”Pzz”
    ‘)
    UNION ALL
    SELECT *, 0 AS [Internal] FROM OpenQuery (
    ADSIlim,
    ‘SELECT sAMAccountName, employeeNumber, givenName, mail, company, department, manager, ADsPath
    FROM ”LDAP://OU=User_Accounts_External,OU=COMPANY,DC=DOMAIN3,DC=DOMAIN2,DC=DOMAIN1”
    WHERE objectCategory = ”Person” AND objectClass = ”User” AND ”userAccountControl:1.2.840.113556.1.4.803:”2
    ‘)

    Reply
  • gonzague pichelin
    March 20, 2020 12:17 pm

    Hi, I also get the error message when trying to get description from computers:
    Cannot get the data of the row from the OLE DB provider “ADSDSOObject” for linked server “ADSI”. Could not convert the data value due to reasons other than sign mismatch or overflow.

    Reply
  • gonzague pichelin
    March 20, 2020 12:20 pm

    here is my contribution: to get all users from the ad I have 2 loops with 2 first letters (looping for each aa,ab,ac)
    BEGIN
    SELECT @sChar= CHAR(@nAsciiValue)
    SELECT @nAsciiValue2 = 43
    WHILE @nAsciiValue2 < 96

    BEGIN

    SELECT @sChar2= CHAR(@nAsciiValue2)
    –PRINT @schar+''+@schar2
    SET @cmdstr=@schar
    SET @cmdstr='
    SELECT
    ''user''
    , distinguishedName
    , givenname AS firstname
    , sn AS lastname
    , displayname
    , title
    , objectcategory
    , mail
    , null AS MAID
    , manager
    , company
    , department
    , mobile
    , physicalDeliveryOfficeName
    , sAMAccountName
    , telephoneNumber,
    isnull(case userAccountControl when ''514'' then 0 else 1 end,''0'') status

    FROM OpenQuery ( ADSI, ''
    SELECT
    distinguishedName
    , givenname
    , sn
    , displayname
    , title
    , objectcategory
    , mail
    , manager
    , company
    , department
    , mobile
    , physicalDeliveryOfficeName
    , sAMAccountName
    , telephoneNumber
    , userAccountControl
    FROM ''''LDAP://10.xx.xx.xx:xx/OU=xxxxxx,DC=xxxxx,DC=xxxxx''''
    WHERE displayname='''''+@schar+@schar2+'*''''
    AND objectcategory=''''user'''' ''
    )'

    –PRINT @cmdstr
    INSERT dbo.sync_AD_users
    EXEC sp_executesql @cmdstr
    SELECT @nAsciiValue2 = @nAsciiValue2 + 1
    END

    SELECT @nAsciiValue = @nAsciiValue + 1

    END

    Reply
  • Hi, I am trying to get the LastLogonDate, but I get an error. It works without the LastLogonDate attribute.

    Code:

    SELECT *
    FROM OPENQUERY
    (
    ADSI,
    ‘SELECT LastLogonDate, pager, telephoneNumber, mail, mobile, EmployeeID, department, SAMAccountName, givenname, sn
    FROM ”LDAP://ADSERER.domain.com/DC=ADSERVER,DC=Domain,DC=com”
    WHERE sn = ”Doe”
    and givenname = ”John” ‘
    )

    Error:

    Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing the query “SELECT ,lastLogonTimestamp, lastlogon, pager, telephoneNumber, mail, mobile, EmployeeID, department, SAMAccountName, givenname, sn
    FROM ‘LDAP://ADSERER.domain.com/DC=ADSERVER,DC=Domain,DC=com’
    WHERE sn = ‘Doe’
    and givenname = ‘John’ ” for execution against OLE DB provider “ADsDSOObject” for linked server “ADSI”.

    Reply
  • Correct Error Message:

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “ADsDSOObject” for linked server “ADSI” reported an error. The provider did not give any information about the error.
    Msg 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider “ADsDSOObject” for linked server “ADSI”.

    Reply
  • LastLogonDate isn’t a valid attribute. Use lastLogon and/or lastLogonTimeStamp

    Reply
  • Is there a list of elements I am permitted to query? i.e. I can query telephoneNumber but I get NULLs when querying mobile (and there are values in mobile). It seems there are quite a few strings that do not return values.

    Reply

Leave a Reply

Menu