Post reply

Import Active Directory objectGUID using .Net Provider for MS Directory Services

  • August 17, 2020 at 4:22 pm

    #3781049

    For a few years now I have been using SSIS and a ".Net Provider for OleDB\OLE DB Provider for Microsoft Directory Services" Connection in to extract Active Directory account information into a text file, so that I can cross check AD user accounts with accounts in older "downstream systems" that can't talk with Active Directory.  I do this to make it possible to create/disable accounts in these older systems...when a matching account in Active Directory is created/disabled.  It's been working great!

    I set this up using the guidance provided in these articles:

    The "SQL command text" that I've added to the ADO.NET Source Editor in SSIS is the following:

    <LDAP://YOURDOMAIN.NAME>; (&(objectClass=user)(objectCategory=person)(!(userAccountControl:1.2.840.113556.1.4.803:=2)) (!sn=Test)(|(sn=A*)(sn=B*))); employeeID, name, givenName, sn, displayName, sAMAccountName, Mail, Title, telephoneNumber, Manager, Department, physicalDeliveryOfficeName, adminCount, homeDirectory, userPrincipalName, initials,distinguishedName,objectGUID

    **As stated in the articles above, the code highlighted in the text above reduces the number of records returned, because Active Directory has a maximum number of records it will return per query, so if/when there are more than that limit returned by the query, the SSI ADO.NET Source just fails with no real indication as to why.

    Recently, a "downstream system" has been added to the mix which CAN talk to Active Directory, but it still needs an additional set of data to create/disable accounts more effectively than it does "out of the box".  To make enabling/disabling accounts in this new system really work, all I need is to get the ObjectGUID for each Active Directory account, OUT and into a simple text file.  I can then use that in the system to make matches and do the extra work it wasn't built to do on it's own.

    The issue is however, that I can't seem to get the objectGUID using the query above (this is the last element in the query above)...to come out as anything other than "System.Byte[]":

    objectquery

    That's not so worrying because the "preview" you see above is just what is displayed when you click the button from the ADO.Net Source Editor screen, and those familiar with SSIS know that I can downstream of this...use a Data Conversion Transformation --OR-- a Derived Column "widget" to convert this column to something useful.

    However, despite trying both data conversion approaches in SSIS and googling for what seems like days and weeks, I still can't find someone that knows how to successfully make use of the objectGUID element in Active Directory.  By "make use" I mean to convert the Active Directory objectGUID into a text format and then export it to a standard CSV file (e.g. convert it from a binary array (varbinary) as it's stored in Active Directory to a readable text format like (varchar) or even into the SQL unique identifier data type would be great).

    I've tried several times to modify the ADO.NET SQL query so that it actually converts the objectGUID before it even enters the data stream in SSIS but haven't yet been successful.  Below is an example of what does NOT work:

    <LDAP://YOURDOMAIN.NAME>; (&(objectClass=user)(objectCategory=person)(!(userAccountControl:1.2.840.113556.1.4.803:=2)) (!sn=Test)(|(sn=A*)(sn=B*))); employeeID, name, givenName, sn, displayName, sAMAccountName, Mail, Title, telephoneNumber, Manager, Department, physicalDeliveryOfficeName, adminCount, homeDirectory, userPrincipalName, initials,distinguishedName,CAST(uniqueIdentifier,objectGUID)

    You also can't just edit the ADO.NET source "widget" using the "Advanced Editor" and use the "Input and Output Properties" tab to change the "Output Column" data type from [DT_NTEXT] for the objectGUID to [DT_GUID] for example, as you'll get this error:

    Error at "data flow": The data type of output columns on the "ADO Net Source" cannot be changed, except for DT_WSTR and DT_NTEXT columns.

    Error at "data flow": System.Runtime.InteropServices.COMException (0xC020837D)

    at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper100 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

    So I thought I'd post here, because I'm sure someone out there must be versed at using the ADO.NET data source for Microsoft Directory Services, and would know what you can and cannot do in this case as it relates to the objectGUID data element in Active Directory.  Anyone?  Someone?  Bueller...bueller?

    netproviderscreen

  • August 18, 2020 at 5:10 pm

    #3781345

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • August 20, 2020 at 11:17 am

    #3781902

    I don't believe there's a straightforward way to do this in SSIS using only SQL. Here are a couple of posts discussing the conversion issue ,with tips on how to use VB or C# to make the conversion. You could pass the values to a script task that handles the .NET heavy lifting.

    https://stackoverflow.com/questions/18383843/how-do-i-convert-an-active-directory-objectguid-to-a-readable-string/31040455

    https://serverfault.com/questions/466594/script-to-resolve-guid-to-string-in-active-directory

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply