While numerous native auditing methods are available for SQL Server, none of them provides an out-of-the-box feature to generate an alert when a specific SQL Server event is detected. We will look to see how to come close with native solutions and also an out of the box solution, ApexSQL Audit
May 29, 2015How to ensure continuous auditing of SQL Server with zero audited data loss
An optimal continuous SQL Server auditing approach must include:
- Continuous auditing
- Real time data collection
- Ability to generate meaningful reports
- Alerting on unwanted activities
- Tamper proof store of audit data
In many cases, the primary requirement that must be fulfilled is that auditing must be performed with zero auditing data lost.
April 28, 2015SQL Server database trigger auditing – How to customize data tracking and reporting – Part 2
Similarly as Watches, the Lookups feature allows the ApexSQL Trigger report to show a meaningful value from a related SQL Server database table, along with the value of the field that was audited. A more human-readable value can be shown in the audit reports, for example “111” (customer’s ID number) versus “John Smithson” (customer’s actual name).
March 12, 2015SQL Server database trigger auditing – How to customize data tracking and reporting – Part 1
ApexSQL Trigger uses SQL Server database triggers for capturing and storing changes that occur on the audited data and schema. More specifically, the DML database triggers are used for capturing INSERT, UPDATE or DELETE statements, and DDL trigger is used for capturing CREATE, ALTER or DROP statements. Now, since there are various types of columns in each database table, reading the audit report is often not very user-friendly and this especially applies to foreign key values in different tables.
March 12, 2015Tracking SQL Server object usage
There is a number of object types in SQL Server database system. Each has its own purpose and role in proper data management. However, sometimes it is required to check how some of them are used.
February 19, 2014Security and compliance in SQL Server
The primary purpose of any database management system is to store and provide accurate information as requested by other software clients. Security of the database system and the information it keeps is another crucial component. There are many aspects of SQL Server security configuration, such as authentication, server and database roles, ownership, or Common Language Runtime (CLR) integration. However, in this article, we’ll focus on those that are related to (and common for) most of compliance regulations.
February 7, 2014Auditing security changes in SQL Server
When it comes to SQL Server security, it’s important to note that there are server and database security levels. All work done by a user is performed on a database, but in order to access the database and do the work, the user first needs to access the server, and afterwards the database – the server security level affects the database security level
February 6, 2014SQL Server database security and compliance
When discussing SQL Server security, one of the most important terms is principal. Principals are SQL Server entities, arranged in a hierarchy, which can request specific SQL Server resources. There are various principals in SQL Server, and in this article, we’ll focus on a database user entity. Unlike SQL Server login entities, which are used for accessing a SQL Server instance (a server-level principal), a database user entity (a database-level principal) is used for defining access to a particular database that belongs to the SQL Server instance
February 3, 2014Auditing SELECT statements on SQL Server
Although SELECT statements are not destructive by nature, nor they can change either data or schema, there are several cases that require their auditing on SQL Server. Executed SELECT statements can indicate various current or potential issues, and this is the reason why it’s important to know who-saw-what and when
January 23, 2014HIPAA Compliance for SQL Server DBAs
Health Insurance Portability and Accountability Act (HIPAA) is a security act that sets standards for ensuring security, privacy, confidentiality, integrity, and availability of patient health information – electronic Protected Health Information (PHI).
January 16, 2014FERPA Compliance for SQL Server DBAs
The Family Educational Rights and Privacy Act (FERPA) [1] is a Federal law created to protect the privacy of student education records. It establishes the rights of parents and currently enrolled eligible students to review their education records, request changes of misleading or inaccurate data, and control the record disclosure
December 24, 2013Basel II Compliance for SQL Server DBAs
The Basel Capital Accord Basel II [1] is an improvement of recommendations for banking standards on international scale, based on three intertwined pillars, issued by the Basel Committee on Banking Supervision. It’s an extension the Basel I Accord [1], and it sets risk and management requirements while introducing a data management approach.
December 5, 2013GLBA Compliance for SQL Server DBAs
The Gramm – Leach – Bliley Act (GLBA) is a security and privacy regulations standard created with a purpose to protect consumer financial privacy. To meet GLBA compliance requirements customers must be informed by the financial organizations about the organization’s information privacy and sharing practices. The customers must be provided with explanations about their rights and unambiguous option to deny their financial information to be shared with any third parties.
December 4, 2013Auditing SQL Server data changes – the centralized solution
In the previous part of the Methods for auditing SQL Server data changes articles series, How to analyze and read SQL Server Audit information, we described several native SQL Server auditing features – Change Tracking, Change Data Capture and Audit. We described their unique and shared characteristics, how they store captured information, how to provide the information, and explained the advantages and disadvantages of each feature.
November 27, 2013How to analyze and read SQL Server Audit information
In the previous parts of the SQL Server auditing methods series, we described the SQL Server Audit feature, its characteristics, components, how to configure and use it
November 24, 2013PCI Compliance for SQL Server DBAs
The Payment Card Industry Data Security Standard (PCI DSS, or just PCI) is a security regulations standard related to payment cardholder and card payment information security. Any company or institution that uses and stores such information is obligated to comply with the PCI standard.
November 22, 2013How to set up and use SQL Server Audit
In the previous part of the SQL Server auditing methods series, SQL Server Audit feature – Introduction, we described main features of the SQL Server Auditfeature – its main characteristics, what events it can audit and where the audit information is stored. We also explained two levels of auditing – the database-level and server-level, and three components necessary for using the feature – the SQL Server audit object, database audit specification, and server audit specification
November 17, 2013SQL Server Audit feature – Introduction
In the previous parts of this series, we described two SQL Server auditing features – Change Tracking and Change Data Capture. We showed their characteristics, how to enable them, how to read the results, and listed their advantages and disadvantages
November 14, 2013How to analyze and read Change Data Capture (CDC) records
In the previous article, How to enable and use SQL Server Change Data Capture, we described the main features of SQL Server Change Data Capture and showed how to set it up. Now, we will analyze the records stored in change tables and describe the methods to read them
November 12, 2013Using SQL Server traces for SQL Server auditing – Part 3 – The out-of-the-box solution
In the previous part of the Using SQL Server traces for SQL Server auditing series, we described SQL Server traces technology, what it provides in terms of SQL Server auditing, and how it can be utilized via SQL Server Profiler and the native SQL Server fn_trace_gettable system function.
November 8, 2013