How to query active directory from Board ?

Hi everyone,

I am currently looking for ways to import in a Board database a few key information about our users, coming from Active Directory.

Basically, our user names are email addresses, and I would like to use AD information to associate the location, company, manager name, etc. to our users. This would be very useful to better analyze the logs for example.

Is there any best practice about that ? Currently my best option is to build a query in Excel, export to a CSV and import into Board, but as you can imagine this is not very satisfying.

Thanks in advance for all your ideas

Etienne

Find more posts tagged with

Usage

Accepted answers

All comments

Robert Barkaway

Hi, this article would appear to suggest that it's possible to query Active Directory information into a SQL database using linked servers and OPENQUERY, I can't say that I've tried it myself but that might be an option if you have a SQL instance available to use?

Etienne CAUSSE

Hi Robert,

I saw this article as well, but I'm not sure what would be the right steps to implement this:

- should I setup a SQL server alongside the board engine on the server ?

- if yes, how do I update the local sql server before querying it from Board ? Are you thinking about a scheduled process (i.e. schedule the execution of a refresh query on the SQL server, then schedule a procedure on the Board server) ?

It seems a bit heavy and not very dynamic, but it could be a solution I guess. Any other ideas ?

Robert Barkaway

Hi Etienne,

I wouldn't suggest setting it up on the Board server, no, to be honest it was more a suggestion for if you already have a SQL server set up somewhere in your company which you could use, if not then it probably isn't the most convenient solution If you did go down that route then you could schedule it all through SQL Agent, the same job could extract the data to either a SQL table or a flat file, and then use the command line call to Board to trigger a procedure for importing the data.

I've just had a quick Google (as I'm sure you've already done yourself), and to be honest it looks a lot like your only other option might be a third party tool for command-line interrogation of AD unfortunately.

If you do get a solution working be sure to share it on here, I'm sure it's something which would be of considerable interest to other community members

Kind Regards,

Rob

Etienne CAUSSE

I will of course share if I find anything more dynamic

I was wondering since the authentication through Windows is possible, if anybody in the lab would have an idea to use that... ?

unknown

I'd recommend setting up a linked server inside SQL to query Active Directory as a ADSI datasource. Below are links to articles with all the details. At a high level, we can setup a linked server to an AD domain controller to query it like a schema. You may want to setup a view on top of this linked server connection, which you can use in a datareader to get the data into BOARD. As a warning, linked servers can be challenging. In some configurations, the link drops occasionally.

Microsoft OLE DB Provider for Microsoft Active Directory Service | Microsoft Docs

https://blog.sqlauthority.com/2016/03/30/sql-server-query-active-directory-data-using-adsi-ldap-linked-server/

Etienne CAUSSE

Hi Bob Gill and thanks for this suggestion.

I was wondering, since Board is natively capable of using Windows & AD , or even another LDAP source to authenticate itsusers, why is it not possible to query a few additional AD fields at the same time ? Would that be very complicated to implement ?

unknown

Hi Etienne CAUSSE,

You raise a very good point. BOARD does have native support for AD and other LDAP tools. I think you have a very good idea here. Since BOARD is already querying AD for group membership, it just needs to be extended to query for other user and group attributes. I've included some links below on all the attributes available for user, group and computer object attributes.

AD User Attributes - User Attributes - Inside Active Directory
AD Group Attributes - https://social.technet.microsoft.com/wiki/contents/articles/12079.active-directory-get-adgroup-default-and-extended-prop…
AD Computer Attributes - https://social.technet.microsoft.com/wiki/contents/articles/12056.active-directory-get-adcomputer-default-and-extended-p…

In the meantime, you could also use powershell to query AD, but I don't think we can link that to a datareader. If you want to run a powershell script to generate a CSV file each night, then you could use a datareader to import into BOARD.

Use Powershell to Generate a CSV file of AD Users - https://gallery.technet.microsoft.com/scriptcenter/Powershell-script-to-5edcdaea

You might want to write this idea out in the ideas area to see if we can get it into the product.

Good luck,

Pietro Ferrari

Bob, all,

all Windows systems (from Win2000) come with a Microsoft native OLE DB provider for Active directory. Therefore you can read data in AD simply creating a SQL Datareader in Board, create a new connection using the OLE DB wizard, you will find the data provider for connecting to AD.

So it's already possible, no need to go through MSSQL use Linked servers or any other layer.

The only limitation you'll find if that the provider will not list "tables" but after you have created the connection you can type manual SQL queries and load the data as if you were reading a standard relational source.

Fabio Donatellis

Hi Etienne CAUSSE

The solution is very simple and can be integrated directly into Board Server.

Microsoft Provides a Ole-DB provider to connect to Active Directory Service. (here more details).

The oledb driver should be included in the operating system and you know that it is possible to query Active Directory with a sql command.

So, from Board side, implementation is very easy using with a datareader:

From the datareader tab, create a new connection
In the connection string field, enter your oledb connection string as this sample:

Provider=ADSDSOObject; Integrated Security = SSPI

You can get the connection string from a udl file and set a property to define the type of authentication to AD: Username and password or integrated (Windows authentication)

click on "Save & Connect".
You'll get a warning message, "Database empty" . This is corrrect because AD is not a database and there is no a db schema or list of tables.
Now that the connection has been created, you can set up a manual query to return information from your AD.

Below an example to extract all users and some attributes:

Select userAccountControl, employeetype, physicalDeliveryOfficeName, department, company, title,
from 'LDAP://myADServer.com/OU=OrganizationalUnit,DC=DOMAIN,DC=NAME'
WHERE objectClass='user' and objectClass = 'person'

This solution is very used to implement the "Enterprice Security".

In this way it is possible to import the list of users, in order to manage security profile.

Regards

Fabio

Etienne CAUSSE

Exactly what I was looking for... I'll test that ASAP

Etienne CAUSSE

Hi Fabio Donatellis,

In the RDBMS field, what am I supposed to setup ? "OLEDB for OLAP" ? Or "General" ? It seems I cannot reach the server when I query it...

Fabio Donatellis

Hi Etienne CAUSSE

In the RDBM, you must set the field to "General".

"OledDB for OLAP" is to connect to multidimensional databases like SSAS.

If you use integrated authentication, the system will log in to the domain controller with the user that running the service "Board Engine". This user must be authorized to access and query the AD.

Regards

Fabio

Etienne CAUSSE

Hi Fabio Donatellis,

I'm stuck with this message when querying my AD :

My query is as follows:

Select displayName, employeeNumber, physicalDeliveryOfficeName, department, company, title, manager,
from 'LDAP://DC=eu,DC=seb,DC=com'
WHERE objectClass='user' and objectClass = 'organizationalPerson'

Any obvious mistake ? I'm not very familiar with AD & LDAP.

Fabio Donatellis

No, no obvious error.

Remember to run the query browser from the tab "Manual".

On the net there are many AD Query tools that might be useful to write the query.

If you can not configure it in any way, open a ticket and send us all the information and screenshots.

We will try to help you.

Fabio

Etienne CAUSSE

I'll do that, thanks

unknown

...linking to a related question

How to query the User List from MS Active Directory