Find sysadmins Hiding in Active Directory with PowerQuery

Chances are if you are using SQL Server in an enterprise environment you are probably using Active Directory groups to setup and maintain permissions, but how as a DBA do you know who the users are behind the groups and many there actually are? This is especially important for the sysadmin permission.

In this article I’m going to show how to use PowerQuery to find all the users in Active Directory that have the sysadmin permission. As Brent Ozar puts it “these are the people that can get you fired”.

NB: I will be using the November update for PowerQuery.

Get a list of sysadmins

Open Excel 2013 and on the PowerQuery tab select “From Data” and then “From SQL Server Database”. In the window that is then displayed, enter your server name, master for the database then the following SQL:

SELECT
  spm.name, sp.is_disabled, sp.name, spm.type_desc
FROM
  sys.server_role_members srm,
  sys.server_principals sp,
  sys.server_principals spm
WHERE
  sp.name = 'sysadmin' AND
  sp.principal_id = srm.role_principal_id AND
  spm.principal_id = srm.member_principal_id AND
  spm.type_desc = 'WINDOWS_GROUP';

1-sql-statement

Click OK to load a preview into the Query Editor.

Split the name column, using \ as the custom separator. Then rename the columns to: Domain, GroupName, IsDisabled, Permission. Remove the type_desc column.

You should end up with something like this:

2-after-transformation

Click Apply & Close to load the results into Excel.

Creating a PowerQuery Function using Active Directory

The next step is to create a PowerQuery function which will query Active Directory for a given domain and group and return the members.

I found the easiest way to create this as a function was to go through all the steps for the first group then using the Advanced Query Editor to turn the M code into a generic function, which could then be reused.

This was the function that I ended up creating:

let
    GetADUsers = (domainName as text, groupName as text, ou as text, dc as text) =>
let
    Source = ActiveDirectory.Domains(domainName),
    theDomain = Source{[Domain=domainName]}[#"Object Categories"],
    group = theDomain{[Category="group"]}[Objects],
    InsertedCustom1 = Table.AddColumn(group, "distinguishedNameLower", each Text.Lower([distinguishedName])),
    ABC = InsertedCustom1{[distinguishedNameLower=Text.Lower(Text.Replace(Text.Replace(Text.Replace("CN={GROUPNAME},OU=Groups,OU={OU},DC={DOMAIN},DC=priv", "{GROUPNAME}", groupName), "{DOMAIN}", dc), "{OU}", ou))]}[group],
    member = ABC[member],
    TableFromList = Table.FromList(member, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    DEF = Table.ExpandRecordColumn(TableFromList, "Column1", {"displayName", "distinguishedName"}, {"Column1.displayName", "Column1.distinguishedName"}),
    InsertedCustom = Table.AddColumn(DEF, "Custom", each Text.Contains([Column1.distinguishedName],"OU=Users")),
    RenamedColumns = Table.RenameColumns(InsertedCustom,{{"Custom", "IsUser"}}),
    InsertedCustom4 = Table.AddColumn(RenamedColumns, "Custom.3", each Text.Range([Column1.distinguishedName], Text.PositionOf([Column1.distinguishedName],"DC=")+3,Text.Length([Column1.distinguishedName])-Text.PositionOf([Column1.distinguishedName],",DC=priv")+4)),
    RenamedColumns2 = Table.RenameColumns(InsertedCustom4,{{"Custom.3", "Domain"}}),
    RemovedColumns1 = Table.RemoveColumns(RenamedColumns2,{"Column1.distinguishedName"}),
    ReorderedColumns = Table.ReorderColumns(RemovedColumns1,{"Column1.displayName", "Domain", "IsUser"}),
    RenamedColumns1 = Table.RenameColumns(ReorderedColumns,{{"Column1.displayName", "DisplayName"}})
in
    RenamedColumns1
in
    GetADUsers

You can paste this directly into a blank query, and then test it by using the Invoke button in the Query Editor.

3-invoke-button

If you do test the function using the invoke make sure you remove this step, then click Apply & Close.

Combining the sysadmin Active Directory groups and the PowerQuery function

In this final step the list of Active Directory groups with the sysadmin permission is combined with the function created above.

Before this can be done a mapping table needs to be created for the domain name that SQL Server outputs and what it actually is. For example, as far as Active Directory is concerned your domain might be mydomain.local or mydomain.priv, however SQL Server is only going to say mydomain\groupname.

So, I created a very simple table in an Excel sheet with four columns (Domain, FullyQualifiedDomain, OU, DC). Which looks something like this:

4-domain-mapping-table

NB: To merge this table with the output from the sysadmins query the value in the Domain columns need to match exactly.

With this table selected, I clicked the “From Table” option in the PowerQuery ribbon to bring up the Query Editor so that I could merge this table with the results of the sysadmin query.

To merge this table, click the Merge button, then select the query which corresponds to the sysadmins in the drop down, then click the “Domain” column heading in each table.

5-the-merge

Click OK and a new column appears, I then expanded the column, with all the new columns of the table selected.

To use the GetADUsers function, add a custom column and call the function

6-calling-the-function

Click OK, then expand the newly added column.

I then cleaned things up a bit by renaming some of the columns and removing the unnecessary ones. The final result ends up like this:

7-the-result

Click Apply & Close to load the result into Excel and send it off to your boss / security team 🙂

Useful Resources:

http://office.microsoft.com/en-us/excel-help/power-query-specifications-and-limits-HA104054432.aspx?CTT=5&origin=HA104003813

http://office.microsoft.com/en-us/excel-help/power-query-specifications-and-limits-HA104054432.aspx?CTT=5&origin=HA104003813

http://office.microsoft.com/en-us/excel-help/start-page-HA104003813.aspx?WT.mc_id=Blog_PBI_Features_PowerQuery

http://office.microsoft.com/en-us/excel-help/power-query-formula-categories-HA104122363.aspx?CTT=5&origin=HA104003813

Advertisements

Why Bother with Biml?

Also check out an updated post on – More reasons you should adopt Biml

If you haven’t heard of Biml before, it stands for Business Intelligence Markup Language. Basically it allows you to describe Microsoft Business Intelligence objects (SSIS Packges, SSAS Cubes) in XML which are then generated into the actual objects.

At first reading you would be forgiven for thinking that this is just going to add another step in an already long winded process to build a data warehouse. That is what I used to think until I saw it in action a few weeks ago at SQLRally.

What does it look like?

Here is a trivial example which will just generate two project level connection managers, and one SSIS package which has a single Execute SQL task.

1-what-it-looks-like

As you can see, the XML is very expressive which makes it very easy to read and understand. After running the Biml generation tool the following appears (as if by magic) in the Integration Services project.

2-solution-explorer

And the package itself looks like this:

3-the-product

Mixing C# and Biml

What makes Biml really powerful is the ability to mix C# with the Biml code to generate dynamic packages. Take another trivial example:

4-biml-with-c-sharp

You can see the c# code in lines 7, 8 and 19. This Biml script will loop through the <package> element five times and in turn create five packages.

5-solution-with-dyn-packages

So why bother?

In addition to being able to embedded trivial looping mechanisms, you can get a lot more creative and sophisticated. For example, you can execute sql queries and then loop over the results. This would allow you to generate dynamic packages based on some metadata store.

At SQLRally Davide Mauri went into a lot of depth on implementing design patterns with Biml to automate the monkey work, and I think he absolutely right! He has very helpfully posted his slides and demo code on his blog, and I would highly recommend that you have a read through as it is very educational.

What I’m working on

The shop I’m currently working for stores a lot of data in SalesForce, so I’m looking at how I could implement some of the design patterns that Davide has shown with SQL Server as the source system and use SalesForce instead.

Stay tuned! (And let me know if this would be of interest to you).

References

http://bimlscript.com – A great resource of tutorials, from getting started to in-depth

http://www.varigence.com/Products/Biml/Capabilities – These are the original developers, they have their own IDE

http://bidshelper.codeplex.com – This handy addon for BIDS / SSDT BI enables you to compile your Biml into SSIS packages. If you are a Microsoft BI developer you should really already be using this!

SQLRally Nordic – My Reflections

This week I attended SQLRally Nordic edition which was held in Stockholm and I must start by saying how grateful I am to my boss for sending me. Second, thanks to all the Swedish people that spoke English to me, I had some really nice conversations with people, most of them started off “You’re Australian, but you live in London?”.

Monday: Pre-Con: Data Warehouse Modeling – Making the Right Choices

This workshop was the main reason I came to SQLRally. It was an entire day talking about data warehouse modeling! Both Davide and Thomas give a very thorough overview of the entire process end-to-end. This part of the description really stood out to me before the event:

In this workshop, Thomas Kejser and Davide Mauri will share all the information they learned since they started working with data warehouses, giving you the guidance and tips you need to start your BI project in the best way possible―avoiding errors, making implementation effective and efficient, paving the way for a winning Agile approach, and helping you define how your team should work so that your BI solution will stand the test of time

I was particularly interested in hearing about how to take an agile approach to building a data warehouse, and in future posts I’m going to cover more of what I learnt.

To summarize their approach:

  • Implement BI Design Patterns (e.g. loading staging tables, dimensions and facts, these are all well known and repeatable)
  • Automate everything, after you’ve standardized your solution with the above design patterns. Davide showed some really excellent demos of how to use BIML to create a bunch of SSIS packages based on meta data
  • Focus on the “business” processes – after automating all the “technical” processes (the repeatable and well known parts) you will have more time to focus on the parts which are business specific

Their key mantra throughout the whole day was: avoid the monkey work.

Tuesday’s Top Session: Index Impact Analysis (with Klaus Aschenbrenner)

It is a classic problem that everyone who works with SQL Server faces, how do I measure the impact of adding an index.

In this session Klaus showed how easy it is to:

  • Capture a workload of real transactions on a production OLTP database
  • How to use this workload to establish a baseline in a test environment
  • How to use the baseline to measure the actual impact of adding an index

His approach (using the Distributed Replay feature of SQL Server) is definitely one that anyone who was listening to him could reproduce. This is something that I’m going to be looking into further when I’m back in the office!

Wednesday’s Top Session: Automating Your DW Development (with Davide Mauri)

This was a more in-depth session on BIML (Business Intelligence Markup Language) and BI Design Patterns. During Monday’s pre-con Davide talked at a high level about design patterns and how to automate their implementation, however only gave a few simple examples.

In this session he showed a number of different design patterns and then demoed how they could be implemented and automated using BIML, as well as another custom tool which he developed specifically for creating packages which deal with Slowly Changing Dimensions.

The other thing that made this session stand out was the presentation style, Davide is a very energetic speaker, he spoke very quickly and still didn’t finish all of his slide deck. There was no point when I thought “come on, hurry up”. 10/10 in my book.

In Summary

I can definitely say that my expectations were met and exceeded. The pre-con was excellent, I learnt a lot of very practical things which I can start doing straight away, as well as a good amount of theory to backup the “why” I should start doing these things.

The sessions I attended were varied, but overall the quality was excellent. There was one session I ended up in which was way over my head and in hindsight I wish I had recognized this in the first 5 minutes and gone to another session. My take away from this is to actually take note of the session level before it starts.

PowerQuery – Turning My Frown Upside Down

Microsoft has started to include this very handy button in all of their new Excel add-ins.

1-sending-feedback

It is for people to send feedback to the Microsoft developers about problems they are having, problems they are solving or if they are generally happy with the product.

This week I was using PowerQuery to see if I could use it to query Active Directory (AD) and list all the members of a specific AD Group (more on this in a future post). However, whilst I was able to get PowerQuery to list the members of some AD groups it wouldn’t list them for the groups I was actually interested in.

2-powerquery-error

I reached out to a friend of mine Niko Neugebauer (twitter | web), he took a look at what I had done and couldn’t see anything obviously wrong with it and so he asked me “did you try sending a frown feedback to the dev team?”. Until he actually suggested that it didn’t cross my mind.

I promptly clicked the “Send a frown”, this brought up an email already typed out, with a screenshot of what I was doing and the formulas I had written, I added some more detail and sent it off.

I also posted on the PowerQuery TechNet forum.

The next day I received a response to my email from the PowerQuery team asking me some questions, at the same time someone who works on the PowerQuery team reached out to me on Twitter and there were two responses waiting for me on the forum, one of which was the solution.

My frown was turned upside down (to a smile), and I learnt a lesson – it’s ok to send a frown from time to time.

The answer was to turn on Fast Combine, but more on that another time. Thanks to all of you who helped me out 🙂