Querying AD from SQL 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Querying AD from SQL 2000

Author

Topic

fatherjack
Starting Member

3 Posts

Posted - 2004-07-01 : 12:20:05

Hi,
I am a SQL DBA but am new to AD.

I need to extract data from AD and place it in SQL tables but am at a loss as to how i can relate information in AD. I have taken Group names from AD and put them in a table but i cannot get them to link to the users in AD to see which users are in which group (or indeed which groups a user belongs to). Until i can link users and groups there seems little point extracting User data.

I am also getting lots of errors alluding to data type conversion failures despite me trying to get over it with Convert statements. This seems to happen most on date fields, the Description field and the Member field. ([COLUMN_NAME=member STATUS=DBSTATUS_E_CANTCONVERTVALUE]).

Any help offered is greatly appreciated, AD is supposed to be great but i dont see it yet!!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-01 : 16:27:10

You need to setup a linked server to AD:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36289

Tara

fatherjack
Starting Member

3 Posts

Posted - 2004-07-02 : 03:57:02

Tara,
I have the linked server already. I have extracted data but cannot see how to link data from different classes (or categories - i am not sure of the terms of reference). Also certain fields are not returning any data at all.

e.g.
I have run this:

select convert(varchar(100), [Name]) as GroupName
-- , convert(varchar(100), [Description]) as GroupDescription
-- , type
-- , convert (varchar(250),[member]) as [Member]
from openquery(ADSI,
'select name
from ''LDAP://DC=wan,DC=net''
where objectClass = ''Group''')

** NOTE: un-commenting the description, type or member fields in the outer select (yes i put them in the inner one too!) causes errors in conversion

More importantly, I cannot link the above data to the result set from :

SELECT *
FROM
OpenQuery( ADSI,'<LDAP://DC=wan,DC=net>;(&(objectCategory=Person)(objectClass=user));
name, userprincipalname, useraccountcontrol, physicaldeliveryofficename, adspath;subtree')
where physicaldeliveryofficename is not null
and userprincipalname is not null
and useraccountcontrol != 514 -- standard account that is disabled ie 512 + 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources