LDAP Login to Retrieve Users and Groups

[#1]

Hi Everyone,

Could someone provide an LDAP connection string, with (mock) login credentials, that you've successfully used?

I'm currently using the following format, but it appears to keep defaulting back to the anonymous login, which then does not return the populated "memberof" and "group*" fields I need:

CONNECT TO [Provider=ADsDSOObject;User Id=fakeuser;Password=xxxxxxxxxxx;Encrypt Password=True;Integrated Security=SSPI;Data Source=LDAP;Location=DC=www,DC=acme,DC=com;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

Thanks!

-DJ

[#2]

See here

You coudl install the SQL provider for Active Directory and try from QlikView.

Or, you could run comand line tools, and write output to a text file. Load that text file into QlikView . It might be simpler. In Windows server versions those command line tools are available.

http://technet.microsoft.com/en-us/library/cc755655%28WS.10%29.aspx

-Alex

[rwunderlich]

Are you sure your problem is with the login? "memberof" is a multi-valued field and ADsDSOObject can't return multi-valued fields. Attached is a script with a workaround.

This blog post and the referenced QV Cookbook example show another approach using MS Logparser.
http://qlikviewnotes.blogspot.com/2008/12/extracting-data-using-microsoft.html

[#4]

Hi Alexandru and Rob,

Thanks for your help with this.

Alexandru:

Is there a download-able AD-SQL Server connector? All I see are the OLEDB-style connection strings, like I have above. The command-line tools are a solid backup strategy (thanks for the link), but I'd prefer to get a direct connection working.

Rob:

Thanks for putting that QVW together. I've actually been trying your code and macro, but the "member" field in the macro isn't returning anything, so the GroupDN and UserDN fields have no overlap. The AD admin here says "member" is populated, but requires the login, which made me think my connection string was wrong.

Have either of you gotten a login to work in the AD connection? I've got Log Parser and Task Scheduler standing by, but feel like I'm so close to cracking this.

Thanks again for your help!

-DJ

[rwunderlich]

djnorton wrote: but the "member" field in the macro isn't returning anything

Are you by chance using SR7? This (subfield) is broken in SR7.

djnorton wrote:Have either of you gotten a login to work in the AD connection

I've never tried an explicit login. I had assummed, possibly incorrectly, that integrated security was the default. Your connection string has

Integrated Security=SSPI

You might try taking that off.

[#6]

Hi there, try the following syntax for the login:

CONNECT TO [Provider=ADsDSOObject;User ID="uid=admin,ou=system";Encrypt Password=False;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648] (XPassword is secret);

In In my case, I'm using the user "admin", which is under the organizational unit of "system", change these attributes based on your actual active directory. This configuration works with Apache Directory Service.

Regards.

[#7 · Accepted solution]

Hi Everyone,

Sorry for the delay. I was able to get authenticated LDAP queries to work(!), using Rob W's code. Thanks, Rob!!

The key pieces were:

1. Changing the macro safety settings to "Allow System Access", and
2. Use of the following connection string format for an authenticated LDAP login:

CONNECT TO [Provider=ADsDSOObject;User ID=Domain\user;Encrypt Password=False;Data Source=LDAP://ldap.acme.com;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648] (XPassword is ZXXXXXXXXXXXXXXXXXB);

Note that there are many small syntax changes in this connection from the other examples I've encountered, including specifying the user's domain, referencing the "Data Source", and not "Location", and explicitly noting "LDAP://" for the LDAP server data source. In fact, I created this connection using the 32-bit "OLE DB Provider for Microsoft Directory Services".

Also, even though I'm using the DNS-style server address for the Data Source in the connection string, I'm using the Active Directory naming format in the FROM clause of my query: DC=ldap, DC=acme, DC=com

The only problem I'm having now, is that the client is less enthused about needing to run a system-access macro, because QlikView Server's macro settings are server-wide, not app-specific, which means they'd need to open up their Production server to running any macros the developers create -- not an acceptable option. I can start a new thread for this topic shift, but please let me know if anyone has any suggestions or workarounds.

In the meantime, we'll probably run this query in a single-purpose app, from either the Dev Server or a hidden box, and generate/update a QVD file that developers can access on the network shares.

In the meantime, thanks again for everyone's help!! This was a huge win.

-DJ

[rwunderlich]

djnorton wrote:The only problem I'm having now, is that the client is less enthused about needing to run a system-access macro,

Use something like MsLogparser to have a pre job extract the data to csv files and load from there. See the previously referenrced blog post.

[#9]

I found the solution to my macro issue, and I'll admit that I should have seen it before. But, first:

I was running into a Group limit of 1500 users (Ex. All-Hands group), so I turned Rob's macro around to start from the User table and chase MemberOf. This does assume that no user will be in >1500 groups, but hopefully that's a safe assumption:

Function UserMemberOf(DN)
Set objItem = GetObject("LDAP://" & DN)

strmemberof = objItem.GetEx("MemberOf")
ret = ""
For Each Item in strmemberof
 ret = ret & Item & ";"
Next
UserMemberOf = Left(ret, Len(ret)-1) ' Drop trailing semicolon
End Function

So, getting the macro to work then on the Prod server, without needing to allow System Access macros, requires the following:

In Tools > Edit Module, be sure the Current Local Security setting in is set to "Allow System Access", or Rob's lookup script will be unable to retrieve the MemberOf field. But, setting "Requested Module Security" to Safe Mode vouches for the macro as being safe. QVS reserves the right to error out on a macro that was marked as safe and/but ends up performing a system or external app call.

From the Help file:

"By selecting Safe Mode the document designer indicates that the macros in this QlikView document contain no code that can access either the system or applications outside QlikView. If such code is encountered during macro execution in a document declared to be in Safe Mode, the execution will fail."

Also, note that everything I've listed in this thread works well for Active Directory, but appears to not apply directly to my client's non-AD LDAP servers (they've got both AD and non-AD LDAP servers). So, now I'm running through the potential permutations to get my query tweaked to run from this "generic" LDAP server. (Too bad I can't edit the thread title...)

Rob, does MS LogParser work with non-AD LDAP? Any suggestions?

[jkangcswg]

Thank you for the excellent code! The only thing I had to tweak was to change the workaround for undefined SQL error from "FIRST 1000"  to "FIRST 999" to make it work.

Is there any way to retrieve User ID as well?