Extract users from Active Directory using SSIS

  • Hi,

    I need to implement a solution in my work company, they want to know the group members from diferent groups listed in active directory. So I presented a solution that was building a SSIS package that extract the info from active directory, list it to a txt file, and then put all the data in the SQL Server tables. My problem here is that i'm not a developer, and my problem here is to get a code that build me the txt file with the data I need. the fields are, "samaccountname", "memberof", "displayname", "departament" and "group".

    any one can help me with this?

    thks

  • You will need to write custom script to access the active directory, this site has something similar to what you are doing..

    http://www.mssqltips.com/tip.asp?tip=1657

  • yes, i already implemented that, but I having problems editing the code to add this costum fields, as i said I not a developer, and the code development is very hard to me.

  • Hi

    you can create a linked server to your domain controller.

    then

    declare @groups table ( grp sysname )

    declare @usrs table ( usr sysname, grp sysname )

    declare @actgrp sysname

    declare @actntgrp sysname

    declare @max-2 int

    declare @i int

    insert @groups SELECT *

    FROM OPENQUERY( [<linked server name>],

      'SELECT Name

      FROM ''LDAP://<linked server name>/ DC=<subdomain>,DC=<domain>,DC=<net>''

      WHERE

      objectClass = ''group''

      ORDER BY name

    ')

    select @max-2 = count(*) from @groups

    select @i = 0

    select @actgrp = Min(grp) from @groups

    while @i < @max-2 begin

      select @i = @i + 1

      select @actntgrp = '<domain>\' + @actgrp

      insert @usrs select name, @actntgrp from OpenRowset(NetGroupGetMembers, @actntgrp)

      select @actgrp = MIN(grp) from @groups where grp > @actgrp

    end

    will give you logins and groups.

    if exists (select * from tempdb.dbo.sysobjects where name = N'##ADS' )

    drop table [dbo].[##ADS]

    SELECT *

    INTO ##ADS

    FROM OPENQUERY( <linked server name>,

      'SELECT sAMAccountName, userPrincipalName, company, department, Name, givenName, SN, Mail, telephoneNumber, mobile,

      l, physicalDeliveryOfficeName, postalCode, streetAddress, facsimileTelephoneNumber, msExchHideFromAddressLists, distinguishedName, info, title, st, userAccountControl

      FROM ''<linked server name>/ DC=<subdomain>,DC=<domain>,DC=<net>''

      WHERE sAMAccountType=805306368 AND NOT ''userAccountControl:1.2.840.113556.1.4.803:''=2

      ORDER BY SN

    ')

    will give you a lot of information about the users.

    joining the results should give you everything you need

    SELECT *

    FROM ##ADS JOIN @usrs

    ON @usrs.usr = ##ADS.sAMAccountName

    regards

    karl

    Best regards
    karl

  • New code for SQL Server 2005 and above:

    declare @groups table ( id int identity, grp sysname, distinguishedName VARCHAR(MAX) )

    declare @usrs table ( id int identity, usr sysname, grp sysname )

    declare @actgrp sysname

    declare @actntgrp sysname

    declare @max-2 int

    declare @i int

    , @distinguishedName VARCHAR(MAX)

    , @sql VARCHAR(MAX)

    insert @groups (grp, distinguishedName)

    SELECT Name, distinguishedName

    FROM OPENROWSET('ADSDSOObject', 'adsdatasource';'<domain\user>';'<password>',

    'SELECT Name, distinguishedName

    FROM ''LDAP://<ads server name>/ DC=<subdomain>,DC=<domain>,DC=<net>''

    WHERE

    objectClass = ''group''

    ORDER BY name

    ')

    --SELECT * FROM @groups order by id

    select @max-2 = MAX(id) from @groups

    select @i = 0

    while @i < @max-2 begin

    select @i = @i + 1

    select @actgrp = '<domain>' + grp, @distinguishedName = distinguishedName from @groups

    WHERE id = @i

    SELECT @sql = 'select sAMAccountName, ''' + @actgrp + ''' from OPENROWSET(''ADSDSOObject'', ''adsdatasource'';''<domain\user>'';''<password>'',

    ''SELECT givenName, SN, Mail, info, sAMAccountName

    FROM ''''LDAP://<ads server name>/DC=<subdomain>,DC=<domain>,DC=<net>''''

    where objectCategory = ''''Person'''' and objectClass = ''''User'''' and SN = ''''*'''' and sAMAccountType=805306368 and not ''''userAccountControl:1.2.840.113556.1.4.803:''''=2 AND memberOf = ''''' + @distinguishedName + ''''' '')'

    insert @usrs (usr, grp) EXEC(@sql)

    end

    select * from @usrs

    Best regards
    karl

Viewing 5 posts - 1 through 4 (of 4 total)

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