Active Directory Users into Sql Server Table

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

Development Tools

ASP.NET

Active Directory Users into Sql Server Table

Author

Topic

saidev
Posting Yak Master

101 Posts

Posted - 2012-04-21 : 10:32:11

Hi Guys,

I am trying to retrieve all the users in the Active Directory and dump into a Sql Server Table(Sql2008). I was able to search one employee at a time but could not able to search all the employees in the Active Directory. Basically i want to retrieve 1.SamAccountname and 2.Employeenumber. Can you guys help me with the code?
I am using VS2010/VB.NET/ASP.NET. Appreciate your help.
Thanks

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-04-22 : 09:59:21

As long as you have knowledge of the LDAP path - then here are some notes:http://www.sqlserver-dba.com/2012/02/sql-server-powershell-active-directory-search.html
Note: this is tested on SQL Server 2005 . and is using powershell.
Once you can retrieve the recordset - INSERT into a SQL Server table

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Sachin.Nand

2937 Posts

Posted - 2012-04-23 : 05:49:42

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

The above article shows how powershell can be used to query AD even for a particular OU level .

After Monday and Tuesday even the calendar says W T F ....

saidev
Posting Yak Master

101 Posts

Posted - 2012-04-23 : 14:39:38

quote:
Originally posted by jackv

As long as you have knowledge of the LDAP path - then here are some notes:http://www.sqlserver-dba.com/2012/02/sql-server-powershell-active-directory-search.html
Note: this is tested on SQL Server 2005 . and is using powershell.
Once you can retrieve the recordset - INSERT into a SQL Server table

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Hi,

Thanks for the info. I didn't find any from the link you provided.
I was able to export the data to excel file. I want to insert the data into sql table. Please see my code below and if you could modify my code to insert the data into sql table, i really appreciate your help. Thanks

Private Sub getADDetails()

Dim dirEntry As System.DirectoryServices.DirectoryEntry

Dim dirSearcher As System.DirectoryServices.DirectorySearcher

Try

dirEntry = New System.DirectoryServices.DirectoryEntry("LDAP://Servername/DC=ffe,DC=fcted,DC=com")

dirSearcher = New System.DirectoryServices.DirectorySearcher(dirEntry)

Dim objStreamWriter As StreamWriter
objStreamWriter = New StreamWriter("\c:Userlist1.csv")

For Each sResultSet As SearchResult In dirSearcher.FindAll
objStreamWriter.Write(GetProperty(sResultSet, "employeenumber"))
objStreamWriter.Write(GetProperty(sResultSet, "mail"))
objStreamWriter.WriteLine(GetProperty(sResultSet, "samaccountname"))

Next
objStreamWriter.Close()
Catch ex As Exception
End Try
End Sub

Private Shared Function GetProperty(ByVal searchResult As SearchResult, ByVal PropertyName As String) As String
If searchResult.Properties.Contains(PropertyName) Then
Return searchResult.Properties(PropertyName)(0).ToString
Else
Return String.Empty
End If
End Function

saidev
Posting Yak Master

101 Posts

Posted - 2012-04-23 : 16:44:48

quote:
Originally posted by saidev

quote:
Originally posted by jackv

As long as you have knowledge of the LDAP path - then here are some notes:http://www.sqlserver-dba.com/2012/02/sql-server-powershell-active-directory-search.html
Note: this is tested on SQL Server 2005 . and is using powershell.
Once you can retrieve the recordset - INSERT into a SQL Server table

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Hi,

Thanks for the info. I didn't find any from the link you provided.
I was able to export the data to excel file. I want to insert the data into sql table. Please see my code below and if you could modify my code to insert the data into sql table, i really appreciate your help. Thanks

Private Sub getADDetails()

Dim dirEntry As System.DirectoryServices.DirectoryEntry

Dim dirSearcher As System.DirectoryServices.DirectorySearcher

Try

dirEntry = New System.DirectoryServices.DirectoryEntry("LDAP://Servername/DC=ffe,DC=fcted,DC=com")

dirSearcher = New System.DirectoryServices.DirectorySearcher(dirEntry)

Dim objStreamWriter As StreamWriter
objStreamWriter = New StreamWriter("\c:Userlist1.csv")

For Each sResultSet As SearchResult In dirSearcher.FindAll
objStreamWriter.Write(GetProperty(sResultSet, "employeenumber"))
objStreamWriter.Write(GetProperty(sResultSet, "mail"))
objStreamWriter.WriteLine(GetProperty(sResultSet, "samaccountname"))

Next
objStreamWriter.Close()
Catch ex As Exception
End Try
End Sub

Private Shared Function GetProperty(ByVal searchResult As SearchResult, ByVal PropertyName As String) As String
If searchResult.Properties.Contains(PropertyName) Then
Return searchResult.Properties(PropertyName)(0).ToString
Else
Return String.Empty
End If
End Function

When i export the data to Excel the 3 fields i am retrieving are merged. Can you guys help me on how to get the data into excel with individual columns? Appreciate your help. Please see below

41693Kei.Wetzle@sports.netKWetzle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources