According to Microsoft’s Best Practices, specifically, their SQL Server 2005 Security Best Practices Document, Windows Authentication is the preferred method of authentication in the SQL Server world.  They state that SQL Authentication should only be used, when possible, for legacy, third-party, or non-Windows applications whenever possible.  Personally, this is good news, our environment is well-managed with groups (as opposed to assigning rights directly to users) and our system administrators are completely on board.  So, as older legacy applications have been rolling off to archive, I have made a concerted effort to push for only using Windows Authentication wherever possible.  This has been a great success and almost all of my SQL logins are gone, but taking the authentication out of the sole hands of the DBAs can cause other problems.

On more than one occasion, a user has requested access to some system of a low-level helpdesk person.  The helpdesk person, meaning well, but not aware of the implications, has placed said user in one of my “DB Only” groups in my “Database Groups” OU in Active Directory, thus granting them some level of access to a database.  While this is not inherently bad, it certainly can lead to a user having access to a database which they should not, for compliance reasons, or otherwise.  Usually, your system administrators have greater access to Active Directory than you, because it is, after all, their system, you’re just using it.  And even if you have trained your systems guys well, someone can malevolently alter access without your knowledge.  So what can be done about this?

In my environment, I took several actions to help give me better control and visibility into my Windows Authentication groups.  First, long ago, I had my systems administrators create an OU that was all mine.  My DBA group had full delegated rights to it and we could create OUs under it to help us keep things organized, create groups, and assign users to said groups.  This has helped in my migration away from SQL logins and toward Windows Authentication, without having to bother them every time I need a new group for some set of permissions.  It also created a logical divide that I could point to for those helpdesk guys who might accidentally grant rights to users.

Recently, however, I wanted to make sure I had a good audit of to whom which rights were being assigned.  I have also been trying to dive into PowerShell, so I thought this would be a good opportunity to stretch a little and figure out how to get this done.  I am not going to delve too much into PowerShell syntax or anything, there are tons of blogs out there that are way, way better than me at PowerShell, I am just learning it now.  If you’re just getting started in PowerShell, may I recommend Sean McCown’s videos on the subject.  They are a great place to start getting your feet wet.  Here is a link to the first in the series.

I knew I would need four basic components to this mini-project:

1. A Powershell script to query Active Directory for all groups and group members in my DB OU
2. A Task or SSIS Package to pull the output of the script into a holding table
3. A sproc to compare the newly captured data with the last known state of data to find the differences and then overwrite the existing data with the newly captured
4. Generate an e-mail to notify the DBA team of any differences found in the previous step

I didn’t have any concern with steps 2, 3, or 4, I had done that sort of thing hundreds of times, the Powershell script was the interesting part, so I will focus on that piece for now.  I may address the rest in another post later, if anyone is interested.

Now, I am sure there are dozens of scripts that do exactly what I need spread all over the Internet, but I wanted to learn how to do this, so the end product may be a bit unpolished, but here we go.

From my limited knowledge of PowerShell and my software developer background, I knew I would have to load a module to hit Active Directory.  So, I started Googling for what that particular module would be called, easily enough it came up right away as “ActiveDirectory”, simple enough.  So, I opened PowerShell:

Hmmm, no dice.  I thought it must be a Role missing on my machine, so I went to Server Manager and looked through the Roles.  The closest thing was “Active Directory Domain Services” which would certainly do it, I’m sure, but I think that would also do a dcpromo, which I definitely do NOT want to do.  I don’t want my systems guys getting pissed at me for having made my test server a DC on the domain.  So, I called my systems guy, who knows a little PowerShell, and asked him if he knew of a way to only install a portion of the Role.  He informed me that what I probably needed was “Remote Server Administration Tools” Feature.  Well, that made sense, so I went ahead and installed that Feature (Warning: requires reboot).  After my server came back up, I tried the Import-Module again:

Looks like it’s working, so I verified it:

Yep, it’s loaded.

Next, I wanted to browse to my DN (Distinguished Name, which is the long string that defines an object in an LDAP structure, like Active Directory, they usually look similar to CN=SomeGroup,OU=SomeOu,OU=SomeParentOU,DC=domainPortion,DC=domainPortion).

So, I switched my directory to AD and then to my specific DN:

Now we are at my OU within my domain’s Active Directory.  So, I did some querying to try to find out what I could do here.  Typing “gci” or “dir” get’s you a list of all child elements, and my guess would be that the child elements would be OUs and groups that I have created under my DB OU:

And that’s precisely what I got.  OK, I thought, what if I do this recursively?

And hundreds of groups and OUs popped up on my screen.  Great, this is exactly what I need for the first portion of my script.  But, I don’t want the OUs, at least not for my immediate purposes.  Ultimately, I don’t care if someone comes in and creates an OU in my DB OU, it’s not good organization, but it’s not going to directly affect security on my SQL Server.  So, I want to ignore anything but groups:

Much better!  So, I’m going to store that to a variable for later use.  I’ll use the variable to export out my list of groups to a CSV as well as use it as the base for the next part: getting a list of all of the users/groups in each of my groups.

So, what method can I use to find out what users or groups are in a group?  I have the DN of the group, can I switch to it like a directory and then just get the children of that location?

Well, yes, I can switch to that specific group, but no, I cannot simply get it’s children.  There must be another way.  So, when in doubt, ask the system itself:

I issued the command, “Get-Command -Module ActiveDirectory”, which tells the system to tell you which commands are available in the module.  The fourth in the list talks about GroupMembers, so I wonder if there is a Get-ADGroupMember, scrolling down:

Yep, let’s give it a shot against one of the groups:

Great! It works! I get a whole list of each user or group in that group.  This is exactly what I need for the second portion of my script, the relationship between groups and their members.  Now I just need to collect all that data for each group in my variable and export the new collection to another CSV.

Well, there were hundreds of groups, so there are likely to be thousands of members collectively in those groups.  So, I don’t want to append a CSV file with each write, so I am going to need to create some structure in memory to hold all of this data as I parse through it and then dump it all out at once to my file.  So, to store this 2D data, I would like to have an array of some custom STRUCT.

For those who are unfamiliar with programming, you may have never created your own struct or class before, but it truly is very simple.  Basically, to put it in database terms, I am creating a simple data structure, that could be loosely compared to a single row of data.  I will then have an array of these structs, making the whole data structure not too dissimilar from a table, where each instance of the struct is another row, and each row contains several fields.

To create my struct I am going to use the add-type command which takes a string that contains the definition of my struct and it’s constructor.  Don’t get too hung up on this part, I can go into deeper detail in another post, but for now, just know that this data type will have four fields and a single method that allows it to be loaded with data.  The code to create the type looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

add-type @"
public struct DBgroupMember
	{
	public string distinguishedName;
	public string name;
	public string samAccountName;
	public string group;
	public DBgroupMember(string DistinguishedName, string Name, string SamAccountName, string Group)
		{
		distinguishedName = DistinguishedName;
		name = Name;
		samAccountName = SamAccountName;
		group = Group;
		}
	}
"@

Now we have our data type (named DBgroupMember) ready to be used to store each group member’s DN, name, username, and the group to which they belong.  We just need an array into which we are going to put all of these individual instances.

1

$DBgroupMembers = @()

Easy enough.  Now, remember that variable in which we stored the list of groups, now we need to iterate through each group in that list and get that group’s members, store them in a new DBgroupMember object, and place that object in our array, the code for that looks like (including the declaration and assignment of the variable that holds the list of groups):

1
2
3
4
5
6
7
8
9
10

$DBgroupList = gci -Recurse | ? {($_.ObjectClass -like "group")}
foreach ($DBgroup in $DBgroupList)
	{
	$thisDBGroupMembers = Get-ADGroupMember $DBgroup
	foreach ($thisDBGroupMember in $thisDBGroupMembers)
		{
		$DBgroupMember = new-object DBgroupMember ($thisDBGroupMember.distinguishedName, $thisDBGroupMember.name, $thisDBGroupMember.SamAccountName, $DBgroup)
		$DBgroupMembers += $DBgroupMember
		}
	}

And now we have two data structures in memory, one ($DBgroupList) which holds a list of all of the groups we care about, and the other ($DBgroupMembers) which holds a list of all of the members of all of the groups.  Now we just need to write those out to their files:

1
2

$DBgroupList | Select-Object Name, DistinguishedName | Export-Csv O:\Scripts\PowerShell\ActiveDirectory_AllDatabaseGroups.csv -notype
$DBgroupMembers | Select-Object distinguishedName, name, samAccountName, group | Export-Csv O:\Scripts\PowerShell\ActiveDirectory_AllGroupMembers.csv -notype

In each of these commands, we are taking our variable, selecting out the fields we care about, and using the Export-Csv command to some local disk location, specifying “notype”, which removes some file markup that would only make our job of importing the CSV into SQL harder later.  You can read more about the Export-Csv command here.

Let’s put it all into one script, just to see it all in one glance:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

Import-Module ActiveDirectory
cd AD:
cd "OU=YOUR_OU,OU=ANOTHER_OU_MAYBE,DC=PART_OF_YOUR_DOMAIN_NAME,DC=ANOTHER_PART"
add-type @"
public struct DBgroupMember
	{
	public string distinguishedName;
	public string name;
	public string samAccountName;
	public string group;
	public DBgroupMember(string DistinguishedName, string Name, string SamAccountName, string Group)
		{
		distinguishedName = DistinguishedName;
		name = Name;
		samAccountName = SamAccountName;
		group = Group;
		}
	}
"@
 
$DBgroupMembers = @()
 
$DBgroupList = gci -Recurse | ? {($_.ObjectClass -like "group")}
foreach ($DBgroup in $DBgroupList)
	{
	$thisDBGroupMembers = Get-ADGroupMember $DBgroup
	foreach ($thisDBGroupMember in $thisDBGroupMembers)
		{
		$DBgroupMember = new-object DBgroupMember ($thisDBGroupMember.distinguishedName, $thisDBGroupMember.name, $thisDBGroupMember.SamAccountName, $DBgroup)
		$DBgroupMembers += $DBgroupMember
		}
	}
 
$DBgroupList | Select-Object Name, DistinguishedName | Export-Csv O:\Scripts\PowerShell\ActiveDirectory_AllDatabaseGroups.csv -notype
$DBgroupMembers | Select-Object distinguishedName, name, samAccountName, group | Export-Csv O:\Scripts\PowerShell\ActiveDirectory_AllGroupMembers.csv -notype

I think that does it for this particular post.  If there is interest, I will go ahead and do another post about the rest of the process, but I hope this has given you some insight into how to browse Active Directory from within PowerShell.