SO reality hit yesterday, we needed to import the data from all a clients Active Directories – not many by any standards and all 2008 I still have nightmares about uncovering forgotten NT4 domains that are “business critical” – anyway you can use Power BI Desktop to easily import multiple Active Directories and then merge them into a table for reporting, that all works really well and is frankly a breeze. You say you want to use an Active Directory, give it some credentials and then select where and what you want to import. If you need to combine data then you just merge your queries.
So why are you wasting our time by making us read this?
It all falls apart when you start to set it up into an App ready to publish it. The Enterprise Gateway (On-Premise Gateway) doesn’t support active directory connections, so you have two options
- Hire someone to manually refresh reports all day – This is a great fun job and I’m sure your staff retention will be sky high
- Use a single Windows account for everything (that needs Windows accounts) including logging into Power BI and all your data sources – I can hear the panic in anyone with a security background reading this
Well neither of those options work for my company! So we chose not to choose naff, we chose life instead
So the 3rd option… we ended up scratching our heads and getting annoyed for a long, long time for me (about 5 minutes), then we broke down the problem and found the solution or rather knowing that there’s nothing new under the sun found a solution we could leverage on a blog post really it looked too simple, we couldn’t believe more people didn’t do it. A single step is all that is needed to get you started.
You start by setting up a linked server connection on your SQL server, it took our SQL admin 10 minutes to do that 7 minutes was spent by us reading and re-reading the instructions in case we has missed something… we hadn’t.
What you need…
- Active Directory Credentials (preferably a service account of some description in multiple AD environments trusts are your friend otherwise you’ll need multiple connections in theory just repeat but change linked server names and adjust scripts accordingly)
A solution that needs a single thing is amazing!
So as soon as you have a linked server set up what use is that?
Ok moment of honesty, our first step is for me to admit that there is a limit limitation in a standard Active Directory, the limit is in terms of return size. This was the most complex part of the task we had to deal with, but know that you can update Active Directory to get around this. To date we have not done that so know that is is possible to get clever and work around it. I’ll share a blog on that at the bottom.
So in practical terms with an unaltered Active Directory you can return 901 Row – yup that threw me this morning – I would have expected a round number.
Select * from OPENQUERY([LINKED SERVER], 'SELECT givenname, sn, samaccountname, displayname, mail, l, physicalDeliveryOfficeNamFROM ''LDAP://[DOMAIN CONTROLLER]/DC=[DOMAIN]'' WHERE objectClass = ''User'' ')
The key to the whole thing is OPENQUERY() and of course understanding which Objectclass you want to query, we tested it with computer and user, although it should work with all the others, group included. Note that inside the OPENQUERY statement you they’re apostrophes not speech marks and you need multiples of them as the internal variables need to be contained as well.
Once you have built your query simply either import it directly or create a table – you’ll know what is right for you – and you’re good to go. As it’s a SQL server your On-Premise Gateway will be able to cope with it no problem and you can schedule your report builds as you require.
More than 901 rows
In reality for my client this alone didn’t work for either Computers or users, so we got creative and built a range of queries using Excel and some variable to build the Union query, once the first is done the rest is quick, as with all SQL it’s just about getting the syntax correct and in this case it is in being able to combine your AND statements.