Exporting User List from Active Directory

Sometimes you just need a simple file with a list of users in it.

In my case, I've made various programs to streamline and automate the work of my department. We "feed" one of these programs user data from Active Directory and elsewhere so it can make and delete accounts when students transfer in or out of the district.

You may not have a custom system like that, but there are many other reasons to be able to export data from Active Directory into a spreadsheet or text listing. One example would be turning over a list of users to the payroll department, so they can tell you what accounts should have been closed but slipped through the cracks. (Side note: I actually recommend doing at least annually and preferably every three to six months.)

To make such a list, login to a Domain Controller for your Active Directory system as a Domain Admin, run the command line, and use a command like this:

csvde -f ad.txt -n -d "ou=students,ou=People,dc=controller,dc=example,dc=com" -r "(&(objectCategory=person)(objectClass=user))" -l "sAMAccountName,givenName,sn,description"

That was probably too long to fit on the page, so let's break it down.

• csvde:
  This will make a file in the current directory (a.k.a. folder.) That file is in the CSV format. To remember this command, think of it as as "CSV Data Export."
• -f ad.txt:
  This file will be named "ad.txt".
• -n:
  Any binary data is excluded.
• -d "ou=students,ou=People,dc=controller,dc=example,dc=com"
  It will limit itself to data in the Organizational Unit (OU) named "students", which is inside "People", and in the Active Directory system at controller.example.com.
• -r "(&(objectCategory=person)(objectClass=user))":
  It will limit the export to only user accounts. For example, if there are computers or groups in that OU, those will not be exported.
• -l "sAMAccountName,givenName,sn,description":
  Its columns will be the username, the first name, the last name, and the description. Note that the first name is labeled "givenName" and the last name is labeled "sn" as in "surname."

If you want to change the OU, just adjust the part after the -d to include your OU and DC structure. If you want to change the data in the export file, just change the part after the -l. To learn more details, check out Microsoft's article on the csvde command.

If you adjust this to suit your environment, you should be able to generate CSV files that list your users very quickly. At my job, we can export over 1,000 users in under a minute. The CSV file can be read by scripts we write or imported into a Google Sheet and shared with Payroll for a quick account audit.

G Suite and Social Security Numbers

A nearby school was hit with a spearphishing attack not long ago. As a result, their employee's personal information, including social security numbers, were stolen.

To protect my coworkers from a similar attack, I set up an email filter to catch messages with social security numbers in them. These messages are quarantined and reviewed by humans, who then visit the sender in person to review the situation. When I discussed this with my counterparts in other districts, they were very interested. So I decided to document the process here in case others could benefit.

At my job, we use G Suite for Education. It has a feature called "Content compliance" which uses regular expressions. So I went to admin.google.com, clicked on "Apps", clicked on "G Suite", and then clicked on "Gmail". From there, I clicked on "User Settings", selected the root OU, and scrolled down to "Content compliance".

In "Content compliance", I added a new rule. If you do this, make sure that rule affects outbound messages. I recommend also adding "Internal - sending" and "Internal - receiving", as well. This can help in the event of one coworker's account being compromised by a bad actor who then requests data from someone in Payroll. In my case, I enabled all three of those conditions. However, I didn't enable "Inbound". My thinking was that if the message already traveled across the Internet, it was already vulnerable and there was hardly a point. I recommend you consider your own case and make your own decision.

Then add the following regular expression (a.k.a. "regexp" or "regex") as a rule:

(^|\s)\d{3}-\d{2}-\d{4}(\s|[[:punct:]]|$)

In step three, I set the action to "Quarantine message". Then I saved the settings.

Lastly, I ran some tests. I made a test account and sent several messages to it. Those included the fake social security number of "123-45-6789" in the subject, the body, and in an Excel file attached to a message. In each case, as the administrator, I was sent a message telling me a new message was delivered to the quarantine with a link to take me there. I had the chance to review the message and reject it or approve it. If rejected, the message is sent back to the original sender with a vague message about it violating the recipient's content policy.

One final note: Don't forget the human factors.

If you decide to implement this, I strongly recommend discussing quarantined messages with their senders prior to rejecting or approving them. In my experience, people have responded quite well to this approach. I always explain the events at our neighboring district (without naming them), explain that I took steps to protect us, and finally explain that their message was caught in this filter. Even in cases where they were sending their own tax forms to another account they control, they were sympathetic to my intent. I was also respectful of their right to make their own decisions -- even if it put them at risk.

I think of it this way: If Mrs. Smith sent her student loan paperwork to herself, then I gave her my advice. She may choose to accept it (and I reject the message) or to accept the risk (and I approve the message.) That is her choice, as she is only putting herself at risk. In cases where someone is putting someone else at risk (e.g. Payroll sending out W-2 paperwork for employees), I speak to them and confirm that it is legitimate. If it is, I try to help them find a safer way to get their work done. By taking this approach, people generally react well and are appreciative.

Email Security Presentation

I recently had the opportunity to offer email security training to my coworkers. A few days later, our payroll clerk received a spear phishing message and realized it was a scam. She is quite sharp and probably would have spotted it anyway, but it got me thinking that others might not be so lucky.

I've modified my initial presentation for more general use. A number of details that I discussed verbally have been added to the slides and/or speaker notes. I am publishing this under a Creative Commons license so others can use it in their organizations. I recommend customizing it to suit your organization's needs before using it, but it should save a lot of time compared to building a presentation from scratch.

The one thing I ask in return is that you include a link back to this blog. Other than that, it is my earnest hope that you find this tool useful.

Options: Play in full screen | Make your own copy | License

NTP Server Testing

If you run an NTP server, you should try this site. It is good for checking if your NTP server is available to your users when they're not on your internal network. It can also be helpful for running some security testing.

SSHguard & IPFW

It seems that SSHguard 1.7 dropped support for the "hosts.allow" file. Since I already wrote about how to setup SSHguard 1.6.x on FreeBSD using TCP Wrappers, I thought I should offer a quick update for SSHguard 1.7.x.

Please note that the process shown here assumes FreeBSD 10.x and SSHguard 1.7.x. The directions will go through five major steps: (1) Install SSHguard, (2) get SSHguard configured, (3) get IPFW started, (4) restart to bring it all together and make sure it works. Lastly, I'll cover (5) how to look at what is happening.

First, install SSHguard from the ports collection or upgrade it with portmaster, as needed. For example, to install it for the first time, you could:

su
cd /usr/ports/security/sshguard
make install

When prompted, select IPFW. To clean up unnecessary files, you can type "make clean", but this step is optional. If you already have it installed, check the FreeBSD Handbook for directions on using portsnap (section 4.5) and portmaster (section 4.5.3.1) to upgrade the port. Just be careful to avoid breaking any other ports in the process.

Once you have SSHguard 1.7.x installed, you'll need to configure it. Start by whitelisting any necessary IP addresses by adding them to the file "/usr/local/etc/sshguard.whitelist". Only add one IP address per line. I recommend preceding those lines with a comment to help you remember why you added them. Comments are lines that begin with a ”#”. So you might have something like this:

# Don't block the VPN server.
12.34.56.78

The last step in configuring SSHguard is to tell your system to run it at boot time. Do this by adding these lines to "/etc/rc.conf":

# Start SSHGuard
sshguard_enable="YES"

Now we should be ready to move on to the IPFW system. This replaces the much easier to manage TCP Wrappers system we used in SSHguard 1.6, a.k.a. the "/etc/hosts.allow" file. The first step is to enable the IPFW firewall at boot time by adding these lines to /etc/rc.conf:

firewall_enable="YES"
firewall_logging="YES"
firewall_type="open"

If you don't want to reboot the system, you can manually start the firewall by running "service ipfw start" as root. This is a great way to make sure that all your settings are right so far. However, don't do this unless you have physical access to the server. Changing firewall settings when you only have a network connection to the system is a quick way to get locked out of it by accident. The steps I've listed here won't do that, but I don't know what other changes you've made before reading this article, so I'm including this warning just in case.

Now that we have IPFW running, SSHguard will report suspicious activity to what IPFW calls "table 22." We need to tell IPFW to block everything listed in table 22. To do this, edit /etc/rc.local and add the following lines. If the file already exists, skip the first line and add the rest to the end of the file.

#!/bin/sh
echo Adding sshguard to IPFW settings
/sbin/ipfw -q add 55000 deny all from 'table(22)' to any

Next, allow the /etc/rc.local script to run during system startup. To do that, run this command as root:

chmod 0755 /etc/rc.local

Restart the system with "shutdown -r now" to confirm that this script runs properly at startup. Watch for it when the startup messages scroll by on console and also check the settings by logging in as root and typing "ipfw list".

That is basically it. I encourage you to review these steps and try to learn what they do and why they work that way. You'll probably find ways to adjust this process to fit your system a little better. However, this should get you started.

If you ever want to know what the IPFW firewall is doing, you can check its rules by running "ipfw list" as root.

If you want to see the SSHguard rules, specifically, you need to look at table 22. SSHguard keeps these rules isolated in their own table, so they don't accidentally overwrite anything else nor create any other unpredictable outcomes. To look at table 22, just run "ipfw table 22 list" as root.

Lastly, if you're setting up SSHguard for the first time, please read the end of my older article as well. It includes some advice that is still relevant. Also, remember that SSHguard should only a piece of your risk mitigation strategy. No single product or trick will ever cover you completely. Even if you're not an expert in Information Security, you should always strive for defense in depth as a way to reduce your risks.

SSHGuard

If you run any Unix-like systems, you probably know that the bad guys are trying to break into your server over SSH. You can use fail2ban or sshguard to greatly reduce the chances of a break-in. Below, I show how I setup sshguard on my FreeBSD servers. Its quick and relatively easy, so I consider it a requirement for all of my FreeBSD servers.

First, install sshguard from the ports collection:

cd /usr/ports/security/sshguard
make install

Next, tell FreeBSD to allow it to start by adding this line to /etc/rc.conf:

sshguard_enable="YES"

Then start it and stop it. This will create a configuration file that we'll use.

service sshguard start
service sshguard stop

Now the file /usr/local/etc/sshguard.whitelist should exist. Edit that and add any IP addresses that you might need to whitelist. For example, I whitelisted my network monitor. My reasoning was that it would test if the SSH service was still running every 60 seconds. So this would look like a break-in attempt and the monitor would be blocked after a few minutes.

You might also wish to whitelist certain other hosts, but I don't recommend whitelisting everything in your internal network. If someone did manage to break into your web server or some other system, they could then use "island hopping" to get to this host and break into it, too. So sshguard makes that harder on the attacker.

To add items to the whitelist, just add one IP address or FQDN per line. If you want to insert a comment, you can start the line with a "#" and then write your comment. I highly recommend putting a comment just above every entry. A few years from now, after an IP address is assigned to a different server, you might not remember why it is in your whitelist. Comments can help your future self save time.

Now just start the service back up with this:

service sshguard start

To confirm that it's running, try this:

service sshguard status

That is all it takes. If sshguard sees a suspicious attempt to login, it will add the IP address to the top of /etc/hosts.allow as a "deny" rule. It will take care of things all by itself from now on.

If you find that it blocked an IP by mistake, you can remove the block by just removing its IP from the hosts.allow file. Just be sure that you can really trust that IP. Maybe someone put a rootkit or bot on that host and sshguard is doing exactly what it should be doing. So be confident that its an error before removing the IP.

A few last notes: First, sshguard will log some data in /var/db/sshguard/blacklist.db. As far as I can tell, this is more or less just a log. I think sshguard uses it at startup time, but I'm not sure. If you need to remove the blacklisting from an IP, edit /etc/hosts.allow instead.

Second, there are a few different ways to setup sshguard. One of them involves piping data from syslog into sshguard. Others involve using PF or IPFW instead of hosts.allow. I haven't used those option and don't know the relative advantages and disadvantages of each method. What I've presented here is what works for me. Please feel free to research the options further and do what works for you. If you know why I should consider another method, please leave a comment on this article. I would truly appreciate the advice.

Lastly, don't forget that this is looking for persistent failed logins from a single IP. Advanced Persistent Threats can use botnets to try out one or two passwords from an IP and then one or two from another IP and so on. Patient attackers might also try one or two passwords every few hours until they guess right. People who know you or looked at the password list you keep under your keyboard are much less likely to be stopped by sshguard. The bottom line is simple: sshguard helps reduce risk but security is a mindset and not something any single product can give you. Be smart and be safe out there.

SFTP Connections from PowerSchool

At my job, we store student data in a program called PowerSchool. One of PowerSchool's features is AutoSend. AutoSend can make a text file full of data and send it to another computer over SFTP. This is very useful, as it allows student data to be entered once (in PowerSchool) but appear in many systems.

Recently, I replaced and updated the FreeBSD system that runs our SFTP server. After the upgrade, PowerSchool couldn't send data to the FreeBSD SFTP server. Other SFTP programs, such as FileZilla, were able. This issue only seemed to affect PowerSchool's AutoSend. I couldn't figure it out at first. The FreeBSD community couldn't. Our tech support couldn't. They escalated the issue over and over until it reached "engineering" and I never heard back from engineering.

After working on this on-and-off through the summer and eventually found a way to make it work. Since it took me so long and confused so many other people, I wanted to put this out there for others to benefit.

The short version is this: I had to change the default settings of the SSH server.

This may sound strange to some, but SFTP on many Unix systems -- such as FreeBSD and Linux -- is based on OpenSSH. OpenSSH is a system that is all about making encrypted connections and preventing the bad guys from seeing what you're transmitting. However, its default settings moved to a more strict standard at some point and this was why I saw a difference between the old FreeBSD server and the new one.

The setting in question deals with how OpenSSH handles authentication. In plainer language, it's all about the login process. It seems that "keyboard-interactive" is the mode used by programs that interact with humans, such as FileZilla. However, "password authentication" is the kind used by automated systems, such as PowerSchool. Personally, I found the name "password authentication" to be confusing for a while, but that is what it is called.

So I edited /etc/ssh/sshd_config to allow the password authentication system. However, the OpenSSH developers disabled it for a reason. They know more about computer security than I do, so I struck a compromise. I changed the settings so that password authentication was valid if and only if the connection came from my PowerSchool server.

If you need to do this, just find your system's copy of sshd_config and add the following lines to the bottom of the file. If you have any lines beginning with "Match", this should go immediately above those.

# Turn off PasswordAuthentication in general, i.e. without a Match statement to change it.
PasswordAuthentication no
# Allow only the IP address of the PowerSchool server to use the
# PasswordAuthentication directive. Note the "PasswordAuthentication no" 
# above all Match statements.  That is part of this configuration, but 
# must be before any Match blocks.
Match Address 999.999.999.999/32
        PasswordAuthentication yes

You should change "999.999.999.999" to the IP address of your PowerSchool server. Everything else should be fine exactly as I wrote it above.