Get data from Active Directory and store onto SQL server

Question

Get data from Active Directory and store onto SQL server

iLoveSQL

SSCrazy

Points: 2888
More actions
January 6, 2005 at 7:39 am

#84506

Is there anyone done this kind of task?
Bascially, in Active Directory, we have a list of computers and certain discriptions in it. Is there a way to pull those data and dump into SQL server hopefully not all but based on some criteria.
Thank you in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

There are several ways to go about this. You can do so via a linked server connection using the ADSI provider or you can use the ADSI objects in a script and put the information in SQL Server via ADO. It probably depends on how much processing you need to do on the data before inserting into SQL Server.

More on the subject:

INFO: Performing a SQL Distributed Query by Using ADSI (299410)

Microsoft TechNet Script Center: Computer Objects

K. Brian Kelley
@kbriankelley

iLoveSQL SSCrazy Points: 2888 More actions · Answer 2

This is exactly right information that I was looking for.

Thanks Brian.

Kun

iLoveSQL SSCrazy Points: 2888 More actions · Answer 3

Hi,

I think I need the real example. Those documentation doesn't include to to link server the AD. I tried below but keep getting error message as below. Do you have any good example how to create linkserver from SQL?

Thank you so much,

USE master

GO

-- To use named parameters:

EXEC sp_addlinkedserver

@server = 'ADSI',

@provider = 'Active Directory Service Interfaces',

@srvproduct = 'ADSDSOObject',

@datasrc = '[myserver.domain.com]'

GO

SELECT title, telephoneNumber

From 'ADSI://DC=[domain], DC=COM'

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

-- Error message

Server: Msg 7403, Level 16, State 1, Line 1

Could not locate registry entry for OLE DB provider 'Active Directory Service Interfaces'.

OLE DB error trace [Non-interface error: Provider not registered.].

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

ADSI is a system component in Windows 2000. It's not installed by default under NT 4.0 (as would be expected since NT 4.0 preceded AD). If you're on NT 4.0 you'll have to download and install the provider. Make sure that's not an issue.

Also, check to see that you create a linked server based on the following instructions:

Platform SDK Docs on Creating Linked Server with ADSI

K. Brian Kelley
@kbriankelley

iLoveSQL SSCrazy Points: 2888 More actions · Answer 5

iLoveSQL

SSCrazy

Points: 2888

February 9, 2005 at 2:57 pm

#541469

Thanks,

I made the connection work.

Jim Clay Newbie Points: 5 More actions · Answer 6

Can you share how you made the connection work. I am trying to connect to AD and can't seem to find the right combination of settings.

TIA,

Jim

Joshua-227326 Grasshopper Points: 11 More actions · Answer 7

I too am looking for a way to query my Active Directory environment through SQL. I followed all the steps and was able to create the link. When I run the statement to create the view and select data from it i get the following errors:

Error: An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.

[Executed: 4/21/05 2:46:30 PM EDT ] [Execution: 0/ms]

Error: Invalid object name 'viewADContacts'.

[Executed: 4/21/05 2:46:30 PM EDT ] [Execution: 0/ms]

Any help would be greatly appreciated.

Thank you

John Klaassen Newbie Points: 7 More actions · Answer 8

OK...call me mister ignorant, but I really don't know the answer to this.

In the code example posted below:

USE master

GO

-- To use named parameters:

EXEC sp_addlinkedserver

@server = 'ADSI',

@provider = 'Active Directory Service Interfaces',

@srvproduct = 'ADSDSOObject',

@datasrc = '[myserver.domain.com]'

GO

SELECT title, telephoneNumber

From 'ADSI://DC=[domain], DC=COM'

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

Where is this run from? While logged in where?

My need to to run a query against some Active Directory information from a Oracle database instance. Clues would be greatly appreciated.

Thank you,

John

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 9

This runs from the SQL Server against a domain controller. This assumes the computer's domain because a path to a particular domain or domain controller is not specified. I believe it also runs under the context of the SQL Server service account.

K. Brian Kelley
@kbriankelley

STTR-318080 Grasshopper Points: 10 More actions · Answer 10

Link server:

**************************************************

USE master

GO

exec sp_addlinkedserver @server=N'ADSI',

@srvproduct=N'"Active Directory"',

@provider=N'ADsDSOObject',

@datasrc=N'"ldap://server.name.local:389"',

@location=NILL, -- DBPROP_INIT_LOCATION

@provstr=N'ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648',

@catalog=NILL -- DBPROP_INIT_CATALOG

GO

exec sp_serveroption 'ADSI','Data Access',TRUE

exec sp_serveroption 'ADSI','rpc',TRUE

exec sp_serveroption 'ADSI','rpc out',TRUE

exec sp_addlinkedsrvlogin

@rmtsrvname='ADSI',

@useself=TRUE

**************************************************

request:

**************************************************

SELECT sn AS [Surname], givenName AS [Name], userPrincipalName AS [Account] FROM openquery(ADSI,

'SELECT userPrincipalName, givenName, sn FROM ''LDAP://server.name.local'' WHERE objectClass = ''user''')

WHERE sn IS NOT NULL AND givenName IS NOT NULL ORDER BY sn, givenName

**************************************************

SQL Server 2005/2000 Ok.