Importing Active Directory user accounts in SSIS?

Question

Post reply

Importing Active Directory user accounts in SSIS?

emiranda 59653

Ten Centuries

Points: 1096
More actions
April 10, 2013 at 9:24 am

#276262

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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

emiranda 59653 Ten Centuries Points: 1096 More actions · Answer 2

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.

emiranda 59653 Ten Centuries Points: 1096 More actions · Answer 3

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.