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

Importing Active Directory user accounts in SSIS? Expand / Collapse
Author
Message
Posted Wednesday, April 10, 2013 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:24 AM
Points: 48, Visits: 528
I am developing an SSIS package that will create user accounts with random passwords for different groups and sending a custom email to the different users after their account is created. Unfortunately, our network admin don't want to script the account creation part and wants to do it manually after I create data files needed for account creation. Since I cannot predict when he will create the accounts, I am thinking of querying Active Directory to find if a user account exists and updating a flag that will allow me to send the appropriate email.

Using a quick search on the internet, I found this old article on how to do it in SSIS:

http://www.mssqltips.com/sqlservertip/1657/get-active-directory-users-and-groups-with-sql-server-integration-services/

Seeing as this article is more than 3 years old, is there a better solution for this now or does this solution still work? Any ideas on using an alternative to query Active Directory will also be appreciated.

Erick
Post #1440881
Posted Wednesday, April 10, 2013 9:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:47 PM
Points: 12,910, Visits: 32,020
do you really need #1 all active directory uses, or #2 only the ones in a certain group?

there's an extended stored proc that makes #2 easy:
--you need to KNOW the name of the AD group
EXEC master..xp_logininfo @acctname = 'disney\authenticatedusers',@option = 'members' -- show group members

for all users, you'll run into problems using an LDAP linked server, as it will only return the first 1000 rows/records; i've done it in a programming language to get all of them, and i think there's a work around, but it's been a while since i had to research this.
--doesn't quite work...permissions
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM ''LDAP://mydomain'' '



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1440896
Posted Wednesday, April 10, 2013 9:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:24 AM
Points: 48, Visits: 528
Thanks for the quick reply Lowell. I don't think xp_logininfo works as the accounts I'm creating don't have access to the SQL server. For AD purposes, the accounts are just coming from two AD groups: Parents and Students. I'm looking right now on maybe going thru a powershell script to check if a user account exists as that might be a simpler solution.
Post #1440905
Posted Wednesday, April 10, 2013 10:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:24 AM
Points: 48, Visits: 528
Thanks, Lowell, I was able to make it work using your linked server solution. I dug around and found a thread in the SQL 2005 forum that discussed how to fix the permissions issue and was able to make it work on my environment.
Post #1440942
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse