I'm relatively new to SQL Server only using the Express edition up until now for MS MDT for desktop deployments. I now need to start utilising SQL Server 2008 to query our Windows Server 2008 R2 Active Directory in native mode (forest / domain functional level is 2008 R2). The aim here is to query AD from SQL to allow reporting on AD objects and then publish the reports within our intranet. I guess the first thing I'll need to achieve is a link between the SQL server and AD. It seems I need to create a linked server but all my attempts through SQL Server Management Studio and SQL Server Business Intelligence Development Studio have proved unsuccessfull thus far ! All information in the forums I've researched is related to previous versions of SQL and none refer to 2008 R2 AD. My first question is ..... is it possible to query 2008 R2 AD from SQL 2008 or is there some compatibility issues with the AD schema that I'm not aware of ? Second question ..... If answer to question one is YES, it is possible, then could someone provide me with guidance on how to achieve this please ?
I want to start examining AD groups etc and reporting on the objects within them. Guess this is relatively staright forward when you know how !
In anticipation .......
the only way I know of to query AD from SQL is to use the execute SQL stored procedure xp_cmdshell to run a dsquery via cmd shell - this would query AD and put the returned data into a SQL table called DomainAccounts
Other than that I would suggest you will be off to another tool -
How you would run those from SSIS is another issue - PS I think can be run straight from SQL Agent but LogParser is more complicated but SQL Express wont have SQL Agent ...
answered May 21, 2010 at 11:09 AM