x

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

more ▼

asked May 21, 2010 at 10:55 AM in Default

Steve Wyvill gravatar image

Steve Wyvill
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered May 21, 2010 at 11:09 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840
x8

asked: May 21, 2010 at 10:55 AM

Seen: 2571 times

Last Updated: May 21, 2010 at 11:01 AM