Work around for issue retrieving description field from AD via LDAP

working on an ETL project where we need to tie information from the AD server to the ERP system to get SSO logins.  I need to relate the Employee ID found in the ERP system to an Employee ID in the AD. I was trying to do this from SQL running in a SSIS package.

The problem is that the Employee ID in the AD is stored in the description attribute... i know... couldn't have been easy and stored in the EmployeeID or EmployeeNumber attribute right?  I can connect to the AD  and read the other attributes, but I get the "Could not convert the data value due to reasons other than sign mismatch or overflow" when trying to read the description attribute.  After some research, I understand it is a common problem because LDAP reports that value as a multi-value.  I found a supposed workaround on the Experts Exchange site, but that doesn't appear to work as it is also accessing via an OpenQuery (ADSI, ...... LDAP://.....  query.  

Just wondering if there is a work-around with SQL?  or if it is possible to pull a list of the description and mail fields via  .NET?  if .NET, could you point me to some examples?  While I do understand some C#/VB, I'm not an expert in that regard, especially when accessing AD.

Thanks
Wayne RushingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vadim RappCommented:
You can create COM object or assembly that will pull the information from AD; you can then call that DLL from T-SQL. Here's documentation: Create CLR Functions

Here's the sample code:
        Dim DefaultNamingContext As String, Results As SearchResultCollection
        Dim Searcher As DirectorySearcher, RootEntry As New DirectoryEntry("LDAP://RootDSE")
        DefaultNamingContext = RootEntry.Properties("defaultNamingContext").Value
        RootEntry = New DirectoryEntry(("GC://" & DefaultNamingContext).ToString)
        Searcher = New DirectorySearcher(RootEntry)
        Searcher.PropertiesToLoad.Add("description")

        Dim login As String = "myusername"
        Searcher.Filter =  String.Format("(&(sAMAccountName={0})(objectCategory=person)(objectclass=user))", login)
        Results = Searcher.FindAll()
        Dim Description = Results(0).Properties("description")(0).ToString

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
larryhSr. Software EngineerCommented:
I wrote a C# script that is included in my SSIS package.  Our script imports selected AD attributes into a SQL table.  Here's a snippet of the code we use for this attribute:

if (result.Properties.Contains("description"))
{
    description = result.Properties["description"][0].ToString().Trim();
}
else
    description = "";

Open in new window


I check for the existence of the attribute because sometimes it's not there in our environment.
0
Wayne RushingAuthor Commented:
I want to thank Vadim and larryh for their input.  Vadim, thanks for the complete script and links.  larry, looking at your snipet would be helpful as to how to pick out the specific field.  I greatly appreciate your help.

I actually ended up doing something  similar, but slight different, so just wanted to share in case that helps others:
Public Sub Main()
	Dim vars As Variables = Nothing
	Dim adRawFile As String = Nothing

	Dim employeeID As String = Nothing
	Dim mail As String = Nothing
	Dim sn As String = Nothing
	Dim givenName As String = Nothing
	Dim initials As String = Nothing
	Dim description As String = Nothing

	'Get the variable values from the package
	Dts.VariableDispenser.LockForRead("ADRawFile")

	Dts.VariableDispenser.GetVariables(vars)

	adRawFile = CType(vars("ADRawFile").Value, String)

	vars.Unlock()

	'Get the employee information from the AD using a DirectorySearcher
	Using dirEntry As New DirectoryServices.DirectoryEntry("LDAP://TEST.com/DC=TEST,DC=com")

		Using dirSearcher As New System.DirectoryServices.DirectorySearcher(dirEntry)

			Dim results As SearchResultCollection
			Dim result As SearchResult

			If File.Exists(adRawFile) Then
				File.Delete(adRawFile)
			End If

			' filtering: only users where sn AND (EmployeeID OR description) is populated AND AD account is enabled
			dirSearcher.Filter = "(&(objectCategory=person)(objectClass=user)(mail=*)(sn=*)(|(EmployeeID=*)(description=*))(!(userAccountControl:1.2.840.113556.1.4.803:=2)))"

			dirSearcher.PropertiesToLoad.Add("EmployeeID")
			dirSearcher.PropertiesToLoad.Add("sn")
			dirSearcher.PropertiesToLoad.Add("initials")
			dirSearcher.PropertiesToLoad.Add("givenName")
			dirSearcher.PropertiesToLoad.Add("mail")
			dirSearcher.PropertiesToLoad.Add("description")

			dirSearcher.PageSize = 1000

			dirSearcher.SearchScope = SearchScope.Subtree

			results = dirSearcher.FindAll()

			Using strWriter As New StreamWriter(adRawFile)

				'strWriter.WriteLine(DateTime.Now.ToString())

				strWriter.WriteLine("EmployeeID,mail,sn,givenName,initials,description")

				For Each result In results

					employeeID = Nothing
					mail = Nothing
					sn = Nothing
					givenName = Nothing
					initials = Nothing
					description = Nothing

					employeeID = CType(result.GetDirectoryEntry().Properties.Item("EmployeeID").Value, String)
					mail = CType(result.GetDirectoryEntry().Properties.Item("mail").Value, String)
					sn = CType(result.GetDirectoryEntry().Properties.Item("sn").Value, String)
					givenName = CType(result.GetDirectoryEntry().Properties.Item("givenName").Value, String)
					initials = CType(result.GetDirectoryEntry().Properties.Item("initials").Value, String)
					description = CType(result.GetDirectoryEntry().Properties.Item("description").Value, String)

					If Not String.IsNullOrEmpty(employeeID) Then
						employeeID = employeeID.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim()
					End If

					If Not String.IsNullOrEmpty(mail) Then
						mail = mail.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim().ToLower
					End If

					If Not String.IsNullOrEmpty(sn) Then
						sn = sn.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim().ToUpper
					End If

					If Not String.IsNullOrEmpty(givenName) Then
						givenName = givenName.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim().ToUpper
					End If

					If Not String.IsNullOrEmpty(initials) Then
						initials = initials.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim().ToUpper
					End If

					If Not String.IsNullOrEmpty(description) Then
						description = description.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim()
					End If

					strWriter.WriteLine(employeeID + "," + mail + "," + sn + "," + givenName + "," + initials + "," + description)
				Next

				'strWriter.WriteLine(DateTime.Now.ToString())

			End Using
		End Using
	End Using

	Dts.TaskResult = ScriptResults.Success
End Sub

Open in new window

0
larryhSr. Software EngineerCommented:
Glad you figured out a solution.  It looks like you are writing to a flat file and then I imagine you will import that file into a table using a separate SSIS step.  We just did the INSERT statements in the C# script directly rather than a bulk import.  I can see how using a file and importing it might be faster.  However, I think that most of the performance issues are in querying Active Directory rather than inserting records.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.