Can you incorporate something from AD in a SQL query?

Question

Can you incorporate something from AD in a SQL query?

Rod at work

SSC-Dedicated

Points: 34008
More actions
November 19, 2012 at 11:37 am

#144044

We've got a table with a column for Windows logins. Is it possible to perform a join in a SELECT statement that incorporates data from Active Directory? I'd love to get the user's name in the query.
Kindest Regards, Rod Connect with me on LinkedIn.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

yes, with a linked server.

for me, domain information is very static...we don't add new people or move people into groups very often, so I usually take a "snapshot" of the AD and save it in a static table,which i update via a job once a week.

here's a couple of very basic example of a linked server, simply substitute your domain name:

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',

@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',

@datasrc=N'YOURDOMAINNAME'

select * from openquery

(ADSI,'SELECT

cn,

sn,

sAMAccountName,

mail,

telephoneNumber,

mobile,

co,

distinguishedName,

displayName,

physicalDeliveryOfficeName,

department

FROM ''LDAP://yourdomainname''

WHERE objectCategory = ''Person'' AND objectClass = ''user'''

and another:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

select * from openquery

(ADSI,'SELECT cn, mail, co, distinguishedName, displayName

FROM ''LDAP://yourdomain''

where objectClass = ''yourdomain\lizaguirre'' '

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

also, the actual number of fields available to query is daunting; i only grab a few myself;

but this website http://www.rlmueller.net/UserAttributes.htm has a spreadsheet that has the most comprehensive list of AD attributes I've found so far :

http://www.rlmueller.net/References/Schema.xls

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 3

Thank you, Lowell, I've got to explore this. I've never worked with linked servers before, but I think this is worth checking out.

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 4

I should add, this does work with AD 2003, correct? Our servers are kind of old; we're running 2003 R2 AD.

Kindest Regards, Rod Connect with me on LinkedIn.

Lowell SSC Guru Points: 323518 More actions · Answer 5

Rod at work (11/19/2012)
I should add, this does work with AD 2003, correct? Our servers are kind of old; we're running 2003 R2 AD.

yeah, my code examples have been working for years; i'm pretty sure it doesn't matter the version of AD for these examples i posted.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Lowell (11/19/2012)
yes, with a linked server.
for me, domain information is very static...we don't add new people or move people into groups very often, so I usually take a "snapshot" of the AD and save it in a static table,which i update via a job once a week.
here's a couple of very basic example of a linked server, simply substitute your domain name:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',
@datasrc=N'YOURDOMAINNAME'
select * from openquery
(ADSI,'SELECT
cn,
sn,
sAMAccountName,
mail,
telephoneNumber,
mobile,
co,
distinguishedName,
displayName,
physicalDeliveryOfficeName,
department
FROM ''LDAP://yourdomainname''
WHERE objectCategory = ''Person'' AND objectClass = ''user'''
and another:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM ''LDAP://yourdomain''
where objectClass = ''yourdomain\lizaguirre'' '
)

Does this use the SQL Server service account for access to AD? What level of access does it need to have?

Lowell SSC Guru Points: 323518 More actions · Answer 7

it will pass your current credentials,which i assume are your windows credentials;

if you hit it as sa, you'd get the typical error for invalid user.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!