SOX and the Single Admin
The Sarbanes-Oxley (SOX) Act poses a real compliance headache for businesses of every size, and Windows admins are feeling the heat. Fortunately, we’re here with a few handy shortcuts.
- By Russell Olsen
Does your SOX compliance stink? Is your new technology budget in lockdown because
all the free cash is going towards compliance? You are not alone.
Costs associated with making information systems compliant with the Sarbanes-Oxley
Act of 2002 have gone through the roof. A Financial Executives International (FEI)
survey of 217 large companies (average revenues above $5 billion) revealed that
SOX compliance cost an average of $4.36 million. The same survey showed that companies
spent, on average, nearly 40 percent more than they expected in the effort.
Companies are underestimating the challenge of meeting SOX compliance, and busy IT managers and Windows admins are bearing the load. A lone Windows admin may not be able to do much about strategic planning and budgets, but with the right tools, you can get the most out of the time and dollars on hand. If you’re looking for some tips on cleaning up your SOX act, you’ve come to the right place.
The 411 on Section 404
From an IT perspective, the challenge comes from Section 404 of the Sarbanes-Oxley
Act of 2002. Section 404 requires that companies define, test, implement and document
effective internal controls over financial reporting.
Think of these controls as safeguards designed to prevent and detect problems.
Home security offers a good analogy. To keep burglars out, you install an alarm
system, lock the doors and windows, put lights in dark areas and maybe get a
watchdog. Without realizing it, you’ve implemented some prevent controls (locks,
lights and watchdog) and a detect control (burglar alarm). For SOX compliance,
companies implement prevent and detect controls that ensure the accuracy of
|Figure 1. Business Process to IT Infrastructure Map: Business processes are handled by IT systems, which store financial data, leading you right into the wonderful world of managing SOX compliance. (Click image to view larger version.)
Of course, it falls to IT departments to implement these controls. Figure 1 shows the simplified process of inputting purchase orders. Orders entered into the purchasing application get stored in the back-end database and at some point are reported to the financial reporting application. No question, IT plays a significant role in the processing of order information. The question is, how do companies use detect and prevent controls to safeguard that data and ensure its accuracy?
To prevent data from being altered, IT managers can limit users’ ability to enter or approve orders, secure
the database so that the order information can’t be altered once entered, and restrict access to the overall
network. On the detection front, the company might implement logging, monitor who has entered orders,
and perform a periodic review of
user access histories. These are just a few examples of IT-related controls within the business process.
When it comes to SOX compliance, it’s up to IT managers and admins to implement these types of controls. Unfortunately, audit requests and issues can overwhelm busy IT staffs, especially when an audit finding creates a scramble to remediate the issue. This is where compliance efforts have begun to stink -- like a pair of old gym SOX that no one wants to touch.
The best way to clean up SOX compliance is to understand, automate and proactively monitor your controls. To accomplish this, we’ll take the following controls common to most companies and put them through the wash cycle:
- Only Authorized Users have Privileged Access
- All Inactive Accounts are disabled after 60 days and deleted after 90
- All Users Have Expiring Passwords
For each of these controls we’ll explain why the control is important, provide a tool to help automate the control and explain how to use the tool to proactively monitor the control.
Control #1: Only Authorized Users Have Privileged Access
Privileged access typically allows users to override, circumvent or bypass other
controls. Most database administrator IDs have direct access to modify data within
the database. If the wrong person were to gain this privileged access, he could
bypass other controls and alter data directly in the database. Or even worse,
in a Microsoft SQL Server environment, any domain administrator could create a
Either type of access rights must be limited to a small number of people and remain closely monitored.
The Solution: CSVDE
The CSVDE command is typically used to bulk export, import or make changes to
Active Directory environments. You might also be familiar with CSVDE’s twin,
LDIFDE. The CSVDE command can be a great tool for automating the monitoring
of many controls, because it allows you to get a listing of domain users and
|Table 1: Results
of the Sample CSVDE Command
CSVDE Command Syntax
C:\>csvde -f c:\dump.csv -r "(objectCategory=person)" -l "cn,memberOf"
Note: The command must be run on
a server that is a member of the domain in question. You can also copy the
CSVDE file from a server to your desktop and run the command.
Command Syntax Explained
CSVDE is the executable script that queries Active Directory and then creates
a comma separated (.CSV) data file.
–f specifies that a file will be created, followed
by the file name
–r filters based on the specified criteria
objectCategory=person will only be true for users
–l lists the specified attributes (Our command used
the following attributes: cn and memberOf)
Command Output: Table 1 shows the first two lines produced in the
dump.csv file produced in the example script above.
The DN column provides information about the user and the domain, while the CN column displays the user name for each record in the file.
The memberOf column shows the different groups and Organizational Units that the user belongs to (i.e. Enterprise Admins, Domain Admins, DBA_Group, sales, etc). In this example, the user Gary Olsen is a member of the Corp.net domain and is a member of the Enterprise Admins group.
Proactive Monitoring of the Control: The CSVDE command lets you easily monitor privileged access within your Windows environment. Open the dump.csv file using Excel or Access and perform a search for all users with "*Domain Admins*," "*Enterprise Admins*," or "*DBA_Group*" in the ‘memberOf’ field. Create an automated script and you can take monitoring of this control to the next level. With the right script, you could be the first to know when someone is added to the "Domain Admins," "Enterprise Admins," or "DBA_Group" group.
Want to earn a little extra credit? Keep a record of your review and audit
trail to show the auditor -- it’ll help dispel all doubt. For more details on
CSVDE and LDIFDE refer to Microsoft’s "Step-by-Step
Guide to Bulk Import and Export to Active Directory".
Control #2: All Inactive Accounts are Disabled After 60
Days and Deleted After 90
This control is critical in large organizations, which can have hundreds of
people come and go every few months. Meanwhile, the complexities of the HR process
can make it hard to scrub inactive accounts from an AD environment. Throw in
weak password policies, and you have the makings of substantial risk from inactive
The Solution: LastLogOn.vbs
This tool was discovered deep in the results of a Google search for the true
last logon date for Windows users. Hidden behind several commercial tools, an
written by Bill Boswell describes how to identify the true last logon date.
The "LastLogOn" script was created with a few minor modifications. The great thing about this script is that you don’t have to populate the list of domain controllers -- it will identify all the domain controllers and then query each one for the user’s last logon date (and convert it to a readable date).
'Establish ADO Constants
ADS_CHASE_REFERRALS_NEVER = &00
ADS_CHASE_REFERRALS_SUBORDINATE = &20
ADS_CHASE_REFERRALS_EXTERNAL = &40
ADS_CHASE_REFERRALS_ALWAYS = &60
ADS_SCOPE_BASE = 0
ADS_SCOPE_ONELEVEL = 1
ADS_SCOPE_SUBTREE = 2
'Get Distinguished Name for local domain
Set RootDSE = GetObject("LDAP://RootDSE")
domainDN = RootDSE.Get("DefaultNamingContext")
'Initialize ADO connection
Set connection = CreateObject("ADODB.Connection")
connection.Provider = "ADsDSOObject"
Set command = CreateObject("ADODB.Command")
Set command.ActiveConnection = connection
Command.Properties("Page Size") = 1000
Command.Properties("Timeout") = 30
Command.Properties("searchscope") = ADS_SCOPE_SUBTREE
Command.Properties("Chase referrals") = ADS_CHASE_REFERRALS_NEVER
Command.Properties("Cache Results") = False
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\Logondate.txt", True)
'Get list of domain controllers for the domain
Set dcList = GetObject("LDAP://ou=domain controllers," & domainDN)
'Walk each domain controller for logons
For Each dc In dcList
WScript.Echo "Obtaining Logon dates at " & dc.DNSHostName & "Please wait...."
command.CommandText = "SELECT name,lastlogon,whencreated,whenchanged
FROM " &"'LDAP://" & dc.DNSHostName & "/" & domainDN &"' WHERE objectcategory
Set rs = command.Execute
Do Until rs.EOF
adoLastLogon = rs.fields("lastlogon")
On Error Resume Next
Set longDate = adoLastLogon
If Err.Number <> 0 Then
logonDate = "No Local Logon"
longDateHigh = longDate.HighPart
longDateLow = longDate.LowPart
If (longDateLow = 0) And (longDateHigh = 0) Then
logonDate = "No Local Logon"
If longDateLow < 0="" then="" longdatehigh="longDateHigh" +="">
logonDate = #1/1/1601# + (((longDateHigh * (2 ^ 32)) + longDateLow)/600000000/1440)
a.WriteLine(rs.fields("name")& ";"& logonDate &";"& dc.DNSHostName)
WScript.Echo "The file 'c:\Logondate.txt' has been created"
Script Output: Below are the first four lines of output from the script:
MyAdministrator;7/12/2005 8:20:18 PM;dc1.mydomain.com
Guest;No Local Logon; dc1.mydomain.com
MyAdministrator; 7/11/2005 3:40:30 PM; dc2.mydomain.com
Joe User:9/20/2005 6:45:58 AM; dc2.mydomain.com
Script Output Explained: We see that the login information from each
domain controller is contained in the output in the following format: username,
login date time, and domain controller name. The list will probably contain
duplicate entries, similar to the example output, because each domain controller
maintains its own record of user logons. To find the true last logon date, sort
the output by user and find the most recent date. In the above output, the true
last logon for the user "MyAdministrator" is 7/12/2005.
Proactive Monitoring of the Control: Use this script to take control of your AD user base. Run it on a weekly or monthly basis and import the results into Excel or Access. Now you can sort by username, identify the most recent logon date for each user, and then re-sort by logon date.
Assuming you are within corporate policy and have management approval, go ahead
and disable the accounts of any users who haven’t logged in for 60 days or more.
Delete those accounts that haven’t shown a logon in more than 90 days. You’ll
find that this simple control is one of the best for managing network access.
Control #3: All Users Have Expiring Passwords
Expiring user passwords is an often-overlooked, but extremely important control.
In most organizations, a password policy specifies the minimum password length,
use of special characters, password expiration cycle, and other characteristics.
A common issue found by auditors is that administrators often exclude themselves
(and sometimes their friends) from the password expiration cycle, by selecting
the "Password Never Expires" flag. While there are some instances where activating
this flag is appropriate (i.e. system process user accounts), its use should
be limited and monitored.
The Solution: Custom Queries Within Users and Computers
The Active Directory Users and Computers MMC snap-in has some built-in queries
that will help us institute several controls. Two frequently used queries in
the audit world are "Disabled Accounts" and "Non-Expiring Passwords." We’ll
walk through the "Non-Expiring Passwords" query, which is easy to modify to
fit your specific control needs.
|Figure 2. Creating a non-expiring password query.
The Query: Open the Active Directory Users and Computers MMC and the first folder you’ll probably see is a "Saved Queries" folder. Within this folder you can create standard and highly customized queries. To create a query, right-click on the "Saved Queries" folder; select "New Query"; then select "Define Query"; and finally select "Common Queries" and check the "Non expiring passwords" option as shown in Figure 2.
|Figure 3. Results of non-expiring password query.
You use the results of the query (see Figure 3) to identify all users who have the non-expiring password flag set.
Proactive Monitoring of the Control: Using custom AD queries, you can identify and monitor important attributes with a minimum of time and effort. The "Password Never Expires" query, for instance, lets you clean up this critical weakness and baseline those accounts which should have this flag set. As with the other controls, you should executive this query periodically to monitor the control and maintain a clean AD environment.
The three control examples here won’t clean all the dirt out of your SOX implementation,
but they do show how even simple controls can have a major positive impact.
They’ll also help you master creative new ways to automate and monitor other
controls within the environment.
The three control examples here won’t clean all the dirt out of your
SOX implementation, but they do show how even simple controls can have a major
positive impact. They’ll also help you master creative new ways to automate
and monitor other controls within the environment. The following list of tools
will help as you tackle other SOX-related controls:
Baseline Security Analyzer: MBSA can be used to scan for security issues
and identify missing updates. This tool is very useful tool when looking at
your Windows change management processes as well.
- DumpSec (formerly
DumpACL): DumpSec is the catch-all for Windows auditing and can be useful
in gathering user information, group policy, registry information, etc.
- PASSPROP. exe:
This tool will enable lock out for administrator accounts.
- WMIC: WMIC scripting offer endless automation of many of the commands we
- PsTools: The
PsTools suite, by Sysinternals, offers a variety of tools that can be used
with an audit twist.
There is always more. Microsoft Windows offers many built-in commands that
can be used to audit SOX compliance. You simply need to be willing to explore
Interested in learning more about other SOX IT controls? You can review the
framework (PDF) published by the Information Systems Audit and Control Association
Understanding SOX and developing solid audit processes may not make your whites
whiter, but it will help clean up your SOX compliance act. With a small investment
of time and resources, you’ll find that your job will be a lot easier,
especially if you don’t have to deal with cleaning up dirty SOX.