Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inserting Active Directory Data into a SQL Server table Expand / Collapse
Author
Message
Posted Tuesday, May 14, 2013 10:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 9:28 AM
Points: 3, Visits: 66
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.
Post #1452724
Posted Tuesday, May 14, 2013 3:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1452866
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse