SQL Server databases are under greater threat than ever. Cyberattacks are on the rise, and they’re becoming more sophisticated with each new generation. And these aren’t the only risks. Organizations must also guard against insider threats, whether carried out by disgruntled employees or those engaged in more nefarious activities. At the same time, organizations must contend with a growing number of compliance regulations, such as the General Data Protection Regulation (GDPR), Health Insurance Portability and Accountability Act (HIPAA), and the California Consumer Privacy Act (CCPA).

To meet today’s security and compliance challenges, SQL Server DBAs and other security professionals need to implement comprehensive protection plans that safeguard their data, databases, and the environments in which they operate. One of the most important steps they can take is to monitor their SQL Server instances, auditing events that can indicate a threat against sensitive data.

If you’re one of those people charged with monitoring a SQL Server environment, you should capture all metrics that can help reveal possible risks, whether failed logins, altered permissions, schema changes, or anything else. You might be tempted to audit every available metric just to play it safe, but such an approach comes with significant overhead. You need to focus on those events that are the most relevant to your circumstances, taking into account applicable regulations and the type of data being stored.

Although there is no one-size-fits-all solution when it comes to a monitoring strategy, there are specific areas of concern that you should consider when determining the best approach to auditing your SQL Server environments. In general, you should monitor SQL Server access controls, SQL Server objects and configurations, and data access behavior. Even though these are broad fields, they provide a good starting point for defining a monitoring plan based on your specific needs.

SQL Server access controls

SQL Server includes several internal mechanisms for authenticating users to a SQL Server instance and controlling their access to data, using a combination of security principals, securables, and permissions. Principals are individuals, groups, or processes that are granted access to a SQL Server instance at the server level or database level. Securables are the objects that make up the SQL Server environments, such as databases, schemas, tables, functions, logins, or roles. Permissions define the level of access that is granted or denied to principals on specific securables.

Monitoring SQL Server access should be an important part of any auditing strategy because it provides direct insight into who can view or modify sensitive data or whether there are any suspicious efforts to access the data. For example, monitoring failed login attempts can help you detect brute force attacks or other malicious behavior. But that is only one metric. You should also consider tracking the following access-related events:

• Security principals being added or dropped, whether at the server level or database level
• Members being added to or deleted from server or database roles
• Permissions being granted or denied to server, database, and schema securables

Changes in any of these three areas can indicate attempts at fraud or data threats. Once the changes are made, your databases might be vulnerable to data exposure, compliance violations, or disruptions in services. For example, a cybercriminal might gain access to a SQL Server instance and then modify the permissions in order to bulk-copy data out of a database or to run a malicious stored procedure. Access control changes can also indicate that someone inside your organization is being careless or is up to no good.

By monitoring access controls, you can identify a wide range of potential risks. For example, someone might have enabled the sa account, modified permissions on the SQL Server service account, or added members to the db_owner or sysadmin roles. Whether these actions are carried out intentionally or through sloppiness, they can all put a database at risk.

The decision on which access controls to monitor might be driven in good part by prevailing regulations, which often have strict guidelines on what information you need to track, but overall security concerns should also be part of your thinking, even if you’re not subject to a specific set of rules.

SQL Server objects and configurations

In addition to tracking access controls, you should also monitor changes to SQL Server objects at the server, database, and schema levels. In this way, you can audit changes that can put sensitive data at risk in ways not apparent through monitoring access controls. For example, a compromised login might go undetected, but it’s not until you see schema changes such as a dropped table or added function that you realize something is wrong.

As with other aspects of SQL Server monitoring, the exact approach you take to auditing SQL Server objects will depend on applicable regulations and the type of data in your database. However, there are certainly objects that stand out above the rest.

For example, you might find it useful to track activity related to the database objects themselves, such as when a database is created, altered, or dropped or whether it’s been stopped, started, attached, detached, gone offline, or brought back online. You might even want to know if a database’s size has changed and, if so, how much. The goal is to always be on top of changes to the databases so that you know which ones have changed, how they were changed, who changed them, and when the changes occurred.

That said, you need to monitor more than just the database objects to determine whether there’s been any tampering. For a clearer picture, you should also track changes at a more granular level, especially when it comes to objects that store sensitive data. For example, you might want to audit changes to a database’s schema objects so you can see which ones have been recently created, altered, or dropped or have been assigned a new owner. This type of auditing can also help you detect database drift, which comes with its own security concerns.

In addition to monitoring SQL Server objects, it can also be useful to audit changes to configuration settings. For example, a cybercriminal might have gained high-level access to a SQL Server instance and enabled CLR in order to launch a malicious assembly. If you were alerted to that change, you might have enough time to prevent that assembly from running and doing any damage.

Even if configuration changes aren’t the result of an attack, they’re still worth knowing about. For instance, a DBA might have enabled the xp_cmdshell extended stored procedures on a production server to pull down data and then forgot to disable it. Although it wasn’t done with malicious intent, it could still have serious consequences.

Monitoring SQL Server for configuration changes at the server and database levels can help detect threats that might otherwise be missed, but this approach is only effective if you actually catch the change. It doesn’t tell you what the configuration settings are supposed to be or whether any current settings are wrong. For this reason, you might also need to establish a base of agreed-upon settings that you can refer to for the absolute truth—the type of information often stored in a database configuration management archive (DCMA).

Data access behavior

Monitoring changes to SQL Server objects and configurations is extremely important, as is tracking access controls, but they might still not be enough to catch all threats or be in compliance with current regulations. You might also need to track access to the data itself, especially if regulations require it. For example, you might have to audit all SELECT access to each table that contains sensitive data. In this way, you can establish an audit trail of who accessed what data and when that access occurred, details you must often provide during a compliance audit.

You should also consider monitoring other types of events related to data access. One example would be to track whether anyone has used the BCP utility to bulk copy data out of a table. Another example would be to determine whether someone has issued a BACKUP statement, which could indicate the presence of an illegal database copy. Any metrics that can point you to potential data compromise are worth serious consideration.

In some cases, you might need to track data modifications instead of or in addition to data access. Regulations such as HIPAA and the Sarbanes-Oxley Act (SOX) require that you protect sensitive data against wrongful deletions or modifications, whether accidental or intentional. If either of these regulations applies to you, you’ll need to track all data modifications and deletions in order to demonstrate compliance. To do this effectively, however, you should audit database-level object changes, as well as INSERT, UPDATE, and DELETE activity. You should also be able to determine how the data has changed so you can compare before and after values.

In addition to monitoring data access and modifications, it can also be important to track SQL errors, which can point to SQL injection attacks or other threats. Would-be hackers often issue multiple SQL statements against a database to deliberately trigger SQL errors in an effort to navigate the schema. For example, a hacker attempting to carry out injection attacks might try to access invalid objects or use incorrect SQL syntax to glean schema information from the error messages.

Other monitoring considerations

You can go a long way in protecting your SQL Server environment by monitoring access controls, objects and configurations, and data access behavior. But a complete monitoring solution must also take into account factors such as the following:

• Applications that access data in SQL Server databases should be monitored for unusual usage patterns or business processes or for any other suspicious activity.
• The Windows Server computer that hosts SQL Server should be monitored for suspicious activity. Cybercriminals might be able to gain access through server logins and then change server settings or compromise SQL Server database files.
• In addition to monitoring the SQL Server environment, you should also audit the monitoring tools and processes themselves. You must be able to ensure that no one has tampered with the auditing process and that your monitoring solution is as secure as the SQL Server data.
• Effective monitoring often includes the ability to detect unusual usage patterns in how the SQL Server instance is managed and how data is accessed and modified. For this, you might need to create baselines that represent acceptable patterns that can then be compared to the audited events.

Another important factor in monitoring SQL Server is to ensure you have the right tools in place to help you audit selected metrics as efficiently as possible. You should also be able to track trends and to be alerted to behavior that represents an immediate threat. SQL Server Audit provides a good tool for auditing events, but it comes with limitations. One of the biggest is that it’s built into SQL Server. An attacker that gains access to the platform could disable the auditing features and other security protections. With a third-party tool such as Redgate’s SQL Monitor, you get a system separate from SQL Server, along with advanced features that help simplify the monitoring process.

Implementing a SQL Server monitoring strategy is no small feat. It requires careful planning and ongoing diligence to ensure that you’re protecting your sensitive data and complying with applicable regulations. Your monitoring solution should meet your specific requirements based on your current circumstances, with the understanding that those circumstances can change. SQL Server monitoring should also be implemented in concert with other security measures to help guarantee that your systems and data are protected at all times. In today’s climate, data security must be a top priority, and monitoring SQL Server is one of the most important steps you take, but you have to do it right to protect your data to its fullest.

If you liked this article, you might also like SQL Server authentication methods, logins, and database users.