In-Depth

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
• 01/01/2006

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 financial statements.

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 DBA user.

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 group membership.

Table 1: Results of the Sample CSVDE Command DN CN memberOF CN=Gary Olsen Gary Olsen CN=Enterprise Admins CN=Users   CN=Users DC=Corp   DC=Corp DC=NET   DC=NET

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 accounts.

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 article 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).

The Script:

'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"
connection.open
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

'file output
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 String(40,"=")
WScript.Echo "Obtaining Logon dates at " & dc.DNSHostName & "Please wait...."

command.CommandText = "SELECT name,lastlogon,whencreated,whenchanged FROM " &"'LDAP://" & dc.DNSHostName & "/" & domainDN &"' WHERE objectcategory = 'user'"

Set rs = command.Execute
Do Until rs.EOF
adoLastLogon = rs.fields("lastlogon")
On Error Resume Next
Err.Clear
Set longDate = adoLastLogon
If Err.Number <> 0 Then
Err.Clear
logonDate = "No Local Logon"
Else
longDateHigh = longDate.HighPart
longDateLow = longDate.LowPart
If (longDateLow = 0) And (longDateHigh = 0) Then
logonDate = "No Local Logon"
Else
If longDateLow < 0="" then="" longdatehigh="longDateHigh" +="">
logonDate = #1/1/1601# + (((longDateHigh * (2 ^ 32)) + longDateLow)/600000000/1440)
End If
End If

a.WriteLine(rs.fields("name")& ";"& logonDate &";"& dc.DNSHostName)

rs.MoveNext
Loop

WScript.Echo vbNL

Next

a.Close

WScript.Echo "The file 'c:\Logondate.txt' has been created"
WScript.Quit()

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 MMC
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.

Next Steps
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.