Protect and audit your SQL Servers

Data is vital for a business to run day-to-day operations and as a strategic advantage over competitors. Aside from the need to collect and process data, important business decisions are based on data and therefore data needs to be safeguarded and treated as one of the most valuable assets of a company.

Some issues with data are incompleteness, inaccuracies, update mistakes, malicious activities (such as data destruction or stealing data and using it to cause harm) and more. Every company needs to put in place mechanisms to safeguard their data and databases. This can be physical security, network security, database security, application security, etc. By creating opportunities for malicious activities, or worse, not knowing they exist, this creates a potential situation where valuable data becomes worthless or leaked data could cause reputation harm and legal consequences.

Most organizations follow methods and procedures to ensure databases are secure, but the problem is that you do not know what you do not know. It can help to build an environment to create and collect metadata. It provides the ability to catch harmful activity right away or at least provides the mechanism to go back in time to identify what happened, when it occurred, and how it occurred.

So how do you audit your database systems and where can you collect data from? There are several methods for collecting data one can use for analysis, including:

• System logs, like operating system and Microsoft SQL Server
• Build custom logging tables in the database
• Build custom applications for logging
• Use database snapshots to compare before and after values
• Apply Microsoft SQL Server policies
• Log failed login attempts
• Build tight security around database objects and data access
• Use change data capture to get old and new values
• Create triggers on tables to capture data changes

These options work, but as you read through the list, you can observe that there is not one option that will do everything. The option you pick may handle a specific area, but unless you use these methods or some combination, you can never capture the needed data to answer the questions of who, what, when and where.