May 14, 2013 at 10:52 am
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.
May 14, 2013 at 3:43 pm
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[/url]
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply