Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extract users from Active Directory using SSIS Expand / Collapse
Author
Message
Posted Wednesday, March 18, 2009 4:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 22, 2009 9:12 AM
Points: 11, Visits: 19
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

Post #678243
Posted Wednesday, March 18, 2009 4:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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
Post #678248
Posted Wednesday, March 18, 2009 5:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 22, 2009 9:12 AM
Points: 11, Visits: 19
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.
Post #678274
Posted Friday, March 20, 2009 9:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 477, Visits: 1,904
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 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 = count(*) from @groups
select @i = 0
select @actgrp = Min(grp) from @groups
while @i < @max 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
Post #680482
Posted Tuesday, June 25, 2013 8:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 477, Visits: 1,904
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 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 = MAX(id) from @groups
select @i = 0
while @i < @max 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
Post #1467199
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse