Archive for the ‘MSSQL Server’ Category

Importing Active Directory data into SQL Server…the simple way.

January 6th, 2009

…”I will keep it simple while active directory querying let me do so”…

In the past three years I was several times asked to import Active Directory user and computer information into SQL Server. Unfortunately, I was unable to find any complete example or any sort of clear instructions on how to do this. After much research and trial and error, I was finally able to put together the rather simple method described below:

1. Create Active Directory Linked Server in your MSSQL Server instance.

EXEC master.dbo.SP_ADDLINKEDSERVER @server = N'ADSI',
                  @srvproduct=N'Active Directory Services',
                  @provider=N'ADsDSOObject',
                  @datasrc=N'DomainControllerName.Domain.com'

This will be necessary to be able to query Active Directory data from MSSQL.

2. Create the main stored procedure that will perform the query on Active Directory using the Linked Server.

CREATE PROCEDURE [dbo].[LDAP_sp_QueryActiveDirectory] (@FieldsList AS VARCHAR(1200) = Null,
                                                       @OBJClass AS VARCHAR(25) = Null,
                                                       @OBJCategory AS VARCHAR(25) = Null,
                                                       @RootOU AS VARCHAR(200) = Null,
                                                       @ChildOU AS VARCHAR(200) = Null)
              AS
                 -- DNS identificators ( Domain Components )
                    DECLARE @DNSId AS VARCHAR(25)
                        SET @DNSId = 'DC=yourDOMAIN,DC=COM'''
 
				-- LDAP Linked Server
				DECLARE @LDAPserver AS VARCHAR(25)
					SET @LDAPserver = 'ADSI'
 
				-- LDAP FILTERING
				DECLARE @LDAPstring AS VARCHAR(500)
 
                     -- One level Search
					 IF UPPER(@ChildOU) IS Null and UPPER(@RootOU) <> 'COMPUTERS'
                        SET @LDAPstring = '''LDAP://OU=' + @RootOU + ',' + @DNSId
 
                     -- Get everything
                     ELSE IF UPPER(@RootOU) IS Null and UPPER(@ChildOU) IS Null
						SET @LDAPstring = '''LDAP://' + @DNSId
 
                     -- Containers CNs
                     -------------------------------------------------------------------			
 
                     -- CN Computers Search
                     ELSE IF UPPER(@ChildOU) IS Null and UPPER(@RootOU) = 'COMPUTERS'
                        SET @LDAPstring = '''LDAP://CN=' + @RootOU + ',' + @DNSId
 
                     -- CN Users Search
                     ELSE IF UPPER(@ChildOU) IS Null and UPPER(@RootOU) = 'USERS'
                        SET @LDAPstring = '''LDAP://CN=' + @RootOU + ',' + @DNSId
 
                     -- CN Other Searches
                     ELSE IF UPPER(@RootOU) = 'SYSTEM'
                                OR UPPER(@RootOU) = 'MICROSOFT EXCHANGE SYSTEM OBJECTS'
                                OR UPPER(@RootOU) = 'BUILTIN'
                        SET @LDAPstring = '''LDAP://CN=' + @ChildOU + ',CN=' + @RootOU  +',' + @DNSId
 
                     -------------------------------------------------------------------
 
                     -- 2 OU Levels Search
                     ELSE
                    	SET @LDAPstring = '''LDAP://OU=' + @ChildOU + ',OU=' + @RootOU + ',' + @DNSId
 
                 -- Fields to Retrive from Active directory service
				 IF UPPER(@FieldsList) IS Null
				      SET @FieldsList = ' cn ' 
 
				-- LDAP Query
				DECLARE @LDAPQuery AS VARCHAR(2000)
 
                    SET @LDAPQuery = 'SELECT ' + @FieldsList + ' FROM ''' + @LDAPstring + ''' WHERE'
 
						IF  @OBJCategory  IS not Null
                         	SET @LDAPQuery = @LDAPQuery + ' objectCategory = '''''+ @OBJCategory +''''''
 
                        IF @OBJClass IS not Null
							SET @LDAPQuery = @LDAPQuery + ' AND objectClass='''''+ @OBJClass +''''''
 
				-- Complete Query
				DECLARE @OPENQUERY AS VARCHAR(5000)
					SET @OPENQUERY = 'SELECT TOP 100 PERCENT Rowset.*
									    FROM OPENQUERY('+ @LDAPserver +',''' + @LDAPQuery + ' AND userAccountControl>1000'') AS Rowset
									UNION
									SELECT TOP 100 PERCENT Rowset.*
 										FROM OPENQUERY('+ @LDAPserver +',''' + @LDAPQuery + ' AND userAccountControl<1000'') AS Rowset'
 
				-- Run Complete Query
				EXEC (@OPENQUERY)

The final query (above) is, as you can see, divided in two. One part retrieves the first 1000 rows and the other part the rest. This is a workaround over the limitation of Active Directory queries which will only return 1000 records per request.

A brief description of the parameters.

@FieldsList
List of fields you want to be returned.
Fields listed will be returned in reverse order

Example:
‘displayName, sn , givenName’ will be returned as ‘givenName, sn ,displayName’

@OBJClass
LDAP Object Class.
Possible values could be: ‘user’,'computer’,'MIS’,… (depends on your companies AD OU structure.)

@OBJCategory
LDAP Object Category.
Possible values could be: ‘Person’,'Computer’,… (depends on your companies AD OU structure.)

@RootOU
LDAP Root Organizational Units.
Possible values could be: ‘MIS’,'Corporate’,etc.. (depends on your companies AD OU structure.)

@ChildOU
Child of the specified Root OU.
Possible values could be: ‘Accounting’,'Executive’, … (depends on your companies AD OU structure.)

Possible usage Examples:

Retrieve Users

                 LDAP_sp_QueryActiveDirectory 'wWWHomepage,
							    info,
							    ipPhone,
							    facsimileTelephoneNumber,
							    pager,
							    mobile,
							    homePhone,
							    telephoneNumber,
							    c,
							    co,
							    postalCode,
							    st,
							    l,
							    streetAddress,
							    physicalDeliveryOfficeName,
							    department,
							    company,
							    title,
							    mail,
							    sAMAccountName,
							    displayName,
							    sn,
							    initials,
							    givenName',
  						            'user','Person','TEST'

Retrieve Computers

           LDAP_sp_QueryActiveDirectory 'location,
						      description,
						      operatingSystemServicePack,
						      operatingSystemVersion,
						      operatingSystem,
						      dnsHostName,
						      cn',
 						      'Computer','Computer','Computers'

3. Create the table where you will import the records obtained from Active Directory.

CREATE TABLE [dbo].[Employee_List](
	[EmplyeeID] [NUMERIC](18, 0) IDENTITY(1,1) NOT NULL,
	[PhoneNumber] [VARCHAR](25)  NULL,
	[Extension] [VARCHAR](50)  NULL,
	[FirstName] [VARCHAR](50)  NULL,
	[LastName] [VARCHAR](50)  NULL,
	[Department] [VARCHAR](50)  NULL,
	[Location] [VARCHAR](50)  NULL,
	[Type] [VARCHAR](10)  NULL,
	[Email] [VARCHAR](80)  NULL,
	[MiddleName] [VARCHAR](50)  NULL,
	[OfficeAddress] [VARCHAR](500)  NULL,
	[OfficeZipCode] [VARCHAR](50)  NULL,
	[OfficeCity] [VARCHAR](50)  NULL,
	[OfficeState] [VARCHAR](25)  NULL,
	[OfficeCountry] [VARCHAR](50)  NULL,
	[HomePhone] [VARCHAR](50)  NULL,
	[MobilePhone] [VARCHAR](50)  NULL,
	[FaxNumber] [VARCHAR](50)  NULL,
	[Notes] [VARCHAR](50)  NULL,
 CONSTRAINT [PK_Employee_List] PRIMARY KEY NONCLUSTERED
(
	[EmployeeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

4. Create the stored procedure that will dump the Active Directory data into the MSSQL table. It will need to be scheduled to run every night (I recommend) in order to import changes from Active Directory.

ALTER  PROCEDURE [dbo].[LDAP_sp_TD_ActiveDirectory_Users]
             AS
                SET NOCOUNT ON
 
                -- FIRST STEP: CLEAN UP SQL TABLE
 
	 	TRUNCATE TABLE Employee_List
 
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects sys WHERE sys.xtype in ('U') AND sys.id = OBJECT_ID(N'tempdb..#Employees'))
           DROP TABLE #Employees
 
         	CREATE TABLE #Employees
                                (
                                        [PhoneNumber] [VARCHAR] (25)  NULL ,
					[Extension] [VARCHAR] (50)  NULL ,
					[FirstName] [VARCHAR] (50)  NULL ,
					[LastName] [VARCHAR] (50)  NULL ,
					[Department] [VARCHAR] (50)  NULL ,
					[Location] [VARCHAR] (50)  NULL ,
					[Type] [VARCHAR] (10)  NULL ,
					[Email] [VARCHAR] (80)  NULL ,
					[MiddleName] [VARCHAR] (50)  NULL ,
					[OfficeAddress] [VARCHAR] (500)  NULL ,
					[OfficeZipCode] [VARCHAR] (50)  NULL ,
					[OfficeCity] [VARCHAR] (50)  NULL ,
					[OfficeState] [VARCHAR] (25)  NULL ,
					[OfficeCountry] [VARCHAR] (50)  NULL ,
					[HomePhone] [VARCHAR] (50)  NULL ,
					[MobilePhone] [VARCHAR] (50)  NULL ,
					[FaxNumber] [VARCHAR] (50)  NULL ,
					[Notes] [VARCHAR] (50)  NULL ,
 					[Status] INT NULL
                                  )
 
-- SECOND STEP: POPULATE TABLE WITH FRESH LDAP DATA
INSERT INTO #Employees
   (
	 [PhoneNumber]
    ,[FirstName]
    ,[LastName]
    ,[Department]
    ,[Location]
    ,[Email]
    ,[MiddleName]
    ,[OfficeAddress]
    ,[OfficeZipCode]
    ,[OfficeCity]
    ,[OfficeState]
    ,[OfficeCountry]
    ,[HomePhone]
    ,[MobilePhone]
    ,[FaxNumber]
    ,[Notes]
    ,[Status]
 
   )
        EXEC LDAP_sp_QueryActiveDirectory '
											  useraccountcontrol,
                                                                                          Info,
											  facsimileTelephoneNumber,
											  mobile,
											  homePhone,
											  c,
											  st,
											  l,
											  postalCode,
											  streetAddress,
											  initials,
											  mail,
											  physicalDeliveryOfficeName,
											  department,
											  sn,
											  givenName,
											  telephoneNumber',
											  'user','Person' 
 
       -- THIRD STEP: UPDATE EXTENSIONS
       UPDATE #Employees SET Extension = RIGHT(PhoneNumber,4)
 
       -- FOURTH STEP: DELETE SERVICE ACCOUNTS
	 DELETE FROM #Employees WHERE LOWER(LEFT(FirstName,3)) = 'service_account_prefix' and LastName IS Null
 
       -- FIFTH STEP: DELETE DISABLED ACCOUNTS
         DELETE FROM #Employees WHERE Status in (514,546,66050) 
 
-- POPULATE TABLE
INSERT INTO Employee_List
([PhoneNumber]
    	      ,[FirstName]
	      ,[LastName]
	      ,[Department]
	      ,[Location]
	      ,[Email]
	      ,[MiddleName]
	      ,[OfficeAddress]
	      ,[OfficeZipCode]
	      ,[OfficeCity]
	      ,[OfficeState]
	      ,[OfficeCountry]
	      ,[HomePhone]
	      ,[MobilePhone]
	      ,[FaxNumber]
	      ,[Notes]
)
       SELECT  [PhoneNumber]
    	      ,[FirstName]
	      ,[LastName]
	      ,[Department]
	      ,[Location]
	      ,[Email]
	      ,[MiddleName]
	      ,[OfficeAddress]
	      ,[OfficeZipCode]
	      ,[OfficeCity]
	      ,[OfficeState]
	      ,[OfficeCountry]
	      ,[HomePhone]
	      ,[MobilePhone]
	      ,[FaxNumber]
	      ,[Notes] FROM  #Employees

I added several filtering steps after retrieving the records to the temporary table. You are free to comment them out or modify them in order, for example, to just modify a bit column for disabled accounts and not delete them as I am doing. See source comments (THIRD to FIFTH steps)

If you are curious about what other properties you can retrieve from an object you can use “ADSIEdit.msc” from the “Windows 2003 Server Support Tools” that can be found in \SUPPORT\TOOLS\SUPTOOLS.MSI in the Windows 2003 Server CD or for download from the Microsoft download site. This is a helpful tool that will expose the field names that you can use in your query and its values, but bear in mind that it depends on your permissions (aka. Contact your closest network administration franchise).

4. Schedule a job in your MSSQL Agent in order to run this stored procedure every night and you will be good to go.

Forgotten Notes:

I forgot to mention an issue that occurs when trying to return “User Groups” in your query. Active Directory User groups are stored in an array like field that can not be casted by the OLE DB in MSSQL.

Tags: , , ,
Posted in Active Directory, MSSQL Server, TSQL, Windows 2003 Server | Comments (0)