Product articles SQL Monitor SQL Server Security Monitoring
Monitoring SQL Server Security:…

18 February 2019

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

18 February 2019

Monitoring SQL Server Security: What’s Required?

If we know how a database is likely to be attacked, we can arrange our lines of defense, and install the monitors required to detect any attempts. However, some types of attack are difficult to imagine, so we also need our monitoring tool to be adaptable, so that it can collect a more diffuse collection of metrics, and then help us determine the reason for any sudden change in the patterns of access .

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

A characteristic theme of all the current legislation on the responsible curation of data is that there should be a system for continuously monitoring and analyzing database activity, in real-time. This Database Activity Monitoring (DAM) system must detect anyone, inside or outside the organization, accessing or tampering with information in any database-driven applications that handle personal, financial, HR, or other business data.

The monitoring system will collect metrics such as syntax errors, denials of access, permission changes and unusual access patterns, and then aggregate them, and report on them. Although this sort of database security monitoring is part of the broader activity of database auditing, and real-time protection, it is quite separate from, and independent of, any defensive techniques to prevent attack. The aim is to be immediately aware if this layer of defense has been avoided, sidestepped or penetrated, and to detect any unusual activities on the server, including database read and update activity, server or database settings, or permission changes.

Database activity monitoring: requirements

A DAM system will need to ensure that it can detect and log any user activity that violates the security policies for the data, or that indicates unusual or suspicious user activity.

This system must operate as independently as possible of the RDBMS, to prevent anyone who is attempting to tamper with the system from also disabling the audit and control system. For example, any attacker who gains administration rights can then use it to disable triggers and constraints and stop both SQL Server Audit and SQL Server Agent. We need an independent process, such as a custom monitor, that regularly checks that the audit and control system that you are using is still working.

The broader DAM system must provide:

  • Data privacy – by ensuring that only authorized applications and users can view sensitive data. With a good access control system in place that matches the users to their requirements for data access, It is then wise to log any failed attempts to access data. The DBA must aim to be able to report a list of every person who has read access to sensitive data.
  • Database authorization – by checking the activities of privileged ‘superusers’ and reporting on anomalous activities, and by making sure that all administrative changes to access control and authorization (DCL) and metadata (DDL) can be traced to an individual user, where possible, rather than a shared SQL Server admin login.
  • Data governance – by checking that no changes are made to critical database metadata without the relevant change control, and by checking that no unauthorized changes are being made to data values.
  • End-user accountability – ensuring that unauthorized or suspicious application-based user activity can be tracked and audited with enough veracity and detail to be usable as legal evidence. It must be able to associate specific database transactions with individual application end users.
  • Fraud detection – monitoring and recording unusual patterns of usage by authorized users, and particularly changes to historic data, or business processes, made outside the normal procedures.
  • Cyber-attack alerts – detect an intrusion based on a divergence from normal patterns of activity, by first establishing a baseline of normal user behavior, the background level of database errors, patterns of access, denials of permission, and so on. The system should also be able to detect intra-database attacks and back-door attacks, in real time
  • Configuration auditing – detecting changes in database and server configuration that aren’t covered by change management procedures, to comply with audits required by the U.S. Sarbanes-Oxley Act (SOX)
  • A system that also works with Virtual and Cloud environments – many systems rely on in-house hosted databases, whereas cloud and virtualized environments are just as much in need of monitoring database activity

What is needed in a SQL monitoring tool?

All organizations that handle data do so in the face of the wide-ranging requirements, and growing legislation, such as embodied by the GDPR, the Payment Card Industry Data Security Standard (PCI DSS), the Health Insurance Portability and Accountability Act (HIPAA), the Sarbanes-Oxley Act (SOX), U.S. government regulations such as NIST 800-53, and many others.

Therefore, any monitoring tool that aspires to compliance must allow for a wide range of custom monitoring, to allow monitoring of the processes of a database application, as well as the database system. It must be able to monitor errors, sessions and individual SQL Statements that access database objects that allow access to sensitive information, as well as changes to database objects. It must also be able to monitor database server settings and database settings to detect changes.

However, of course, all monitoring activity comes with a technical cost in terms of CPU, memory, potential attack-surface and network traffic. The more you require of your monitoring software, the greater that cost. Monitoring must be as lightweight as possible and must be careful to avoid locking or blocking. For SQL Server, the use of Extended Events is an obvious way of keeping this overhead low. The two principles of filtering-at-source and being selective about the metrics collected are always important.

Any security monitoring system, itself, presents a potential security risk because of the sensitive nature of what it must check. The monitoring system must have had rigorous security checking and use the best of current security practices. Ideally, the database and monitoring system should engage in mutual security checking. The storage of the monitoring system should be well tied-down for security.

How to monitor for security

The great value in a monitoring tool, such as SQL Monitor, is in ‘baselining’ any metric you provide and allowing you to set alerts when there is a substantial variance from this baseline. In SQL Monitor, we install each custom metric, which is simply the integer value returned by a SQL Query, separately. This gives a wide range of possibilities for alerting. More advanced reporting and checking is better done by a separate application, because requirements vary so widely.

  • For tracking the business processes of an application – there is no industry standard, so SQL monitor relies on an interface with the application, via the database. This would need a custom monitor, or set of them, that can produce graphs that plot current activity against a baseline, and mange alerts.
  • For detecting intrusions from outside the organization – the easiest course is to monitor the characteristic errors found in both in-band and blind SQL Injection, to monitor all denials of access to database objects, and to check any attempts to use the system stored procedures that are used by hackers to extract data.
  • For detecting changes in server and database settings – the monitoring tool should monitor for specific informational messages relating to changes to the server and database configuration. However, if we only monitor changes, it is easy to miss the fact that a configuration has drifted from the value at the time of release. The agreed settings should be stored within a DCMA (Database Configuration Management Archive). There are many ways of using this data, but we can, for example, load these agreed settings into an extended property that the monitor can compare them to the current, ‘live’ settings
  • For checking on the session-level activity of users – SQL Monitor, as shipped, can only show general trends and manage alerts. The only practical way of checking on session-level user activity is to use Extended Events and a custom monitor. We could supplement this with a database-level process that monitors for any unusual chain of processes, to use a custom monitor to alert the administrator to check it for validity.
  • For changes to permissions and users – Ideally, all users would be administered via an LDAP or Domain, but there should be checks on the DCL within the database, by comparing with the access control code for the release, to ensure that no unauthorized changes have happened, such as to add a new user or role, or to change access rights to existing users. It is easy to use a range of Extended Events to audit all attempts, successful or not, to make changes to permissions and logins.
  • For database drift – The monitoring system should check for any changes to the schema of the database that have been made without any authorization or change-control. It should alert the administrator whenever a change happens, and record the nature of the change, who did it, when, and to which database object.

Security monitoring of a database application is far more effective if the database has an interface that reports on security issues within the application. This has only to report a numeric summary of the state for each security metric, such as the number of anomalous user interactions, or number of failed front-end injections, or where a single user makes many requests for lists of data. It must allow a DBA to query for details. This relies on the application having an effective logging system for user behavior, within the database itself. With a system like this, SQL Monitor can be used along with a custom metric, to do the graphing and alerting.

Promoting a culture of alertness

Whenever I’ve been asked to assess the security of a website or application, I’ve found that I can usually predict what I’ll find by first assessing the attitude of a team to designing for security, and to security monitoring. On one occasion, a successful penetration of the network happened whilst I was being briefed by the team leader on their splendid security efforts!

Over the years, I’ve found that developers and admins have needed encouragement to develop an interest in defensive security design, least-privilege access, and fine-grained access control.

Database security must be tested in the same way that one tests the software processes, by using penetration tests as part of the test regime. The best strategy for focusing effort on security defenses, and monitoring, is to make the security requirements an intrinsic part of the documented user requirements. With the arrival of GDPR, it is now far easier to persuade the business to do this.


In reading through the requirements of the various regulations that are imposed on the processing and holding of data, you’ll find a broad range of monitoring requirements. Where one is certain of the sort of tampering, penetration or copying of data that is being attempted, or is likely to be attempted, then it is relatively easy to detect it. The odds are stacked against the attacker with the well-known attack vectors, such as SQL Injection.

If the type of likely attack is unknown and difficult to imagine, then then we need to monitor a more diffuse collection of metrics and determine the reason for all sudden changes in the patterns of access, even if often the cause is innocuous, such as a popular football or baseball game.

With any system that is concerned with security, the principle is to flag up all changes that do not correspond with agreed changes that are logged in a source control system as being authorized. Otherwise it is difficult to tell whether a change is intentional or malicious. For this sort of monitoring, nothing beats a visual representation of database metrics that supports the intuition of the database professional.

You may also like