Continuous SQL Server database auditing and reporting using the transaction log

SQL Server database transaction log files are continuously pumped with the transactional information and details on database changes by the SQL Server itself. Even though the information from the transaction log files and backups can be used as a solid resource for database auditing, SQL Server does not provide a solid solution to utilize transaction log files to their full potential nor does it offer any simple way to explore those transaction log files or analyze the information within in order to perform continuous SQL Server database auditing.

November 10, 2016

How to automate adding SQL auditing triggers to a new, or changed database

Maintaining a before-and-after audit trail for sensitive tables can be time consuming, especially with a database that is under continuous development, and particularly in teams that use continuous integration. Most table changes will break existing triggers and necessitate their update. The ability to automate the refactoring and re-creation of a trigger based auditing layer, to keep up with underlying database changes, and run this process unattended or as part of a continuous integration process can be a huge time saver.

September 28, 2016

SQL Server compliance requirements

To be in compliance means to be conforming to a specific set of regulations, standards, policies or laws. Many countries worldwide have specific laws or regulations which are imposed to companies and organizations which they have to follow in order to satisfy specific standards or rules – to be and remain in compliance. Organizations that use SQL Server databases to store customer data and other information abide to the compliance requirements. Additionally, even those organizations that are not subject to compliance regulations or laws need to fulfill their own organization policies, hence they tend to introduce their own compliance regulative.

May 12, 2016

How to automate trigger-based SQL Server auditing

More often than not, a top level requirement for all SQL Server DBAs is to have mechanisms to capture who changed what and when and to have stored audit information available for further investigation. In order to achieve this, database auditing needs to be set up using various mechanisms. One of those mechanisms is a trigger-based auditing.

April 27, 2016

Continuous auditing of SQL Server database using the transaction log

Challenge

Imagine if you could decrypt and pump all the wealth of transactional information and details contained in the SQL Server transaction log, in human readable format, and then store this as data directly in SQL Server tables, ensuring it wasn’t lost when the log was truncated, and such that data could be queried, reported on etc. using T-SQL.

March 7, 2016

SQL Server compliance auditing for Title 21 Code of Federal Regulations Part 11 requirements – Part 2

Part 1 of this article explains and provides instructions on how to properly set ApexSQL Audit in order to cover implementation of the Subpart B § 11.10 (a), § 11.10 (b) and § 11.10 (c) requirements of the Title 21 CFR Part 11 FDA’s compliance regulations. In this part, the rest of the Title 21 CFR Part 11 Subpart B will be presented and processed in form that will help ApexSQL Audit users easy setup of ApexSQL Audit for each specific requirement, with a short explanation of each requirement itself as well as what particularly ApexSQL Audit can cover and how

February 25, 2016

SQL Server compliance auditing for Title 21 Code of Federal Regulations Part 11 requirements – Part 1

Title 21 Code of Federal Regulations Part 11 (in the rest of the text it will be referred to as Title 21 CFR Part 11) is part of the Code of Federal Regulations established by the United States Food and Drug Administration (FDA) as a set of regulations on electronic records and electronic signatures (ERES). The CFR Part 11 specifically defines the standards that have to be imposed in order to consider electronic records and electronic signatures as trustworthy, reliable, and equivalent to paper records

February 25, 2016

SQL Server before and after auditing of DML/data changes

Before and after auditing tracks changes to data, showing the old and new values after each change. This data can be re-constructed to show an entire history of row changes and is important for forensic auditing in the case of malicious or inadvertent data changes.

February 25, 2016

How to implement compliance with the PCI DSS regulatory standard for SQL Server – Part 4

In part 1 and part 2 of this series information was provided on how to configure ApexSQL Audit to accomplish PCI requirements from 3 and up to 8, while in part 3 the addressing requirements 10.1, 10.2 and 10.3 of the PCI DSS 3.1 standard via ApexSQL Audit was explained

In this part, the rest of the PCI DSS 10-Track and monitor all access to network resources and cardholder requirements section will be described and as well as some requirements from section 12 that can be met using the ApexSQL Audit. This article is based on the latest PCI DSS 3.1 compliance regulation

January 22, 2016

How to implement compliance with the PCI DSS regulatory standard for SQL Server – Part 3

Part 1 and part 2 of this article described configuring ApexSQL Audit to meet PCI DSS 3.1 standard developed to ensure security of cardholders’ payments and data. In this part, the requirement sections 10-Track and monitor all access to network resources and cardholder will be described. For more details about differences between PCI DSS 2.0 and PCI DWW 3.0 and differences between PCI DSS 3.0 and PCI DSS 3.1, check the official PCI Security Standards Council LLC documents Summary of Changes from PCI DSS Version 2.0 to 3.0 and Summary of changes from PCI DSS Version 3.0 to 3.1

January 6, 2016

How to implement compliance with the PCI DSS regulatory standard for SQL Server – Part 1

The PCI DSS (Payment Card Industry Data Security Standard) is a multidimensional security standard designed as a set of technical and operational requirements to protect data of credit card holders. The PCI DSS applies to all entities that store, process or transmit cardholder data, including software developers of applications and devices manufacturers when used in those transactions

This standard originated in 2005 and was created by the PCI SSC (Payment Card Industry Security Standards Council) organization. The PCI SSC organization is founded by American Express, Discover Financial Services, JCB International, MasterCard Worldwide, and Visa, Inc., with a goal to improve security of payment account data via the PCI Security Standards

December 11, 2015

How to implement HIPAA regulatory standard for SQL Server – Part 2

In Part 1 of this article it was presented how to set up ApexSQL Audit to implement Administrative safeguards standards of the HIPAA regulatory (the 45 CFR Part 164, Subpart C – Security Standards for the Protection of Electronic Protected Health Information that is developed to accomplish protection of electronic protected health information (EPHI)). In this part, the rest of the Administrative safeguards will be presented, while in the Part 3 we will provide ApexSQL Audit settings which will allow you to fulfill the HIPAA’s Technical safeguards and Policies and procedures and documentation requirements sections

September 14, 2015

How to implement HIPAA regulatory standard for SQL Server – Part 1

The Health Insurance Portability and Accountability Act of 1996 (HIPAA) sets guidelines mandating the adoption of Federal privacy protections for health information of individuals which gives patients an array of rights with respect to that information. The HIPAA Privacy Rule ensures federal protections for individually identifiable health information and gives patients a range of rights with respect to that information. The Security Rule defines administrative, physical and technical safety measures to ensure the availability, confidentiality and integrity of electronic protected health information.

August 31, 2015

Before and after auditing in SQL Server

Simply archiving information to audit a database is one thing, but successfully reconstructing an audit history to provide meaningful forensic data is another. It is important to be able to see a full history of user changes, as well as to be able to reverse changes that may have been accidental or malicious.

Ideally, such value-added information can be obtained without requiring a prodigious amount of archived data or creating significant performance impact on audited servers.

In this article, we are going to present two different approaches and solutions to before and after auditing.

August 11, 2015

SQL Server database security auditing

The following auditing implementations are recommended on a database level as part of any database security auditing system:

  1. Schema level auditing:
    • DDL activity
    • Changes made to stored procedures and triggers
    • Changes to privileges, users, and security attributes
August 7, 2015

How to implement SOX compliance requirements for SQL Server – Part 1

Achieving SOX compliance requirements is the mandatory for all publicly traded companies. But when it comes to most IT teams, SOX compliance can be quite vague and confusing. SOX compliance is not written with technology mandate in mind, but rather a mandate for accounting, legal, and financial reporting. In the SOX Act there’s no reference can be found to anything specific related to IT. It is often said that SOX was “written by lawyers, for lawyers”

July 23, 2015