Retrieving data from Active Directory to SQL Server 2005

We will use Visual Basic script to retrieve data from Active Directory. This script will pass data to the SQL Server procedure, which will insert it to the database table.

Just for Note: I used WinNT in visual basic instead of LDAP, and it was smaller script than this one, which I have written below:). But when I was trying to run this script error code: 8000500D was arising, error was indicating to the department property… Unfortunately, I couldn’t find the solution how to retrieve department property using WinNT, that is why I decided to write code by LDAP(code is written below).

So let’s start.

1. Connect to the database.

–First of all let’s create a table, which will hold this information.

CREATE TABLE [ADUsersTable] (
[UserId]     [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Username]   [nvarchar] (1000),
[FullName]   [nvarchar] (1000),
[Department] [nvarchar] (1000)
)

Note: If IDENTITY(1,1)option is new for you, click here to clarify.

–Create procedure.

CREATE PROCEDURE ad_addProc
@Username   nvarchar(1000),
@Fullname   nvarchar(1000),
@Department nvarchar(1000)
AS
BEGIN
   INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
   VALUES(@Username ,@Fullname,@Department)
END

2. On the computer,  where SQL server is installed, create a text file, insert the following Visual Basic script and rename this file to AdUsers.vbs

Option Explicit
Dim oDomain
Dim MyConnection
Dim MyCommand
Dim par1
Dim par2
Dim par3
‘Domain is bg.ge
Set oDomain = GetObject("LDAP://dc=bg,dc=ge")
Set MyConnection = CreateObject("ADODB.Connection")
'Create SQL connection string,  to connect to the database.
MyConnection.Open "Driver={SQL Server};server(local);
database=DBname;uid=BOG0\mkupatadze;pwd=*****;Trusted_Connection=yes;"

Set MyCommand = CreateObject("ADODB.Command")
Set MyCommand.ActiveConnection = MyConnection
Set par1 = MyCommand.CreateParameter("@Username", 202, 1, 1000)
Set par2 = MyCommand.CreateParameter("@Fullname", 202, 1, 1000)
Set par3 = MyCommand.CreateParameter("@Department", 202, 1, 1000)
'Note that ad_addProc is the procedure created in SQL Server database.
MyCommand.CommandText = "ad_addProc"
MyCommand.CommandType = 4
MyCommand.Parameters.Append par1
MyCommand.Parameters.Append par2
MyCommand.Parameters.Append par3
RetrDataFromAD(oDomain)
‘Write the recursive function.
Sub RetrDataFromAD(oArray)
   Dim oADObject
   For Each oADObject in oArray
     ‘If error occurs, script will not be terminated.
     On Error Resume Next
     ‘Clearing  values of parameters
     par1.Value =" "
     par2.Value =" "
     par3.Value =" "
     Select Case oADObject.Class
       Case "user"
          ‘sAMAccountName is the username
          par1.Value = oADObject.Get("sAMAccountName")
          par2.Value = oADObject.Get("name")
          par3.Value = oADObject.Get("department")
          MyCommand.Execute
       Case "organizationalUnit" , "container"
          RetrDataFromAD(oADObject)
     End Select
    Next
End Sub
MyConnection.Close()

Double Click on that file and to see the result run the following query:

Select *
from [ADUsersTable]