Solution Brief : SQL Compliance Manager

SQL Server Audit

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.

Database Auditing

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.

Database Compliance

Well, this depends on the organization, and the data that is stored in your databases. Some data require stricter guidelines, while other data may not be an issue when comprised or lost. Here is a starting list of some things to consider when implementing an auditing solution:

  • Regulations - this is the driver for putting an auditing solution in place. Some of these regulations include HIPAA, SOX, GDPR, PCI DSS, and FERPA. You already heard about this from others in your organization if you need to implement a solution for one of these items. Now it is up to you as a data professional to figure out what needs to be done and how it needs to be done. This can consume a significant amount of your time.

  • Failed login attempts
  • Identify creation or deletion of database objects
  • Know when server configuration changes occur
  • Successful login attempts: When, who, and how
  • Data changes: Before and after values
  • Knowledge of who viewed data
  • Database backups that occur outside regular backup schedules
  • Someone disabling triggers
  • Creation of new logins
  • Security access changes

SQL Server Auditing

As you can see, the list is pretty long and not is this a complete list. So how do you capture the data to either alert you right away of a potential issue or provide the forensics you need to find and recreate a potential data breach? Well, the first approach is always to build your own solution. There is a specific need and technique you want to implement. The downside is this may cover one or maybe a couple of scenarios, but what about the things you did not think of? How do you handle those situations?

This is where SQL Compliance Manager comes into play. SQL Compliance Manager is a tool built with these things in mind. There is a team of people at Idera that are polling multiple customers to determine what they need and staying current with regulations that need to be followed.

As mentioned earlier, the driving factor for an auditing solution is to comply with some regulation. Well, SQL Compliance Manager already has this figured out and has templates you can deploy for the following regulations:

As you can see from the list above, someone has already taken the time and effort to build the collection methods needed to meet regulation guidelines when using Microsoft SQL Server. This also helps you streamline the implementation without the need to study the guidelines and then figure out how to implement various techniques for data collection, analysis, and reporting.

SQL Compliance Manager is the obvious choice if you need to implement an auditing solution to meet one of these guidelines. Do not waste your time and effort building something that already exists.

In the previous sections, we described the obvious reason you should use SQL Compliance Manager. So what are the reasons you would use SQL Compliance Manager if you did not need to follow a regulation guideline? Well, there are still plenty of things that could go wrong. You may not need to pay a regulatory fine, but what if a data breach occurs causing application downtime, or leaked data causes current or future problems? Here are some reasons an auditing solution is important.

Database Security

Microsoft SQL Server can become pretty complex when dealing with security. First, you need to think about the server installation and the service accounts used to run Microsoft SQL Server. Next, you can use two methods of access to Microsoft SQL Server: SQL logins and Windows Authentication. Then, within Microsoft SQL Server, you have server roles and individual permissions that you can grant to objects. Then, at the database level, you have database roles and also the ability to grant permissions on individual objects. As you can see, there are several levels that can you can change. This creates an opportunity for access that should not be available to a user and the ability to change and elevate permissions to access certain data for a moment in time. If you are not tracking these access changes, you may never know who has access at any point in time.

SQL Compliance Manager

Here is a list of some things SQL Compliance Manager can audit.

SQL Server Audit Log

Another area to think about is when a data change causes issues with the database or applications. Are you able to track down before and after values? Can you figure out when a change occurred and what user or application made the change? These could be critical pieces of information for when something goes wrong or worse yet a malicious actor gets access to your systems and causes harm to your data.

SQL Compliance Manager will allow you to set up measures to collect the data and generate reports, whether you need to follow and implement an industry standard regulation or you need internal auditing. As mentioned, you can implement one of several home-grown methods to capture the data you think you need, but what do you do when the data you need did not collect? Therefore, it makes sense to use all the auditing features of SQL Compliance Manager to meet your auditing goals.

Audit SQL Server Activity

Most implementations for auditing collect before and after data so you can see what people changed and when. What if you have sensitive data, such as medical records or confidential employee data? How do you know that someone is not looking at this data that requires safeguarding and should be secure and not accessible to people that should not have access? This is another area where you can configure SQL Compliance Manager to capture data about what people viewed and who viewed the data. Instead of capturing every select statement that occurred, you can customize this to capture only when people access data you determined is a potential issue.

Manage SQL Server Data

Collecting data on your data and all the changes that occur could become quite overwhelming at some point. Over time, this data store can become quite large and require the need to figure out ways to prune and archive older data that may not be of importance anymore. If you build your own solution, you would need to build this into your plans, but often people do not think of future needs like this and take on a life of their own. The great thing about SQL Compliance Manager is that the vendor already built this feature into the solution, so you can keep the collected data to a manageable size with just a few configuration settings.

SQL Server Notifications and Alerts

Being notified right away when a potential threat occurs or a change takes place that is not allowed or an unexpected change occurs is very important. This allows you to address the issue as soon as possible instead of having to wait to review reports to determine if an event occurred that should not happen. SQL Compliance Manager allows you to build alerts for various activities that take place, as shown below. You then also can configure an action to take place when the event occurs.

SQL Server Reporting

The result for any auditing solution is to use the data to answer your auditing questions. It is great that you have data that can answer these questions, but it often takes time to build queries and reports that could satisfy internal, customer, and regulatory compliance needs. SQL Compliance Manager offers many built-in reports:

SQL Server Compliance

As a database professional, you deal with many competing priorities. When tasked with a request, it would make sense to take the time to do research to determine if someone already created what is being requested. It also helps to determine how you can take advantage of what exists versus starting at the beginning. With auditing, this can be a very complex topic and include many nuances and requirements that may exist on the first day or someone requests at a future date. Instead of building one-off solutions to meet an individual need, look to SQL Compliance Manager as your solution to allow you to answer your auditing questions from the first day.