October 30, 2009 at 9:34 am
We have found lots of documentation and have successfully queried our active directory from SQL Server. Problem is our users exist in Novell's eDirectory. We do have a Novell LDAP server that Softerra is able to query but every attempt to query from SQL Server fails. We need to get the users and associated directory information into a SQL table for several different purposes and we can't find anything to point us in the right direction.
Does anyone have any ideas how we can make this happen? Or, are there any tools that could get the contents of eDirectory and put it in AD so we could query it?
We are running 2008 Enterprise, if that makes a difference.
Additional info: we have tried an odbc driver from Novell to find out that it requires the Novell Client be installed on the machine. We don't want the Novell client installed on our SQL Server.
Thanks!
October 30, 2009 at 3:56 pm
We are part way there. We have a C# script that will output the data to a web page using a response.write inside of the foreach(DirectoryAttributeCollection attribute in attributes.Values). Our thought then was to write the results to a table in SQL Server so we modified the script as follows and put it into a script task in SSIS. It "finishes" green but we don't get any data in the table. We ran a trace to see if anything was happening on the SQL Server and didn't even get a connection.
Any ideas what we are doing wrong?
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.DirectoryServices.Protocols;
namespace IS_Script_Task_Project1
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// TODO: Add your code here
//Create connection to eDirectory.
LdapConnection con = new LdapConnection(new LdapDirectoryIdentifier("[ldap server]:389"));
con.AuthType = AuthType.Basic;
using (con)
{
//Pull the distinguished name from eDirectory.
string[] attributesToReturn = new string[] { "workforceID", "fullName", "mail" };
SearchRequest request = new SearchRequest("o=PISD", "(&(objectClass=Person))", SearchScope.Subtree, attributesToReturn);
SearchResponse response = (SearchResponse)con.SendRequest(request, new TimeSpan(1, 0, 0, 0, 0));
con.Bind();
//Response.Write("Name search successful.");
if (response.Entries.Count > 0)
{
try
{
SqlConnection conn1 = new SqlConnection();
conn1.ConnectionString = "Data Source=SQL\\TEST;Initial Catalog=DataExchange;Persist Security Info=True;User ID=***;Password=***";
conn1.Open();
SqlCommand insert = new SqlCommand();
insert.Connection = conn1;
insert.CommandType = CommandType.Text;
foreach (SearchResultEntry entry in response.Entries)
{
string var0;
string var1;
string var2;
SearchResultAttributeCollection attributes = entry.Attributes;
foreach(DirectoryAttributeCollection attribute in attributes.Values)
{
var0 = attribute[0].ToString();
var1 = attribute[1].ToString();
var2 = attribute[2].ToString();
insert.CommandText = "INSERT INTO [LDAP TEST] VALUES ('" +
var0.ToString() + "','" +
var1.ToString() + "','" +
var2.ToString() + "')";
}
insert.ExecuteNonQuery();
}
conn1.Close();
}
catch (Exception)
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
January 20, 2011 at 1:15 pm
Hi All,
I finally found a solution! I created an SSIS package and used the Script Component as the source with the destination being my SQL Table. The script I used is here:
Imports System
Imports System.Data
Imports System.Math
Imports System.DirectoryServices.Protocols
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub CreateNewOutputRows()
Dim con As New LdapConnection(New LdapDirectoryIdentifier("ldap1.[domain]:389"))
Dim credential As New System.Net.NetworkCredential("cn=,ou=aux,ou=support,o=pisd", "[password here]")
con.AuthType = AuthType.Basic
con.Credential = credential
Using con
Dim attributesToReturn As String() = New String() {"workforceID", "cn", "uniqueID", "givenName", "sn", "fullName", "mail", "loginDisabled"}
Dim request As New SearchRequest("o=PISD", "(&(objectClass=user))", SearchScope.Subtree, attributesToReturn)
Dim response As SearchResponse = DirectCast(con.SendRequest(request, New TimeSpan(1, 0, 0, 0, 0)), SearchResponse)
con.Bind()
If response.Entries.Count > 0 Then
Dim counter As Integer = 0
For Each entry As SearchResultEntry In response.Entries
Output0Buffer.AddRow()
Dim CN(2) As String
Output0Buffer.POLICY = entry.DistinguishedName.ToString()
Dim attributes As SearchResultAttributeCollection = entry.Attributes
For Each attribute As DirectoryAttribute In attributes.Values
For i As Integer = 0 To attribute.Count - 1
If TypeOf attribute(i) Is String Then
If attribute.Name = "workforceID" Then
Output0Buffer.EMPID = attribute(i).ToString()
ElseIf attribute.Name = "givenName" Then
Output0Buffer.FNAME = attribute(i).ToString()
ElseIf attribute.Name = "sn" Then
Output0Buffer.LNAME = attribute(i).ToString()
ElseIf attribute.Name = "cn" Then
If String.IsNullOrEmpty(CN(0)) Then
CN(0) = attribute(i).ToString()
Else
CN(1) = attribute(i).ToString()
End If
ElseIf attribute.Name = "fullName" Then
Output0Buffer.FULLNAME = attribute(i).ToString()
ElseIf attribute.Name = "mail" Then
Output0Buffer.EMAIL = attribute(i).ToString()
ElseIf attribute.Name = "loginDisabled" Then
Output0Buffer.DISABLED = attribute(i).ToString()
End If
End If
Next
Output0Buffer.CN = CN(0) + "~" + CN(1)
Next
counter = counter + 1
Next
End If
End Using
End Sub
End Class
April 5, 2012 at 4:44 am
Hi!
I have installed as described above, but the line
Imports System.DirectoryServices.Protocols
in my script is indicating that DirectoryServices can not be found.
What do I need to do to make DirectoryServices available in my Script Task in SSIS?
//Viktor
April 5, 2012 at 7:47 am
I'm not sure what you may be missing. Sounds to me like something is not properly loaded on your computer, whether that be in the OS itself or in BIDS.
Just to be sure, this is for Novell eDirectory. If you are looking to download LDAP from Microsoft Active Directory, it is much simpler.
April 5, 2012 at 12:34 pm
Thank you for your answer!
It is Novel eDirectory I want to access, not AD.
My problem is that the script editor does not recognize Imports System.DirectoryServices as a valid reference so I guess that you are right asuming that something is not properly installed.
I am running BIDS on a Windows 2008 64-bit server, is this supported by the Novel Client? Exactly what type of Novel drivers do I need to install?
Very grateful for your time and help!
//Viktor
April 5, 2012 at 2:52 pm
I never installed anything from Novell on my server.
I had the full version of Visual Studio 2008 so I'm guessing that had installed those libraries.
I would NOT install any Novell stuff or Visual Studio to your server...
April 6, 2012 at 8:32 am
Jim, thanks very much. I also have faced this problem. I have a nightly agent job that runs a vbs script (yeah, I know, very old school) to query our EDir LDAP server and write out the results to a text file, which is consumed by SSIS and imported into SQL table. Klunky.
Have you figured out if you can do this in real-time, as a TVF say? Does your script execute quickly enough that it could be adapted to a CLR-based TVF, for example?
For a .NET newb like myself, how would I go about testing just the script part? I started VS, created an empty C# project and CodeFile, and pasted your code in. But there are syntax errors like "A namespace cannot directly contain members such as fields or methods"
Thanks very much,
Rich
April 10, 2012 at 8:33 am
Hi Rich, Don't knock your VBS solution! It is a good solution. The solution we were running with C# was started by another co-worker, I just took and finished it up and made it work. I would have started with a VBS solution had she not gotten as far as she was with the C#. That said...
We were never able to get it to run in real-time and the script took about 15 minutes to load all 60,000 + users on our network. Since it isn't searchable (that we found), our best solution was to do it in the SSIS package as described above. Once loaded in a table, then we could do with it as we pleased. We just setup an automated job to run nightly and there was an understanding that new users that day would not be loaded until the following morning.
As far as re-creating the project, this was done in a script component in the SSIS package which handles the objects differently than in a standard C# project.
I would like to help further with this but we ditched our Novell network last summer and moved to pure AD so I don't even have anything to test against anymore to refresh my memory. AD queries very nicely directly from SQL Server without having to do this SSIS script in the middle. There are still limitations to querying AD, which is setup on the AD side, that prevent querying real-time so we still download every night. Different now is that it all runs in about two minutes instead of 15.
April 10, 2012 at 9:28 am
Jim I appreciate the reply, thanks.
My solution works OK, and it runs pretty fast. The slow part is the VB script to query LDAP and dump the results to a text file.
I was able to schedule the agent job to run frequently but still keep up-time availability high by first loading the LDAP text data to a staging table in SQL w/ SSIS; then, I use a transaction to truncate the "real" table and populate the real table from the staging table. That second part runs nearly instantaneously b/c it's all happening within SQL Server. So, no matter how long it takes to query LDAP/AD, users always have access to LDAP data.
You could implement something like this so you could run your job more often, even if it only takes 2 minutes to run now w/ AD. The only "downtime" when users couldn't read LDAP would be the time it took to truncate the table and copy the 60,000 rows from one SQL table to another, which would be trivial. (And READ UNCOMMITTED would allow even then).
I don't know what kind of load the AD query places on your domain controller though, whether that would slow down authentication requests and other domain management.
Just curious: what AD limitations preclude real-time queries? I've been thinking that if we abandoned EDir and used AD we could do real-time queries with CLR, but I haven't looked into it. (We have an AD domain here, but not for everyone, just for 2 of our ou's.) But it sounds like you aren't able to do that?
Thanks again,
Rich
April 10, 2012 at 10:58 am
I actually do load it into a staging table first since I have to separate staff and students into different tables and create a column in the destination table from the source data. I could probably run it more frequently than once per day but since our AD load is automated as well (for 99.9% of our users), it wouldn't make any sense. When we were still eDir and accounts were maintained manually, our network engineers asked that we only pull once per day because of the load it put on eDir. I'm guessing AD just handles it better.
The limit in AD is the number of records returned by a query. As I recall, the limit, by default, is 2,000 records. Our engineer raised that limit to the max which is somewhere in the neighborhood of 20,000. This is really only an issue if you are trying to get all users at the same time. Since 20,000 is still not enough for our entire directory, I have a cursor (ugh!) that runs through each school to get all of the users into the staging table. Then, I can do whatever I need since it is all on the SQL side.
If you are looking up individual users, I would think that it would run relatively quickly. Since our original solution was built to maintain local copies, and therefore all of our applications designed with that in mind, I kept the same methodology in place. Also, since AD is only modified once per day, this solution works well in our environment.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply