Problem solve Get help with specific problems with your technologies, process and projects.

Logging for security compliance in SQL Server

Logging for security compliance in SQL Server can be tricky. Recommendations come from all directions: vendors, auditors and lawyers. As a SQL Server administrator, it's your job to determine what's reasonable based on risk. Contributor Kevin Beaver outlines log options, and other considerations when delving into audit logging and system monitoring.

With all the government laws and industry regulations affecting business today, there's likely no one in IT that's unaffected by privacy and security compliance. Regardless of the governing body, a big part of compliance initiatives is to monitor activity related to sensitive information and to keep good records. The problem is there's so much ambiguity in the laws and regulations. When it comes to audit logging, no one – and I do mean no one – can truly provide a definitive answer on logging requirements. Logging vendors will tell you one thing, auditors something else, and lawyers, well, they inevitably have their own opinions and interpretations. This doesn't mean anyone's right or wrong. It just means that when it comes to logging in your SQL Server database environment you need to address things based on risk and what's reasonable. Determine what ultimately supports the requirements of your laws and regulations – not what someone else thinks you should do.

What's right for your business?
The word "reasonable" is tricky too because everyone has their own definition of what should be logged and how logs should be managed. In the end, reasonable logging will likely end up being defined by the courts. What you can do in the meantime though is to make sure you're in "the middle of the herd" and do not stand out. The first thing is to determine what servers and specific databases actually contain sensitive information that must be protected. This may be something you do on your own or something that has already been done in a recent information risk assessment. Keep in mind, you'll likely have some servers in development or testing that house sensitive information, and therefore, won't need any level of log.

Every environment is different and all businesses have varying levels of risk, but at least consider the following logs:

  • SQL Server logs – enabled by default and accessible via Enterprise Manager C2 audit mode – for tracking permissions, access to database objects, and more via the sp_configure stored procedure with 'c2 audit mode' set to 1.
  • Security logging of failed authentication attempts – via Enterprise Manager/Properties/Security tab
  • IIS logging – via the IIS Services Manager
  • General Windows audit logging – via Windows local policies or GPOs

When delving into audit logging and system monitoring, do yourself a huge favor and invest in a third-party logging and event management application such as GFI's EventsManager, or for plain SQL Server log auditing ApexSQL Log is an option. Visit for a list of other commercial products. These tools will save you tons of time and effort. I also recommend guidance provided by NIST's Guide to Computer Security Log Management.

Proceed with caution
Any administrator that's enabled logging knows it can create a significant impact on system performance and storage requirements. I've heard legal experts, less-experienced administrators and others that don't understand the negative impacts of logging make logging recommendations. I've heard poor recommendations including: 'when in doubt more is better,' 'log everything' and 'keep your logs forever.' Certain database security auditing tools even recommend enabling various SQL

Is your SQL Server data protected?
  • Database security: Options to protect data in SQL Server
    Encryption and data separation in SQL Server are not easy or cheap options. Read about other tools and techniques to protect against hacker attacks.
  • Avoid SQL injection with these best practices
    Avoiding SQL Server injection through validating data may be tedious, but it is usually simple and always worthwhile.

 Server logging features such as C2 audit mode to track misuse. All of this sounds good on paper and will obviously please regulators, auditors, and investigators, but at what cost?

The problem with blindly following these idealistic recommendations of vendors, auditors and the uninformed instead of planning out what you need based on business risk is that you'll create a situation where logging actually has a negative impact on your business. Whether it is eating up memory, requiring too many processor slices, or perhaps worst of all, gobbling up storage space faster than you can say petabyte. I've seen Windows and SQL Server logging enabled that has caused local system partitions to fill up in a matter of minutes and which have also created situations where the SQL Server stops responding altogether. Of all the servers on your network, your SQL Servers are likely the last ones you want to make hang or reboot. Talk about too much security getting in the way of business! Besides the technical issues, another thing to keep in mind is that logging everything and keeping the logs indefinitely can introduce legal liabilities and huge discovery expenses as well. Do I even need to mention the administrative burden and inability for the average human to get anything worthwhile out of too many logs?

Don't worry as much about what the privacy and security laws and regulations say. They certainly don't delve into logging specifics. By all means, don't try to manage audit logging and monitoring requirements for each law/regulation separately. Instead, perform a risk assessment and align your compliance initiatives with one of the information security frameworks or standards such as ISO/IEC 17799, CoBIT, or ITIL. If IT and compliance are managed at that level and done the right way, your organization should be able to meet all the compliance requirements across the board without having logs for HIPAA, logs for SOX, logs for PCI and so on.

Whatever you do, don't go at this alone. You don't want to bear the burden of making the critical business decisions associated with audit logging. This will be especially obvious when something bad happens and a regulator, auditor, or forensics investigator pins you down wanting to know your reasoning and business justification for logging what you did (or didn't do). If you and your IT colleagues are bearing a large part of the compliance burden (as is the case in most organizations), run your logging decisions by your organization's legal counsel or compliance manager. Ideally, determining what to log and how to manage logs should be mandated and supported by a compliance or IT governance committee. What I'm trying to say is get and keep others involved. You won't regret it.

Kevin Beaver
is an independent information security consultant and expert witness with Atlanta-based Principle Logic, LLC and creator of Security On Wheels. He has more than 18 years of experience in IT and specializes in performing information security assessments. Kevin has written six books including Hacking For Dummies, Hacking Wireless Networks For Dummies,(Wiley), and The Practical Guide to HIPAA Privacy and Security Compliance (Auerbach). He can be reached at kbeaver ~at~
Copyright 2006 TechTarget

Dig Deeper on SQL Server Database Compliance