How to retrieve Active directory user information through SQL server

Question

Post reply

How to retrieve Active directory user information through SQL server

Parthipan-244146

SSCrazy

Points: 2178
More actions
September 17, 2008 at 12:49 am

#154733

Hi,
Is there any way we can directly retrieve the Active directory user information through SQL server other than the following,
* retrieving the AD information and storing in SQL table through DTS
* There is a way using LDAP also
Anyother solution is there?
BR,
Parthi

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GA.Kramer SSC Eights! Points: 954 More actions · Answer 1

Parthipan,

You can use commandline on your server to retrieve (some of the) AD information.

for example -- exec xp_cmdshell 'net group /domain'

stream it into a table and do your thing.

Hope this pushes you into the right direction.

Regards,

GKramer

The Netherlands

GSquared SSC Guru Points: 260824 More actions · Answer 2

You can use OpenQuery to pull data from the ADSI. For example (secure data removed):

ALTER VIEW [dbo].[vwActiveDirectory]

AS

SELECT

CAST(objectGUID AS BINARY(16)) AS ActiveDirID,

CAST(SAMAccountName AS VARCHAR(20)) AS UserID,

CAST(ISNULL(mail, '') AS VARCHAR(50)) AS Email,

CAST(ISNULL(telephonenumber, '') AS CHAR(4)) AS PhoneExt,

CAST(ISNULL(CN, '') AS VARCHAR(50)) AS FullName,

CAST(ISNULL(givenname, '') AS VARCHAR(50)) AS FirstName,

CAST(ISNULL(SN, '') AS VARCHAR(50)) AS LastName,

CAST(ISNULL(distinguishedname, '') AS VARCHAR(500)) AS Organizations,

CAST(ISNULL(department, '') AS VARCHAR(50)) AS Department,

CAST(ISNULL(facsimileTelephoneNumber, '') AS VARCHAR(12)) AS Fax,

CAST(ISNULL(extensionAttribute1,'') AS VARCHAR(25)) AS SalesClass

FROM OPENQUERY(ADSI,

'

SELECT objectGUID,

CN,

SN,

mail,

telephonenumber,

SAMAccountName,

givenname,

distinguishedname,

department,

facsimileTelephoneNumber,

extensionAttribute1

FROM ''LDAP://servername/DC=****,DC=***''

WHERE userPrincipalName=''*''

AND objectclass= ''person''

AND NOT CN=''#*''

AND NOT SN=''@*''

AND NOT givenname =''****''

')

derivedtbl_1

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 3

Hi GKramer/GSquared,

Sorry for the late reply. Thank you for your valuable information.

I will try it and back to you for any problem.

BR,

Parthi

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 4

I have implemented something similar using a linked server to AD

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. For example (replace the 'x's etc with suitable values for your AD)

SELECT * FROM OpenQuery(ADSI,

'SELECT givenName

, sn

, department

, description

, telephoneNumber

, mail

, userAccountcontrol

FROM ''LDAP://DC=xxx,DC=xx,DC=xxx,DC=xx''

WHERE objectCategory = ''Person'' AND objectClass = ''User''')

note: this post edited as it didn't appear in the thread initially

majorbloodnock SSCrazy Eights Points: 9389 More actions · Answer 5

The only real downside to using ADSI is that you can't easily retrieve information from multi-valued fields (for instance, to retrieve group membership information). However, other than that it works pretty well.

Semper in excretia, suus solum profundum variat

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 6

Thanks alot for all of your information..

As mentioned above there is multivalued properties limitation using ADSI and also can only be able to query 1000 objects as mentioned in below

http://support.microsoft.com/default.aspx?scid=kb;en-us;299410

Is there any body implemented using MIIS 2003 as integration for AD and SQL server which is explained in

http://technet.microsoft.com/en-us/library/cc720650.aspx

BR,

Parthi

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 7

Please look at this posting from a previous topic as regards to how you can query greater than 1000 objects in Active Directory.

http://www.sqlservercentral.com/Forums/Topic472463-149-1.aspx#bm568643]

Shamless self promotion - read my blog http://sirsql.net

sqlagentman SSCarpal Tunnel Points: 4484 More actions · Answer 8

All the previous tips seem like quite a bit of work. I find that the following code block works for my needs:

EXEC xp_logininfo 'DOMAIN\GROUP_NAME', 'members' does the trick.

- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford