SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inserting Active Directory Data into a SQL Server table


Inserting Active Directory Data into a SQL Server table

Author
Message
mlemay-957463
mlemay-957463
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 76
I wanted to gather Active Directory data into SQL Server table to analyze permissions on critical data. I wanted to use a SQL Server Agent Job that would refresh a couple of time per day. I also wanted it to be as clean and portable as possible, so I tried to run everything within the server itself and not rely on external resources which tend to complicate matters. This task seems pretty straight forward, however due to limitations in the OLE DB driver for Microsoft Directory Services and limitations in Powershell it has become quite a chore. The first problem I ran into was trying to bring in "description" from AD using a linked server. The query returned the error: Cannot get the data of the row from the OLE DB provider "ADSDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow.
Upon further examination I found that the "description" field in AD is a "multi value" field and the OLE DB provider doesn't know what to do with it so it errors. I then turned to Powershell which has been held up as the future of Microsoft product integration. Though it's a powerful tool, and did exactly what it was supposed to while running in a Command Prompt, once I put it into a SQL Job it threw a syntax error because the job engine could not parse the following line of code: $Command.CommandText = "INSERT INTO Test(Name, Title, Phone, description) Values('$($Name)', '$($Title)', '$($Phone)', '$($description)')"
I guess I will have to create an external .PS1 file and run it as an Operating System (CMDExec) file unless someone has a better suggestion.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40430 Visits: 14413
mlemay-957463 (5/14/2013)
I wanted to gather Active Directory data into SQL Server table to analyze permissions on critical data. I wanted to use a SQL Server Agent Job that would refresh a couple of time per day. I also wanted it to be as clean and portable as possible, so I tried to run everything within the server itself and not rely on external resources which tend to complicate matters. This task seems pretty straight forward, however due to limitations in the OLE DB driver for Microsoft Directory Services and limitations in Powershell it has become quite a chore. The first problem I ran into was trying to bring in "description" from AD using a linked server. The query returned the error: Cannot get the data of the row from the OLE DB provider "ADSDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow.
Upon further examination I found that the "description" field in AD is a "multi value" field and the OLE DB provider doesn't know what to do with it so it errors. I then turned to Powershell which has been held up as the future of Microsoft product integration. Though it's a powerful tool, and did exactly what it was supposed to while running in a Command Prompt, once I put it into a SQL Job it threw a syntax error because the job engine could not parse the following line of code: $Command.CommandText = "INSERT INTO Test(Name, Title, Phone, description) Values('$($Name)', '$($Title)', '$($Phone)', '$($description)')"
I guess I will have to create an external .PS1 file and run it as an Operating System (CMDExec) file unless someone has a better suggestion.

If you're on SQL 2008 or 2008 R2, that is my recommendation. Some additional reading if you're interested as to why:

The Truth about SQLPS and PowerShell V2 by CHAD MILLER on MAY 27, 2010

In SQL 2012 PowerShell Job Steps are supposedly full blown PowerShell shells so we can expect the same user experience there are we do at our PowerShell prompts. What a concept, I say.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search