How get multi value data form Active Directory using SQL

Question

Is it possible to get multi value properties form AD like description, memberOf. if I run simply by adding memberOf this gives error

select * 
FROM OPENQUERY(ADSI,'SELECT initials, samAccountName, displayName, distinguishedName, mail, memberOf FROM ''LDAP://DC=corp, DC=contoso, DC=com'' WHERE objectClass=''Person''')

Error:

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 is because of memberOf is multi valued property in Active Directory. I am using SQL Server 2008 R2

Answer 1

No, you cannot do this - and there's no "trick" or hack to get it to work, either.

The ADSI provider for SQL Server is rather limited - not supporting multi-valued attributes is one of those limitations.

So you'll need to find another way to do this, e.g. by using SQl-CLR integration and accessing the Active Directory through .NET, or by e.g. exposing the data you need as a web service that you consume from SQL Server.

Answer 2

While you can't use ADSI to return memberof you can query memberof so if you have a group you want to check against you can do the following where extenstionAttribute3 is the employee ID:

SELECT displayName
FROM OPENQUERY(ADSI,
'SELECT displayName
FROM ''LDAP://DC=company,DC=com''
WHERE memberof = ''CN=staff,OU=SharepointGroups,DC=company,DC=com''
AND extensionAttribute3 = ''12345678''
')

If the return value is not null then you can assume the user is part of the group.

Answer 3

Here is my trick / hack for getting this to work:

exec xp_cmdshell 'powershell -command "get-aduser -filter * -properties SamAccountName, <MultiValue> | Select samaccountname, <MultiValue>"' 

Change <MultiValue> to whatever attribute you are trying to pull. It will output the values as comma delimited in SQL. Change the PowerShell cmdlet as needed. All you have to do is collect the output, format it, and join it on your other data. Also, be sure your server has the AD PowerShell module and that you have enabled xp_cmdshell in SQL.

Answer 4

just wrote a sql script to include description (multi-value) field on our company Intranet directory. What I did was export-csv delimited using powershell and then bulk insert that info into a table. The simplest solution for me as we only have about 650 employees (records).

exec xp_cmdshell 'powershell.exe -command "get-aduser -filter * -properties SamAccountName, Description,GivenName,sn,title,telephoneNumber,mobile,mail,physicalDeliveryOfficeName| Select SamAccountName, Description,GivenName,sn,title,telephoneNumber,mobile,mail,physicalDeliveryOfficeName| export-csv -encoding unicode -delimiter "`t" -path C:\SQLJobs\addir.csv -notype"'

Go

CREATE TABLE dbLiftowDir.dbo.ADDir
(
[SamAccountName] NVARCHAR(4000),
[Description] NVARCHAR(4000),
[GivenName] NVARCHAR(4000),
[sn] NVARCHAR(4000),
[title] NVARCHAR(4000)COLLATE French_CI_AS NOT NULL,
[telephoneNumber] NVARCHAR(4000),
[mobile] NVARCHAR(4000),
[mail] NVARCHAR(4000),
[physicalDeliveryOfficeName] NVARCHAR(4000),
)

BULK
INSERT dbLiftowDir.dbo.ADDir
FROM 'C:\SQLJobs\addir.csv'
WITH
(
 CODEPAGE = 'ACP',
   DATAFILETYPE ='char',
   FIELDTERMINATOR = '\t',
   ROWTERMINATOR = '\n',
   FIRSTROW = 2

Other things I did was remove " from field values using set column replace value, and deleting rows that were non-human accounts. As I found it to be easier to do in SQL instead of passing the code into powershell.