Compliance audits ensure that organizations adhere to regulatory guidelines, industry standards, and internal policies. As SQL Server DBAs, you can be prepared for most audits if you already check all of the boxes outlined in this blog post. Each of these points could be its own blog posts, so take your time as you go through each and use this as a general guide on your compliance journey!
Understand the Specific Compliance Requirements
Different industries and regions have varying compliance regulations (e.g., GDPR, HIPAA, CCPA). Begin your process by identifying which ones apply to your organization and understand their data-related provisions. I have curated most of the overlapping topics into subsections below:
1. Data Classification
Many regulations (SOX, PCI DSS, HIPAA, GDPR) have requirements for specific types of data. Classification of this data makes it easier to manage in the database system, as well as simplifies the addition of data retention rules compliant businesses are expected to follow.
2. Access Controls
Verifying that only those with specific reasons have permission to access the database, as well as ensuring that everyone who does have access has just the right amount of permissions to do their job. This is the principle of least privilege. Combine least privilege with separation of powers, AKA “What protections are in place to protect the business from a disgruntled DBA or a compromised SA account?”. Consider reviewing one of my previous posts about access audits to find out who already has access to the database and removing access or reducing privilege where possible. Another concept is to assume breach. This leans more into auditing down the road but also assumes every account could be compromised, so how do you plan on reducing the blast radius? We have seen a company negatively impacted by a disgruntled employee actively deleting production data. The permissions were there, but they missed completely removing access the employee’s access after they were fired.
3. Data Encryption
Data encryption is a necessity for protecting sensitive data. Remember always that there are two sides to every coin, internal and external protections. Volume level encryption and TDE helps to encrypt the data at rest from the outside. A permissioned user can still access the data, and if they have access to the TDE Certificate, they have access to use the backups as well. Always Encrypted is an internal protection where sensitive data can be encrypted by the application and then sent to the database. Ensuring that all connections to the database must be encrypted is an External protection for data in motion, and when TDE is used, TempDB is automatically encrypted as well. I once saw a client who was plagued by ghost emails. Issues and job failures were being emailed, seemingly about the production server and the jobs, but no evidence could be found. Lo and behold, a developer had made a copy of the production server and had it on a personal testing instance.
4. Audit Trails
SQL Server auditing, and with 2022’s ledger feature, changes and visits to sensitive data are easier to track than ever. So long as you are following best practices and logs and digests are going to a secure location, regular review of access and changes to sensitive data should be done and anomalies investigated further. Some of our clients even have every connection to the database servers recorded. It should be drilled in that the first question many DBA’s ask when a “new issue” appears is “What changed?” Do you have the answers?
5. Regular Backups
A necessary part of compliance is a comprehensive data security program, RTO and RPO, and incident response. The lynchpin of business continuity is the presence of a recent restorable backup. Ensuring that backups are occurring within thresholds to meet SLA and disaster plans are necessary. Automating backups is needed, copying backups off-network into safe vaults is recommended, and testing backups through restore processes is critical. Your business is only as safe as your most recent restorable backup. We have seen on more than one occasion companies taken out by a disaster with no backups to help rebuild or ransomwared with all their backups on a local network share also encrypted with no recourse but to hope the payment and decryption don’t ruin the SQL files.
6. Data Integrity
SQL Server has built-in tools to assist with verifying data integrity. We recommend our clients run at least weekly integrity checks for user databases and daily integrity checks for system databases. These, along with ensuring that your server is configured with healthy settings, not allowing dirty reads, and working with the application for data validation, or if you don’t have much control, using CHECK constraints to limit the allowed data and formatting into specific columns will all help in ensuring data integrity. Some companies come to us with 10-year-old servers that have never done an integrity check. One of our great concerns in that instance is that they’ve had corruption for 8 or 9 of those years with no recourse to help them other than spending significant time hoping to succeed in data surgery or otherwise needing to allow data loss.
8. Data Retention and Disposal
Each regulation has different retention expectations for sensitive information. This step is easier if the data is already classified, building jobs or using other tools to check the age of data and delete it automatically. This, paired with regular verification checks to ensure the processes are working as expected, helps to ensure compliant retention.
9. Incident Response
You may never have a security incident, but being prepared for one is well worth the time in ensuring it is managed well if it does happen. The time spent beforehand planning and understanding the steps you need to take should everything go up in flames is time saved in the midst of the disaster. We have seen quite a few companies in crisis where they hadn’t planned for what to do if their data center went down, if ransomware were to occur, or if significant outages were to occur. We agree with the best practices of having a plan for many different scenarios, but to reiterate what I mentioned above about database backups – TEST THEM!
10. Documentation
What if all else fails? Your Database server is a smoking crater. How did everything work? The importance of documenting your environment cannot be understated. We have had “hero stories” where a client’s AG node self-destructed or a ransomware event called for a complete flattening and rebuild of the environment, and because one of our senior team members had documented the process when she built it, it went up with very little friction with hours saved. Change control is another aspect of environment documentation to consider because as always. One of the first questions we need to ask when something new is going on is – “What changed?”
11. Continuous Monitoring
Monitoring for failing jobs, error log events, application log events, AG latency, performance, and disk space – the domain of the DBA – these things should always be on a pot, simmering in the background. Never forgotten! We automate alerts and filter out noise so that we can catch important issues quickly. One of our greatest compliments is when we hear our clients say, “We can sleep better knowing you are watching our servers.” Our tool monitors 24/7 and we review and remediate issues during the work week. If you need help in continuous monitoring, let us know!
12. Third-party Interactions
One of the major compliance concerns is third-party tools, applications, and vendors. Carefully vetting all three and ensuring they are updated in the case of tools and apps is needed. Confirming the security posture of your vendors is also necessary. Especially so because they may not follow the same protocol your company does. Most compliance audits will require a review of how your vendors operate securely.
13. Employee Training
Compliance is the duty of every employee. Should one person in accounting accidentally send the wrong medical invoice to the wrong patient, this is a HIPAA violation and is required to be reported. Training employees is one of the most important jobs of a compliant business. Each employee must know the general rules, specific behaviors, and responsibilities, as well as the dangers of failing to meet the compliance standards.
14. Self-assessment and Mock Audits
Before doing official compliance audits, conduct in-house test runs to pinpoint areas of improvement. Engage with experts to guide these evaluations. Most compliance certifications or attestations require external auditors. Some of these companies offer checklists for these internal test runs.
15. Engage with Stakeholders
You already know that compliance extends beyond the DBA realm. Staying in communication with the other departments in your organization helps to ensure that your work synergizes with the overall strategy and compliance requirements for your business.
Conclusion
Preparing for a compliance audit as a DBA is more than just a technical exercise. It requires a blend of robust technical processes, thorough documentation, and cross-departmental collaboration. Remember that achieving compliance is not a one-time task but an ongoing commitment, especially considering one of the most common audits, SOC2, is specifically how your security measures are being implemented over time. Be sure to regularly revisit and update your processes to ensure alignment as regulations and best practices change over time.
