Everyone today is concerned with cybersecurity and your DBA is no exception. No one wants to deal with a data breach on their watch, and the fact is, your organization is legally required to keep sensitive data secure, meet industry compliance mandates and be ready to pass an audit at any time in accordance with HIPAA, FINRA,  Sarbanes-Oxley (SOX), Payment Card Industry (PCI) Compliance and more.

Audit pressures

The pressure to keep data secure, comply with standards and pass an audit is occupying an increasingly large portion of your IT team, and especially your DBA’s time and concern. The consequences for not meeting compliance range from the mild irritant to the very severe Not only can your business be saddled with a hefty fine after a failed audit, in some very extreme cases company executives may face jail time or the complete failure of the business. Most cases are not so severe and provide organizations with the time needed to find the evidence that will prove compliance — but that is not always an easy task

Internal audits: the key to meeting SQL Server compliance

The primary goal of an audit is to prove that your security policy is sufficient to keep sensitive data secure and that your risks are managed. To ensure SQL Server compliance, organizations need to set policies and have a strategy that includes conducting internal audits before facing an external audit. An internal audit will reveal potential problems and allow the IT team to implement a fix before any issues arise. Ideally, all members of the IT team will be involved in the internal audit.

Before conducting an internal audit, you must understand which data falls under the scope of which regulations. For example, healthcare organizations will need to comply with regulations such as HIPAA, PCI, HITECH and many other state and local mandates.

Passing SQL Server Audits

According to Microsoft, auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit capabilities let you create server audits for server level events and database audit specifications for database level events. Audited events can be written to the event logs or audit files.

While this sounds easy enough, in theory, there are multiple ways to audit activity in SQL Server. In practice, there are different features and techniques with their advantages and disadvantages, and it is a complicated process, fraught with error if not properly executed. A few important notes on how to pass your SQL Server audit include:

1. Map the location of data: Many organizations do not have a firm grasp on where their data is stored, especially when in the cloud. To comply with regulations, you must clearly document where your sensitive data resides and provide proof. It is important to know what load processes and databases the data is associated with.

2. Document who has access to what data: Identity management is probably the most common problem that comes up in compliance audits. You must be very specific as to who has access to what data and the level of access granted. The accuracy of data access information must be updated on a regular basis as it’s easy for this information to become outdated when DBAs leave or change jobs within the organization. Verify that third parties, service accounts, and user accounts are accorded the appropriate level of data access.

3. Implement change management: All changes made to application code, especially when the application needs to access to sensitive data, must have an audit trail. All code must be documented: who changed the code, what was the change, why and when. If part of change management chain was broken, that’s an exception that must be reported and fixed. To pass your audit, it’s important to set up internal processes to capture and document all code changes.  SQL Server has offered change data capture since the 2008 version release which can be enabled on your database and specific tables, without having to make any changes to the objects themselves.

4. Track login histories: Although not legally required, login histories are an accepted method to prove that certain users did not have access to data after a specified date. Log and track all login successes and failures to every SQL Server database, application, and network, to provide the assurance that you can answer any question that arises in your audit.

5. Record of data breaches: Most importantly, your IT team and DBA must document and report any data breach immediately upon discovering the issue. You must be able to report when the breach occurred and which data was compromised. Although you will be deemed at fault and must pay the penalty, the fine increases the longer a breach goes unreported.

The key is to tailor your audit strategy to your unique business processes and industry compliance requirements and execute with compliance, performance, and storage in mind. Your audit strategy is dependent on knowing your data, understanding what data should be audited, and developing the right processes, but also knowing how to handle and fix the exceptions as they arise to avoid noncompliance.

Ntirety’s experienced team of database experts understand how to stabilize, optimize and maximize your database and cloud environments. We offer a customized examination and analysis of your database environment with in-depth introspection of your systems including the audit of schema changes, as well as access and security as measured against your company’s established risk and compliance requirements. Using your existing database infrastructure, Ntirety installs and configures a centralized audit system to create a secure environment for compliance monitoring that includes custom reports, data change tracking, alerts and more. Contact us to learn more.