Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing Active Directory user accounts in SSIS?


Importing Active Directory user accounts in SSIS?

Author
Message
emiranda 59653
emiranda 59653
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 800
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14973 Visits: 38989
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
emiranda 59653
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 800
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
emiranda 59653
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 800
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search