SQL 2005 to Active Directory and visa-versa

Question

SQL 2005 to Active Directory and visa-versa

omakler

Old Hand

Points: 308
More actions
January 3, 2008 at 2:58 pm

#103446

Can anyone help me with this I am pretty new to some of this (recent college grad in MIS). At my job we are trying to extract data from AD to a SQL 2005 DB and vise-versa. Basically a data dump back and forth to maintain consistent information across a few platforms.
I have read in here that I will need to make a linked server connection and we are working on that, what else will we need to do to accomplish that?
http://www.sqlservercentral.com/Forums/Topic153994-9-1.aspx
But it seems like we are missing something. Any help would be appreciated.
Mike
AD Noob

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 1

Is there a requirement that it has to be a linked server?

If not, a VB or C# application that uses the System.DirectoryServices namespace and ADO.NET will get you data transfer in both directions.

omakler Old Hand Points: 308 More actions · Answer 2

Well we are trying to see if there is a SQL way of acomplishing this. Generally what we would like to be able to do is have a web ap call a stored procedure that would grab data from Active Directory update an offsite database and then push some data back into Active Directory.

We currently have a perl script that can pull, but is there a way in SQL server 2005 to accomplish the same thing? Hope that makes sense.

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 3

Setting up a linked server using the OLE DB Provider for Microsoft Directory Services is straight forward but it has two limitations to consider. One, you can't update attributes with it. Two, you can't read multivalued attributes.

To read and write AD attributes, single and mutlivalued, you could make use of the CLR integration in SQL 2005; create a new CLR stored procedure that makes use of System.DirectoryServices. See BOL Getting started with CLR Integration, for a step-by-step.

For a project that used SDS this book helped me out quite a bit, The .NET Developer's Guide to Directory Services Programming.

omakler Old Hand Points: 308 More actions · Answer 4

I see your point. CLR would be a better way to go with the things we are trying to do. Never thought of that.

Thank you for the insight. I will take a look at those items and post back if I have an more questions.

omakler Old Hand Points: 308 More actions · Answer 5

Ok at the risk of looking completely stupid and lost in this area. What is the best way, if possible, to pull data from WinServer 2003 Active Directory to a SQL Server 2005 DB -- using a web link that will perform this through a stored procedure.

I have been looking around for days now, and have determined that it may be best to write a CLR to perform this as a linked server will not, as quoted below, allow a few things, such as updating, which we would be doing , as well as some multivalued attributes. However I am very new to CLR coding. What would is the best way to achieve this and any code examples would be very helpful. We do have a Perl script that does some of the functionality that we desire, so we are thinking that that might be something to consider.

Any help would be very helpful and if it is not possible that would be helpful too.

Todd Engen (1/4/2008)
Setting up a linked server using the OLE DB Provider for Microsoft Directory Services is straight forward but it has two limitations to consider. One, you can't update attributes with it. Two, you can't read multivalued attributes.
To read and write AD attributes, single and mutlivalued, you could make use of the CLR integration in SQL 2005; create a new CLR stored procedure that makes use of System.DirectoryServices.

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 6

[p]Read the following articles. They have good instructions and explanations on how to create your own stored procedures and functions using CLR Integration. [/p]CLR Integration

[/url]

Creating Dot Net Stored Procedures in SQL Server 2005[/url][p]Those articles have examples that will show you how to build, deploy and use an assembly from within SQL. Once you get those working, the next step would be to code an assembly to read and write AD objects. [/p][p]To read and write from AD in your assembly, SDS (System.DirectoryServices) will provide you with the classes and methods to do that. The book I mentioned earlier would be very helpful since it has code samples for C# and VB.NET.[/p][p]The best thing you could do is NOT do this on your production environment. If you don't already have a test environment setup, Microsoft Virtual Server or VMWare Virtual server are very useful for that purpose.[/p]