question

Steve Wyvill avatar image
Steve Wyvill asked

Query Server 2008 R2 Active Directory from SQL Server 2008

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 .......

Steve

sql-server-2008active-directory
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Fatherjack avatar image
Fatherjack answered

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

DECLARE @cmd VARCHAR(200)
SET @cmd = 'dsquery user domainroot -o samid'
INSERT  INTO DomainAccounts
        EXEC [sys].[xp_cmdshell] @cmd

Other than that I would suggest you will be off to another tool -
PowerShell would create a text file of the data that you can import
or
LogParser would pump the data straight into SQL for you.

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 ...

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.