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.

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.

BP, mapped
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.

Passwords query that never dies
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.

Passwords in ADUC
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.

More Information

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:

  • MS 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 have discussed.
  • 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 and experiment.

Interested in learning more about other SOX IT controls? You can review the ITGI framework (PDF) published by the Information Systems Audit and Control Association (ISACA).

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.

comments powered by Disqus

Reader Comments:

Fri, Sep 11, 2009 John Mcintyre Texas, USA

Hi,

Thanks for sharing your insightful thoughts and suggestions - very cool and helpful indeed.

In the spirit of sharing helpful information, thought I'd mention that one of my Microsoft colleagues informed us about a cool FREE tool from a Microsoft partner, that offers over 50 super-helpful Active Directory security reports, such as which accounts are locked out, which accounts are set to expire in the next few days, which security groups are nested, where all a user may have permissions etc.

The tool is called Gold Finger, and it is developed by a company called Paramount Defenses. You can download it from http://www.paramountdefenses.com/g DOT oldfinger.php

Why bother writing complicated scripts, using unsupported command-line tools or paying for such tools, when you can use a 100% AUTOMATED, GUI based, FREE solution that is not only SUPPORTED but also ENDORSED by Microsoft?!

If you're into Active Directory security, then this tool is a must-have. Thought I'd share this helpful tip with you!

Sincerely,
JohnM

Mon, Mar 31, 2008 Ashok US

Hi Russel a great article, administrators can also try an easier way to manage Active Directory by allowing privileged helpdesks to schedule reports on Active Directory and delete and disable them at set times using ADManager Plus Active Directory Management and Reporting Solution. And also comply with SOX.

Mon, Dec 10, 2007 Shane Australia

For controls 2 & 3, I use a tool called "OldCmp" from JoeWare.net. Reporting, moving, disabling, deleting User and Computer Accounts, all easy to script, powerful, quick etc.

Fri, Aug 25, 2006 Gareth Oxford

Fantastic script, is there a way to show wether the users account is disabled? I have tried various combinations in the CommandText line but have not succeeded. - Thanks

Thu, May 18, 2006 Stu UK

Excellent! This showed me exactly what I wanted to know. Thank you very much.

Tue, Jan 24, 2006 Becky Nagel Editor, Redmondmag.com

Aaron, just to add to Mike's post, you can reach us at bnagel@redmondmag.com and mdomingo@redmondmag.com. Thank you!

Tue, Jan 24, 2006 Michael Domingo Irvine, CA

Aaron, can you specify the error in an e-mail to us so we can see about resolving the problem for you?

Tue, Jan 24, 2006 Becky Nagel Editor, Redmondmag.com

We did! I'm so sorry that its still not working for you. We'll look into this right away!

Mon, Jan 23, 2006 Aaron Lincoln

Becky- Did you post the updated code in the article? I copied the script from the webpage, but I am still getting an error.

Thu, Jan 12, 2006 Becky Nagel Editor, Redmondmag.com

Hi Guys -- We've made a modification -- we think it might have been a couple of extra quote marks. Please let us know if you have further problems! -- Becky

Thu, Jan 12, 2006 Becky Nagel Editor, Redmondmag.com

Hi Guys -- We're looking into the error now. We'll try to get something posted here soon. Thanks for your patience in the meantime! -- Becky Nagel, Editor, Redmondmag.com (bnagel@101com.com)

Thu, Jan 12, 2006 Chris Houston

I too am getting the compilation error. The code needs to be checked for accuracy. Any ideas? Any work arounds?

Thu, Jan 12, 2006 Kenny Lovell Anonymous

The script give me an error
Line: 13
Char: 25
Error: Invalid character
Code: 800A0408
Source: Microsoft VBScript compilation error

What does this mean? Also can I run this script from a server and on the desktop. This could be a very usefull tool for me.

Thanks

Wed, Jan 11, 2006 Robert Anonymous

Hi!

Thanks for a great article Russell.

I would like to give the readers an additional tip on a tool that make particulary Control 2 and Control 3 a lot easier. Namely, the Active Directory Janitor.

More info can be found at www.adjanitor.com

Feel free to check it out and post your comments, good or bad. Hope you all find it helpful.

Best Regards,
Robert Lundh
Special Operations Software
www.specopssoft.com

Add Your Comment Now:

Your Name:(optional)
Your Email:(optional)
Your Location:(optional)
Comment:
Please type the letters/numbers you see above

Redmond Tech Watch

Sign up for our newsletter.

I agree to this site's Privacy Policy.