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'' ')
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
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)
26 Comments. Leave new
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”.
Hi.
How can we query AD without Linked Server?
Thank you
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
I have the same problem as ติ๊งต๊องไงจะใครล่ะ? (@lovelygirl_byun). Any thoughts on how to retrieve the “description” field?
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?
First get the data into local table and then use join.
How do I un-link the Server? If I restart the server or DB engine is the connection broken?
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
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.
Hi – Is there a way to pull the attribute otherMailbox from the Active Directory, like it’s done by powershell?
I also cannot pull “memberof”. Is there a workaround for this?
Sorry to resurrect an old thread, but can this be set up to use the user’s session credentials.
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
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
‘)
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.
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
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”.
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”.
LastLogonDate isn’t a valid attribute. Use lastLogon and/or lastLogonTimeStamp
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.
I have SQL Server 2019 installed on a server reached remotely.
I have created a linked server in SSMS with Active Directory (installed on the same physical server) to SQL Server
When I test the connection to ADSI in SSMS it is successful
I have also configured Ad Hoc Queries and checked it to make sure a value of 1 was entered
I have specified a security context for ADSI that has access to ADSI including the domain
When I run an OPENQUERY against ADSI in SSMS in the following format:
Set @PersonEmail = (Select Mail FROM OPENQUERY(ADSI, ‘SELECT mail FROM ”LDAP://a.b.c:3268/DC=a,DC=b,DC=c” WHERE objectClass=”User” and objectCategory=”Person”) AS UserMail WHERE sAMAccountName = @Logon)
I still get error 7321.
How do you query for OUs inside an OU?
I’ve tried variations of the following:
SELECT * FROM OpenQuery
(
ADSI,
‘SELECT Name
FROM ”LDAP://hadomain.com/OU=AnOU,DC=HADOMAIN,DC=COM”
WHERE objectClass = ‘Organization-Unit”
‘) AS tblADSI
or
SELECT * FROM OpenQuery
(
ADSI,
‘SELECT Name
FROM ”LDAP://hadomain.com/OU=AnOU,DC=HADOMAIN,DC=COM”
WHERE objectClass = ‘organizationUnit”
‘) AS tblADSI
What am I doing wrong here?