I have been able to pull Active Directory information (attribute and property) by using MS Access VBA. This has been achieved on a client side.
On the server side, I will need to create something similar so that I can hook it to a SSIS package or SQL Scheduled Job. Does SQL Server have the ability to tap into Active Directory information (attribute and property)?
yes. you can use ldap query as follows
Make sure you work with your sys admin to make sure whichever svc account you are using to create the linked server has the necessary permissions to query ldap
What are you going to do with the data you've tapped from AD and what data are you trying to actually pull over?
I ask because SQL Server actually does have a couple of extended stored procedures that hit AD and beats it against the AD logins (Windows authentication). For example, have a look-see at xp_LoginInfo and sp_validatelogins. That's usually all that people need from AD when it comes to SQL Server.
Hi Yosiasz,
Thanks for that link and the advice.
Hi JeffModen,
So we are using an Ultipro system to manage HR related stuff. Unfortunately, the User Login (SamAccount) and Email have been inconsistent so we are trying to get the AD info and to compare and later to update. Thank you for the SPs. I will look into them.
SELECT Name, mail, displayName,givenname,sn, cn,
distinguishedName, SAMAccountName, objectGUID, Department, adspath, manager
FROM OPENQUERY( ADSI,
'SELECT Name, mail, displayName,givenname,sn,cn,
distinguishedName, SAMAccountName, objectGUID, Department, adspath, manager
FROM ''LDAP://yourserver.lan/DC=your_company_dc,
DC=yourcompany_lan_com''
where objectClass = ''User''
AND objectCategory = ''Person''
AND ''userAccountControl:1.2.840.113556.1.4.803:'' <>2 ')
Very awesome Yosiasz. That is similar code that I passed from VBA using ADO.
Thank you again Sir!
BTW... What is this?
''userAccountControl:1.2.840.113556.1.4.803:'' <>2 '
I got this error but I will look into it later today.
Could not find server 'ADSI' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
oh sorry that is just the name of the linked server name to AD
you use that for userAccountControl to exclude, in this case, disabled users.
AND ''userAccountControl:1.2.840.113556.1.4.803:'' <>2
Yosiasz, I was able to setup a linkserver to the Active Directory server by the information on a page googled and called " Querying Active Directory Data from SQL Server".
I was able to run your above query. However, it returned an error:
Msg 7330, Level 16, State 2, Line 36
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".
If I change/add "TOP 901" to the select statement, then it works. It has to do with something about paging that I googled on. Any thoughts?
SELECT TOP 901 Name, mail, displayName,givenname,sn, cn, distinguishedName, SAMAccountName, objectGUID, Department, adspath, manager
FROM OPENQUERYI believe that is the limitation when using LDAP, 1000 rows. Do you need all of that data in one pull? can you bring a small subset of the data using filters?
interesting read if you might be able to use powershell
http://technet.microsoft.com/en-us/library/ff730967.aspx
read this for paged searches:
Yes, I guess, I can UNION 26 queries (with last name for each ABC as a filter). 
At least now I know how to connect to Active Directory from SSMS.
Ultimately, I think I will be using SSIS with ADO.Net because I read on one of the site that is the recommended approach since I need to run other ETL tasks in SSIS anyway.
Thanks
what is your use case. what are you attempting to do. the limit of 1000 can be overcome using the following method (we have only 150 employees so easy for me to say 
)
create a sql job that runs every 5 minutes
1st create your working table. Here is an actual table I use in a database called SharedResources so that other applications within our ecosystem can leverage.
--then every 5 minutes you dump data into it.
insert into entities
select *
from SharedResources.dbo.yourUsersView src
where not exists (select 1
from entities tgt where src.samaccountname = tgt.samaccountname )I tend to use Powershell to interogate AD (there are lots of nice applets in there) and then get SSIS to process the results into your database.
Actually, that sounds like a good design. Would you able to share a sample of the Powershell to interrogate the AD and how SSIS to process it, so that I can get a start? Thank You
Yosiasz, it is to compare between two systems.
The way how you have it setup is great but if information changes between the two systems, the changes will not be captured.