SOX

FREE Tools for Auditing MS SQL Server

There’s a lot of really nice application tools to audit SQL databases out there. They have lots of bells and whistles and write out a really nice report with professional formatting. If you’ve got one of those, LUCKY YOU. But most of us Admins and Auditors have to scrounge for what we can find with the budget we’ve got (read $0).

So I always like to start out with SQLPing A nice tool from SQLSecurity.com that scans the network both actively and passively, testing SQL’s default listening port 1433 and it reports on what it finds (nothing fancy, just text). This includes those desktop versions of SQL Server: MSDE, that are often configured with SA-no password. This tool will tell you what version SQL is running, and will even do a test for SA-no password. Since SA cannot be locked out, you won’t damage the server with one attempt.

NOTE: Check with your network administrator before running this test. It’s also a great test of your intrusion detection system, because, if the IDS is configured properly, it should catch it and alert for it. Make sure management knows you are using this, if that’s what you’re going to use it for. No IDS? No worries.

I also recommend SQLSecurity. com for a lot of great information and scripts for DBAs. They know all about SQL Injection (Unlike a CIO I recently interviewed three years ago) and they have lots of MS SQL information well worth your visit.

The other free tools I use are found inside SQL Server. Yes, inside the SQL database, and they are called “Stored Procedures.” This is a fancy name for pre-written sequenced query language batch files. The folks at Microsoft have done us all a great favor by writing hundreds of them. Inside the Master database are the stored procedures you want to run, or have a DBA run for you and output to .csv format files. (Each database also has stored procedures, but the Master database SPs are the ones you want.) There is a table full of them, and here are the ones I use:

sp_helpdb Names and file locations on the Windows server of all SQL databases. (And you should find out who or what has access to those files)

sp_helpuser Review usernames, groups the user belongs to, and their default login database.

sp_helplogins a. Identify and review any external users and groups; b. Look for mappings of login name to UserOrAlias or as DB Owner. c. Check AUser and ARemote columns to identify who has remote access to what database.

sp_helpsrvrolemember Users and groups assigned to each server role (More on this later)

sp_helprotect Permissions in the database. Examine this list carefully for what rights are granted and denied to whom.

Get the results of these queries, results from the last post, and SQLPing. You’ll have some very interesting items to review. and remember, Google is your friend.

How to Audit Databases: Part I

Databases are enormous, powerful repositories of data. They can hold payroll, HR personnel data (think social security numbers) stock prices, Accounts Receivable, Client Relationship Management, and customer information. Banks can’t live without them. Most medium and many small sized businesses use them, too.

They are the motherlode of the organization and the last line of defense in a hack. It’s critical that DBAs have the tools at their disposal to monitor and provide reporting. If your database isn’t secure, the hacker won’t care how well indexed it is.

And there are a lot of ways in. If I have administrative access to the server, I can copy all the database files, take them away and reload them on my own database server. If I have unencrypted backups of those files, I can do the same thing.

So the first step in auditing the database is to examine the server the database is running on. This gets confusing to non-DBAs and auditors because many of the terms used inside the database are similiar to server terms. It’s important to keep them separate, and to make sure that access to the database files on the server is monitored. Server administrators do not need to have access to those files, but they may have to, in order to manage/backup the server. So, set up logging.
Make sure everyone who has a need to access that server administratively has a unique ID. Remove access to root(*NIX) or Administrator (Windows). They can have administrative rights, just make sure you can identify them by ID and IP connection.

Finally, what about the backup tapes? If they are not encrypted, you can join the “breach list” of companies that have lost their data when tapes were misplaced, stolen, or “disappeared.”

NEXT: Inside the Database “Server”

I Can Make Your Database Lie to You

So many financial auditors, CEOs, CFOs and others rely on electronic data to understand the complexities of General Ledger, Accounts Payable, etc. In this era of SAP, ADP, electronic time clocks, etc., the one common denominator is the database underlying each application.

Applications aren’t something you just run on one PC anymore (I know I’m preaching to the choir, here). Financial applications, especially, are all networked, and the storage is usually a relational database like Oracle, MS SQL, Sybase, DB2 or MySQL. Relational databases are wonderful for business because you can correlate so many different facts.

So why are they so scary to me? Because they are rarely audited.

I need a network ID to log in, so the database is safe, right? No.

The application has security controls, so my database is safe, right? No.

DBAs (Database Administrators) know exactly what I am talking about here. All those items are just the outer edge of security. If I have a network jack and a database ID and password, I can bypass those controls easily.

Some applications have a database ID and no password, or an easy-to-guess password. And very frequently, that ID has access to everything, including reads, writes and deletes.

If I have that ID and a network jack, I can log into your database using ODBC, Microsoft’s Open DataBase Connection client software that is installed by default on Windows operating systems. I can use Excel, Access, or other database software to pull all your data out.

Or change your data.

And P.S., connecting with ODBC uses clear text usernames and passwords, which is how I once captured a DBA’s ID and password with a sniffer.

Fortunately for all of us, there are usually other financial controls that can capture errors or changes in the database. Usually.

NEXT: How to Audit Databases

What NOT to call SAS 70 Reports

I ran across the new website “securityidiot.com” in my travels, and was reminded that it is so important to be able to laugh at yourself (and others!). It’s so easy to turn a Bad Idea into Bad Technology, these days. Or worse, another new acronym.

You should especially check out the rant on InfoSEC SPEEK that had me ROTFL. (Are “old” acronyms still OK? Or just old?) Between the hackers, the vendors, and our own pretentiousness, don’t we really have to wonder how anything really gets secured?

For example, following up my previous posts about SAS 70 audit reports:

“SAS-70 Certified” (They obviously haven’t read their own report. Maybe that’s a good thing for the rest of us.) I went to Google, just for fun, and searched on the topic after seeing one such statement in an RFP (Request for Proposal). There are an astounding number of responses for businesses that are listing themselves that way. Has no one ever told these folks that there is no such certification???

“CompanyName participates in an annual audit performed by an independent accounting and auditing firm and receives confirmation of our continued compliance with SAS 70 standards.” What standards? What compliance? It’s their own controls that were tested. Where are they getting this stuff? It’s almost painful to read.

Or, in a total munge of regulations:

“AnotherCompany, a premier provider of back office, accounts receivables and financial services announced that it has received full SAS 70 certification. This fulfills Section 404 of Sarbanes-Oxley, the corporate governance accounting mandate.” Wrong added to more wrong. SOX is not a mandate, a SAS 70 audit does not fulfill Section 404, and it’s still not a certification.

Then there’s the businesses that market themselves as having “passed” or “earned” a SAS 70. Writing your own test and passing it - Wow. What an accomplishment! For our sakes, I hope it was an “A” grade and not a “C.”

BAD marketer. BAD.

It also calls into question the quality of the organization. I don’t know about you, but reading that sort of publicity announcement from a Data Center would make me really nervous about putting my data there. And if you search those terms together on Google, there seems to be an embarrassing number (more than zero) of “Data Centers” that are doing just that.

The same feeling would apply for outsourcing my financial processes with the accounts receivable/financial services people. Some medical benefits administrators have “passed” and “earned,” too.

And it’s REALLY embarrassing when a public accounting firm offers such a “certification.”

Ouch. It hurts when I laugh.

SAS 70 Reports - Section One

Commonly, a SAS 70 Type 1 report contains three sections, and a Type 2 has five sections. That because a Type 2 tests the effectiveness of the controls that a Type 1 says are there.

The first section, the “Independent Service Auditors’ Report,” is basically a letter by the service auditor (the CPE firm performing the SAS 70 exam) to the management of the service company (the company being tested in a SAS 70 exam) providing an opinion, with some standard legal coverage, on the quality of the controls in place at the company.

The paragraph after that, if the report is a Type 2, will also express an opinion based on the tests performed as to the effectiveness of those controls. There will also be a disclaimer that the report only provides reasonable, not absolute, assurance that the controls were in place and operating effectively.

Why?

When performing a SAS 70, the auditors are dependent upon what the service organization gives them for information. When I perform logical controls testing, I must ask the service network administrator to give me the information I need for an assessment. We must rely on what the service organization provides us. We do not test the environment directly. Most organizations are loathe to let some auditor have admin rights and go wandering around their network. How, then, do we ascertain the correctness of the information?

The short answer is in the contract. SAS 70 auditors will indicate in a contract for services and in the report that they base their opinion on the information they are given. The onus is on the service company to provide true and accurate information to the auditors.

So this is not a “true” security audit, in the sense that we are not directly testing the environment. (And P.S., only a CPA can perform an “audit.” Everything else is an “exam” or a “test,” or an “assessment.”) But remember that this is a report for your company’s financial auditors, so that they can determine whether the outsourcing agreement with the service bureau is protecting your company’s assets.

But can’t the service company lie to the auditors? Sure. But they take the risk of having their socks sued off if they are committing fraud. That’s also why the financial auditors perform a financial “due diligence” on outsourced companies on an annual basis to make sure they are on sound financial footing. The SAS 70 report is just part of the due diligence to protect your company.

On to the next sections.

“SAS 70″ - It Pays to Actually READ What You’re Getting

When I do an audit and request that my client give me SAS 70 reports from his/her critical financial vendors, I am often amazed (or appalled) at what I get to read.

My team performs about 20-25 SAS 70 Type IIs every year, and maybe 2 SAS 70 Type I exams. Why the big difference? Type II exams actually test the controls the service organization says it has in place. In a Type I, all we test is that the company says it has controls, and that those controls appear adequate. BIG difference. It’s also a big difference in price for the service organization, so that companies try to get a Type I if they can.

Sometimes a service company will start with a Type I, planning to go to a Type II. I’m inclined to recommend getting a SAS 70 readiness assessment, then completing a Type II - it saves money and makes clients happier. More on this later.

Also, Type I exams only look at what control procedures are in place at the time the service auditor comes to visit (called “Point in Time,” appropriately enough). They can throw out the controls the day after that. So this type of SAS 70 has limited value to clients (your company).

For Type II exams, we test over a period of time, say the six months, or nine months, or 1 year previous, to ensure that the controls were in place and effective. The downside is that it is previous time testing, so if the controls fall apart three months after the test, I can’t report that until (or if) I come back for the next test. But it does give considerably more assurance that controls are in place, if you can see that in the previous year what we have tested is in place.

SAS 70 exams must be signed off on by a certified public accountant, even if CISAs are doing the testing on site. Make sure the company that did the test for your outsourced service is exactly that. Otherwise, the report is not legal in a court of law.

I have seen proposals (just two weeks ago, from a very big service company, as a matter of fact) that announced they were “doing” a SAS 70 as part of their security; number one, they can’t “do” one, and number two, a SAS 70 isn’t a “security” exam.

It’s an exam to provide reasonable assurance to the client company’s internal financial auditors.

So, when reading the report, you’ll want to pay attention to the sections that describe what they’re doing to protect YOUR data. If your company is using a specific application over the web, what are they doing to provide safeguards for your data on that web server or database?

A little over a year ago I reviewed a report on exactly this issue; the report tested the office Windows Domain for good control practices but never addressed any controls over their application web server: a Linux box. (Scary, isn’t it?)

There’s generally several sections to the SAS 70 report, and it’s worth knowing what to look for in each section. We’ll touch on that next.

SAS 70 Reports - Why Should You Want One?

There seems to be a lot of mis-information about what a SAS 70 report is - just today I came across a post that referenced being “SAS 70 - compliant.” There is no such thing. There is no pass/fail aspect to a SAS 70 because the Control Objectives and Control Procedures are designed by the client. It’s hard to flunk a test you designed for yourself (although I’ve seen lots of companies that do it).

A Statement on Auditing Standards #70 is used exclusively by service organizations that provide a critical financial service to their client businesses.

For instance, if your company outsources health care management to another company, your company will want a SAS 70 report from the health care management company. Why? (For starters, it’s good to know your health care mgt company takes good care of your money and personal health information.) Because your internal financial auditors are going to demand you get one from them. Health care management costs a lot of money and can have a big impact on your company should they not have good practices in place.

SOX regulations require that companies that outsource services that provide a critical financial function have a SAS 70 from that company. Banks are required by the FDIC to have SAS 70s from any service that provides a critical financial function.

So, your internal financial auditor is asking because he/she must meet regulatory requirements. Any time your company outsources a service that is deemed a critical financial service to the company, they should be asking for a SAS 70. And not just any old SAS 70.

Steps to an Easy Audit (3) - Compensating Controls

These two magic words should be in every network manager and system engineer’s lexicon. It’s your get-out-of-jail (not necessarily free) card with an IT Auditor.

Every IT shop has an application, a device, a configuration that breaks good security rules and usually corporate policy, as well. Every one. Stuff gets bought and installed by another back office group that turns out to have a boatload of security holes. A legacy router in a critical location that can’t have its IOS upgraded. A bunch of wireless registers using WEP. A database that gives every user db_owner access. A firewall rule into the corporate network when everything should be going into a DMZ. (You don’t let anything into your network from the Internet, DO YOU??? Please say no.) Anyway, these issues will all come forward in an IT Audit. If they don’t, somebody should be looking into better IT Auditors.

So, if that has to happen, what controls can you put in place, that compensate for the risk of the vulnerability? Set up a firewall in front of that pesky application that Finance set up? Make that old router log connections AND remove telnet? Lock that firewall rule from IP address to IP address and require 2 factor authentication? All these things are good, but you need one more thing:

Documentation. Document the following:
1.) Your organization acknowledges the risk (identify the issue in the application, router, or firewall)
2.) Management acceptance of a break in policy (covers your assets)
3.) How those compensating controls specifically address the risk
4.) A plan and time line for removal of the vulnerability.

After this, you can hand it to the Auditor, and it will go in their report, but it can be considered reasonable.

Now, a large caveat here: Please resist the temptation to try and make a silk purse out of a sow’s ear. I’ve heard a boatload of excuses called “compensating controls” usually because someone higher up doesn’t want to spend the money. If they won’t listen to you, be honest with your IT Auditor. Management will often listen to an auditor because the report goes to the Board. Don’t take the heat for a bad management decision by backing it up with one of your own!

The second caveat is make sure you are actually doing the compensating control, whether it’s log monitoring, or firewalling, or whatever. Be prepared for the auditor wanting to review that control, just like all the others.

Some auditors might say I’m giving admins an easy out - but I don’t think so. It just means that auditors should look a little harder and know a little more about the systems they are auditing. If we all get smarter, we all get better.

Security Policies: Five Basic Mistakes and Five More

I finished an IT audit not too long ago with an organization that did not have any policies. They had an employee handbook, that had some declarative statements that employees signed off on during their first week on the job. They are a small company growing into a medium-sized one, and part of their business maturity model was to standardize and document the structure of their organization. Having corporate policies is a critical element in business growth. Why?

Every regulatory requirement and/or compliance standard I’ve seen requires them. SOX, PCI, HIPAA, GLBA, FFIEC, COBIT, ITIL, etc. So in order to grow your business, at some point you will run into this requirement. And as an IT Auditor, I’m required to read them.

So I get to read a lot of policies - and a lot of them are bad.

An article from Anton Chuvakin highlights five basic mistakes, and I’d like to add five more (I like things in tens; you know, ones and zeros!) So here’s his five:

1. Not having a policy
2. Not updating the policy
3. Not tracking compliance with the security policy
4. Having a “tech only” policy
5. Having a policy that is large and unwieldy.

These are good points, and it’s a great read, so it got me started thinking about what I come across for policies and have been concerned upon seeing; so here’s my five:

6. Having a policy not mandated and approved by the “top of the house”

If no one from upper management has reviewed and approved these policies, they are just your opinion, or your mandate for your particular department. They do not cover the organization as a whole and provide no legal protections (enter the obligatory “I AM NOT A LAWYER” here). If the management doesn’t stand behind the policies enough to mandate and promote them, they are toothless and the employees will figure it out. So will their lawyers.

7. Having a policy that tries to incorporate standards and procedures

Quick, what’s the difference between policies, standards and procedures? (If you’re planning on taking the CISA or CISSP exams, better know this one). Go here for an answer from the FFIEC.

8. Not keeping employees educated and requiring an annual signed confirmation

Putting rules in an employee handbook that gets read (if that) during the first days of employment sends the message that security policy is not terribly important outside of HR - kinda like signing up for direct deposit and health insurance…. Keep the policy updated and make sure everyone reads and agrees annually. CYA.

9. Borrowing something you got off the Internet to make the auditors happy

Certainly my personal favorite. You may think you don’t have time to really craft a policy, but if it has been approved by management, you will be held to it in a court of law. Don’t borrow something you can’t possibly do and claim it’s your policy; when that policy is tested, you will most certainly flunk in a particularly public way. Ouch to your career.

10. Not taking ownership of the policy

Leaving security policies up to management, or internal audit….anybody but you so that you can complain about how terrible it all is and how much work you have to do in order to support it.

Consider that if you craft the policy, you can create a document that will address the needs of your environment. If it’s a realistic policy, you can build a set of standards and procedures you can incorporate into your workload. You can use these to generate statistics for getting more staff to monitor compliance and implement security. If you write it, you own it. Make it yours, make it real, it will be worth the time it takes to make it right.

Compliance is Only a “Gentleman’s C”

A comment from Dr Chuvakin reminded me of how long I’ve been thinking about “checkbox security.” As an auditor, I am certainly familiar with checkboxes, in fact, for my firm, I’ve written a number of them.

When I am going over doing an IT Audit with a new auditor, having a method for examining the environment is vital. Heck, having a method for pen-testing is vital. But it seems that so many people get caught up in thinking that the method IS the solution. If everything is checked off in the methodology, the environment is secure, right?

No. A thousand times. No. I’m sure TJMaxx had a bunch of checkboxes filled in for somebody. Didn’t do them a darn bit of good.

A few years ago I did an internal pen test for a company, and discovered that their use of a web proxy required that the user log in via HTML each time they went to the Internet. Long story short, Cain and Abel easily decrypted their casual hash for me and I was very shortly inside the network, up to admin level AND the CFO’s password. (Geez that was fun; but I digress…)

I asked my engagement manager, who was also doing a SOX 404 audit for them, if their SOX audit would have found this issue. No, of course not! Auditors don’t run Cain and Abel! (Maybe they should, eh?)

So where would that have left that company? SOX “compliant,” but still easily broken into by anyone with a simple tool. Not good. So much for checklists, checkboxes, and methodologies. The difference was, the company cared enough to pay for a quality pen test, not just someone coming in to run a scan. They changed their proxy, and now this issue no longer exists. They’re proud of their security, and they should be.

But if we are not thorough and specific, we can miss the obvious “low-hanging fruit.” In my mind, that’s all an auditor can really hope to do. And even that seems to be a full time job.

So, for those folks who say, “we’re compliant!!!” it doesn’t mean you are supporting a secure environment. It means you’ve gotten all the little boxes checked in someone’s methodology. It’s a “Gentleman’s C.”

What would an “A” look like? More on that later.