SOX survival kit for the SQL Server DBA

The Sarbanes–Oxley Act of 2002, Sarbanes–Oxley, Sarbox, or SOX is a US federal law “written by lawyers for lawyers”. It’s a regulation created to improve the quality and integrity of financial reporting, and ensure the financial and business information is factual and accurate.

October 4, 2013

Meet GLBA compliance requirements for SQL Server


What is GLBA

The Gramm – Leach – Bliley Act (GLBA) was enacted in 1999. Its purpose is to protect consumer financial privacy. In order to meet GLBA compliance requirements, the financial organizations must inform their customers about the company’s information sharing and privacy practices. Customers must be given and explained their right to opt out (to say “no”) – if they don’t want their financial information shared with certain third parties

October 2, 2013

Meeting PCI compliance requirements with SQL Server

What is PCI?

The Payment Card Industry Data Security Standard (PCI DSS, or just PCI) is an information security standard that protects cardholder and card payment information. The PCI DSS general requirements are designed to ensure a secure, monitored network, protect cardholder and transaction data, provide vulnerability management, strong access control measures, and maintain an information security policy

September 28, 2013

Meet SQL Server auditing requirements of Sarbanes-Oxley (SOX)


What is SOX

The Sarbanes–Oxley Act of 2002, Sarbanes–Oxley, Sarbox, or SOX is a regulation created to improve the quality and integrity of financial reporting. It addresses audits, financial reporting and disclosure, conflicts of interest, and corporate governance, so financial and business information is factual and accurate. Its purpose is to avoid accounting scandals like the ones in 1990s stock market.

September 25, 2013

Open LDF file and view LDF file content

Every SQL Server database is mapped over a set of operating-system files. These files store data and log information. Individual files are used only by one database, and data and log information are never mixed in the same file. While data is stored in an MDF file, all transactions and the SQL Server database modifications made by each transaction are stored in an LDF file – a transaction log file which is an essential component of the database. Conceptually, the log file is a string of log records. Physically, the log records are stored in one or the set of physical LDF files that implement the transaction log

September 18, 2013

How to meet requirements of HIPAA compliance as a part of a SQL Server audit


What is HIPAA

The Health Insurance Portability and Accountability Act of 1996 (HIPAA) is a security act that sets national standards for security of electronic protected health information and protects the privacy of patient health information. In simple words, HIPAA ensures confidentiality, integrity, and availability of all electronic Protected Health Information (PHI)

September 13, 2013

Track SQL Server database security changes

Configuring a safe and secure environment for your SQL Server instance is a complex task. SQL Server security must be set on the SQL Server instance, operating system, firewall, antivirus program, etc. But failing to set up security properly can bring a lot of headaches and even irreversible damage

September 11, 2013

Tracking DDL changes in SQL Server – the ‘Trouble with Triggers’

Tracking changes is an essential SQL Server security task. Besides data change history, which includes DML operations (e.g. INSERT, UPDATE, and DELETE), tracking DDL changes in SQL Server, changes that affect database objects, is of high importance too. In this regard, various techniques can be used as a schema change auditing solution. One of the most common method are DDL Triggers

August 28, 2013

4 techniques for recovering lost tables due to DROP Table operation

An accidentally dropped table may be recovered in several ways. The choice of technique depends on the resources available for the recovery. The first choice is usually a database backup. But, even if the one doesn’t exist, the dropped table may still be recovered (in some cases even quicker than with a backup).

August 15, 2013

How to recover views, stored procedures, functions, and triggers

Regardless of precautions taken to protect your SQL Server, accidents may still occur, causing serious consequences, such as data and objects loss. We will now analyze two possible ways to recover SQL objects – views, stored procedures, functions, and triggers, lost to accidental DROP statement use.

August 5, 2013

Verifying SQL database backups automatically

Backups are the starting point for any serious disaster recovery strategy. Creating SQL database backups on a regular basis is just the first step. Equally important is to make sure they are also reliable and restorable. This is the only way to avoid unpleasant surprises in case of a disaster

August 2, 2013

Multiple SQL Server data recovery options

In the past two weeks we had some interesting Solution center articles describing Microsoft SQL Server topics – each described as the problem (or the challenge) users may encounter with SQL Server data recovery

Any of recovery options using Microsoft’s SQL Server Management Studio requires the availability of a full database backup. And, the backup must be restored in order to be used as a recovery data source

July 5, 2013

SQL Server database auditing techniques

SQL Server database auditing is not used only to address auditing requirements for compliance. It has become necessary for the analysis of database actions, troubleshooting problems, investigating the suspicious and malicious activity. It can also help preventing users from inappropriate actions – as if you had a CCTV system on your databases

There are several SQL Server auditing techniques:

June 28, 2013

Auditing triggers in SQL Server databases

One of the essential SQL Server security topics is finding out who did something, what and when. Being able to provide a history of data for various auditing purposes, some of which are enforced by US laws through compliance regulations, is a serious task for any DBA. Since business policies or the compliance regulations require the auditing of data changes within a database, various techniques adapting to the requirements are used to perform database auditing. The common one is using auditing triggers in SQL Server databases.

June 26, 2013

Restore a database to a point in time – part 1

In various scenarios, a SQL Server restore a database to a point in time might be required. Typically, the following situations require this type of recovery:

  • A data corruption occurred by a user malicious action. For example, a user or DBA might delete or update the contents of one or more tables by mistake, drop database objects that are still required during an update to an application, or perform a large batch update process that fails
  • A database upgrading process goes awry or a database upgrade script fails
  • A complete database recovery after a media failure cannot be performed, because the required transaction log and database backups are missing
June 20, 2013

Capturing who saw what in SQL Server

It’s important sometimes to know and have evidence of who-saw-what on specific SQL Server instances and databases. This can be a requirement you have to provide (e.g. to comply with HIPAA regulations) and there are several ways to ensure the capture of that information

In order to capture who-saw-what in SQL Server, it is important to know what was executed on the server. Even though ApexSQL Log (a transaction log reader) and ApexSQL Trigger (trigger-based auditing tool) provide partial information, in order to get the full details on who saw what, ApexSQL Audit is the perfect tool for the job.

June 11, 2013

Recover deleted SQL data from a backup or from online database files

Recovering DELETED records is something we would all like to avoid, but no matter how careful we are, it can come to that. Having regular full database and transaction log backups is a necessary practice and a great advantage in this situation, but even then, bringing back the deleted records might not be easy

June 4, 2013

Read a SQL Server transaction log

SQL Server transaction logs contain records describing changes made to a database. They store enough information to recover the database to a specific point in time, to replay or undo a change. But, how to see what’s in them, find a specific transaction, see what has happened and revert the changes such as recovering accidentally deleted records

May 27, 2013