Security considerations for deploying SQLWATCH in production

I get a lot of emails with concerns that some of you have very strict security and locked-down environments which prevent you from installing SQLWATCH. At the same time, some of you would have preferred the central repository to connect directly to the remote instance and pull data out of it. Let’s discuss this problem.

Architectural principles

I have designed it with disparate environments in mind to address a couple of problems that other monitoring solutions introduce.

Decentralised

It means that, by design, it works in complete isolation, in disparate environments, demilitarized zones (DMZ), in the context of localhost, and does not require any external processes or network access.

It works well in multi-tenant environments without any risk of cross-contaminating different customers which could often be a case with the traditional, central-repository backed solution.

SQLWATCH must be deployed on each monitored instance.

Secure as any user database

SQLWATCH does not require any special permissions or treatment. It is just an application (user) database that stores performance data collected from the localhost. You should only grant modify access to configuration tables to your DBAs. Reporting data is exposed in views and therefore read-only access for non-DBAs can be further restricted to views only if required.

Local agent jobs collect data

Local agent jobs run data collection and retention and only accesses the local server. SQLWATCH will require access to other databases to collect metadata only – database and table sizes, indexes and few other bits. You can further limit access to these databases by running jobs under a proxy account that only gives SQLWATCH required access.

The optional central repository

The central repository will connect only to the remote SQLWATCH databases and pull data from existing tables. It will either need sp_datareader or GRANT SELECT on tables on the remote database. It does not require access to system objects or anything beyond the remote SQLWATCH database.

You can read more about permission at https://docs.sqlwatch.io

Actions engine can be a risk

You will be running actions via PowerShell module in SQL Agent. They could, in fact, run with elevated privileges or system access but they will only run code defined in the actions table. You will want to limit access to the action definition tables to sysadmins only so no unauthorised users can deploy potentially dangerous actions. If you are worried, you can disable the SQLWATCH-INTERNAL-ACTIONS job but you will lose notifications. Alternatively, you can modify the code so it only runs native T-SQL actions and not PowerShell based actions.

The truth is that if you allow remote access to remote instances you are not protected against the above anyway. Very likely there is already a “management” instance somewhere on your network that has access and can run Invoke-SqlCmd or sqlcmd against your remote instances.

Your security model is probably broken already

SQLWATCH does not require sysadmin access. In order to collect data, the account running SQLWATCH agent jobs must have some additional rights such as VIEW SERVER STATE to read Dynamic Management Views (DMVs), ALTER TRACE to read Extended Events, read-only access to msdb database to query agent job history, read-only access to master database to read sys.databases and availability groups related tables, access to execute PowerShell agent task to get disk utilisation.

Let’s take a step back. If you are happy to collect data remotely and happy to have these permissions granted to a remote login but not the local process, you probably want to re-assess your security model.

What about Personally Identifiable Information (PII)?

SQLWATCH collects “troublesome queries” such as long-running queries, blocking chains and queries with long waits. It also collects index statistics and index histograms.

Queries could and likely will contain PII in the content of where clauses for example select * from dbo.customers where NI_NUMBER = '123456798' or select * from dbo.patients where NHS_NUMBER = '123465798' or select * from dbo.address where street = 'avenue' and surname = 'smith' . Unless you decide to use the optional central repository or push data to Azure Log Monitor, the data never leaves local SQL Server. If you decide to push data to Azure Log Monitor, you will have full control over what you are sending. You can exclude potential PII data.

Index histograms will contain data in the index, and this could also contain PII data. For example, a primary key on the NHS_NUMBER will contain the actual numbers in the histogram. This affects all monitoring systems and you should discuss this with your legal and privacy teams.

Use data encryption

Encrypt your backups and consider Transparent Data Encryption (TDE) to increase security by protecting data at rest if required. Follow your company policy and discuss with your architecture, legal and privacy teams.

Encrypt procedures if you are paranoid about security

If you are in an extreme environment or simply paranoid, you can encrypt procedures to prevent viewing code and making changes directly in SQL. You would have to download the source and add WITH ENCRYPTION to each object. I personally do not think this will give you any additional security but some think otherwise. If you think someone could alter objects in the production and you are worried they could inject malicious code, SQLWATCH is your least worry.

Conclusion

SQLWATCH runs locally, does not require network access and does not require sysadmin access. Does not increase the attack surface area. If you opt-in to use the central repository, make sure you only grant select permissions on the remote tables. We will cover performance considerations in the next post. Stay safe and always deploy with the minimum required permissions.

This post was originally published on 4th May 2020.