active directory to sql - 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)

active directory to sql

Author

Topic

Anton_FA
Starting Member

5 Posts

Posted - 2004-06-14 : 22:57:53

Greetings

can anyone please tell me how I can import my companies list of employees, their dept, names etc into a sql table?

I have been pulling my hair out on this one for sometime now.

thanks

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-14 : 23:38:49

This works for me:

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

-- Change yourusername and yourpassword to a valid domain login and password. Change yourdomain to your domain and com to your root domain if not com
sp_addlinkedsrvlogin ADSI, FALSE, 'yourusername', 'CN=yourusername,CN=Users,DC=yourdomain,DC=com', 'yourpassword'

--Select all users with names beginning with a. This will only return up to 1000 rows so you may need to retrieve the entire list in batches for a large orginization.
SELECT CN, ADsPath FROM OpenQuery
( ADSI, 'SELECT CN, ADsPath FROM ''LDAP://DC=eprod,DC=COM'' WHERE objectCategory=''person'' AND objectClass=''user'' AND CN = ''a*'' ORDER BY CN')

Anton_FA
Starting Member

5 Posts

Posted - 2004-06-15 : 00:21:00

quote:
Originally posted by kselvia

This works for me:

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

-- Change yourusername and yourpassword to a valid domain login and password. Change yourdomain to your domain and com to your root domain if not com
sp_addlinkedsrvlogin ADSI, FALSE, 'yourusername', 'CN=yourusername,CN=Users,DC=yourdomain,DC=com', 'yourpassword'

--Select all users with names beginning with a. This will only return up to 1000 rows so you may need to retrieve the entire list in batches for a large orginization.
SELECT CN, ADsPath FROM OpenQuery
( ADSI, 'SELECT CN, ADsPath FROM ''LDAP://DC=eprod,DC=COM'' WHERE objectCategory=''person'' AND objectClass=''user'' AND CN = ''a*'' ORDER BY CN')

thanks for the reply.

I tried running that but got errors quite early on in the code.

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'sp_addlinkedsrvlogin'.

Any idea what I did wrong?

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-15 : 05:06:19

Not sure, it worked for me, but you can configure the login from enterprise manager too. Look under security of Enterprise Manager, Linked Servers. ADSI Properties + Security. Check "make connection using this login context" and enter a domain user and password.

Ah, you need EXEC in front of sp_addlinkedsrvlogin if you are runinng all 3 statements in one batch.

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-15 : 18:49:18

exec sp_addlinkedserver 'ADSI1', 'Active Directory Services Interfaces', 'ADSDSOObject', 'adsdatasource'

The following will get you a list of usernames from an OU Provided you have linked server setup
the way it is setup above. You will get errors if Order is not exact (Think of your objects that you wish to query using LDAP from child container to parent) Has to be exact or will fail usually with a vague 0x80040e14 error. The security context is also critical for the linked server as well and will return same error. Edit accordingly to your needs, i'm not sure how you would get the other info you may need.Just remember if like you try to query objects within a childOU for example without also specifiying the parentOU in the ldap query the query will fail.

SELECT * INTO #TEMPTABLE (FROM OPENQUERY(ADSI, 'SELECT cn FROM
''LDAP://ServerName.DomainName.Net,Com etc/OU=ChildOu,OU=ParentOu,DC=YourDomain,DC=Net,Com etc'' WHERE
objectClass = ''user''')

Keithc MCSE MCSA

Anton_FA
Starting Member

5 Posts

Posted - 2004-06-23 : 20:27:01

Hi keithc1

could I ask you to clarify a few things please?

where, in the query that you gave me, do I specify what table I want populated?

Do you know of any way to have the active directory regularly dumped into the sql table?

Also, do you know how I can view the organisation units in winxp pro?

thanks
Anton

Anton_FA
Starting Member

5 Posts

Posted - 2004-06-23 : 20:43:08

OK - well I managed to anwser one of my questions.

I got some AD tools of the win2000 CD.

Anton_FA
Starting Member

5 Posts

Posted - 2004-06-23 : 21:06:39

Hi keith

I've tried several different various of your query, but I keep getting this:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-23 : 22:43:16

Anton sorry for late reply, there is one prob with the query and that is there shouldnt be a parenthese between #temptabele and From
ex:
SELECT * INTO #TEMPTABLE FROM OPENQUERY(ADSI, 'SELECT cn FROM
''LDAP://ServerName.DomainName.Net,Com etc/OU=ChildOu,OU=ParentOu,DC=YourDomain,DC=Net,Com etc'' WHERE
objectClass = ''user''')

Try that instead just fill in your info where needed.

I beleive you could create a stored procedure and have the stored procedure run as a job that would populate a table.

This part of the query populates the temp table with the queried AD objects.
SELECT * INTO #TEMPTABLE
once successful you can run
SELECT * FROM #TEMPTABLE to see the ad objects inserted.
If you want to do a permanent table then just use
Use Database
go
SELECT INTO dbo.TABLENAME FROM OPENQUERY(ADSI, 'SELECT cn FROM
''LDAP://ServerName.DomainName.Net,Com etc/OU=ChildOu,OU=ParentOu,DC=YourDomain,DC=Net,Com etc'' WHERE
objectClass = ''user''')

again where tablename is anything you want to name your table and yes you need admin tools to view OU's from XP.

Keithc MCSE MCSA

IDpro
Starting Member

1 Post

Posted - 2004-06-30 : 08:48:23

There exists a product which provides out-of-the-box functionality for synchronizing LDAP directories and SQL databases. Please check out enSure [url]http://www.persistent.co.in/products/p_ensure.html[/url] a product from Persistent Systems.

You can write to mailto: support@persistentdata.com to get an evaluation copy of enSure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources