SQL Server Security Auditing: Checking Who Has Access to What

IT professionals are typically in agreement when it comes to the need to secure systems to ensure data is not accessed by those who are not authorised. The security of a SQL Server is usually implemented during the initial setup and deployment to adhere to company policy, and more often than not, left as is and unreviewed.

Why Implement Recurring SQL Server Security Audits?

Outdated Access Rights and Permissions

Recurring periodic security audits for SQL Server environments should be adopted as changes to access rights and permissions can occur frequently. For example, a contractor may have required access for a project. After the contractor leaves it is easy to forget about removing their access and so their account may still exist, potentially giving them full admin rights to your SQL Server. Likewise, when people leave an organisation it is common that their accounts are disabled or removed but their login details may still exist on the SQL Server and they may still own objects in SQL Server databases that they created or altered (e.g. SQL jobs, tables or even databases).

Often, we see that an individual will have access to a SQL Server and overtime, will require more rights to do the job. This is often assigned as a result of a support request and may not be questioned by the DBA as they are trying to solve the issue at hand – in other words they may not realise there was a violation of security policy.

Review SQL Server Environment Configuration

It is also worth reviewing the setup of the SQL Server – did we configure it properly and to the business requirements but use the smallest ‘footprint’ we could? Do we have any potential holes that can be exploited, not just from outside our organisation but internally as well by employees accessing sensitive data – either on purpose or by accident. This Guardicore article discusses an exploitation of SQL Servers using known ports, known usernames and common passwords to access SQL Servers and turn their hosts into crypto-miners, vailidating the need to reduce the footprint and eliminate know attack vectors and exploits.

With changes often needed around security to ensure people have access, how does an organisation ensure who has what level of access? How can you identify potential security threats? The answer is to review or audit the security of your SQL Server environment on a regular basis.

Here’s our suggestions for what to consider including in a SQL security audit:

Service Pack Level and Cumulative Update Level – Microsoft regularly release patches to address security issues. It is generally recommended to be patched to the latest service pack or Cumulative Update for SQL Server. Spectre/Meldown was a major security vulnerability fixed by SQL Cumulative Updates.
Auditing of Failed Logins to the SQL Server should be enabled – this will help detect if we are seeing a ‘brute force attack’ if we see the same login failing excessively.
Is SQL Server Listening on TCP Port 1433? This is the default port and is well known – ideally SQL Server should be configured for a non-standard TCP port number
Which accounts are in the SQL server sysadmin role? This role gives a user full rights to the whole SQL Server.
Which users have database owner rights to databases? In the context of the database access this means these users can do anything to the database i.e. create/alter/drop tables, views, indexes and data. They can even drop the database they are the database owner.
Databases containing highly sensitive or business critical data should be encrypted regardless of the state:
- At rest – We recommend using Transparent Data Encryption (TDE)
- In transit – We recommend enabling SSL encrypted connections
- Off host backups – If you’re using TDE for encrypting your data rest, you off host backups will encrypted by default. If you’re not using TDE, you can encrypt SQL Server native backups using a certificate.
The service account running for SQL Server should be provisioned with the lowest required permissions.
The service account should be provisioned as a local account unless it requires access beyond the network boundary of SQL Server host.
The XP_CMDSHELL, OLE AUTOMATION, OPENROWSET, OPENDATASET features should be disabled. These features can open access holes into SQL Server.
The SQL Server Browser service should be disabled. Common practice is to disable any unused services and minimise the ‘footprint’ of the SQL Server. Also, any installed services that are not needed should be removed e.g. SSAS installed and running but never used or configured. Also, enable the Hide Instance option to protect against broadcasting the instance in the event of accidental starting of the browser service.
Secure Database backup folder – if backups are located on local or network storage – who has access to these locations? The account running the SQL Server and SQL Server Agent service will require full access but who else could copy files from this location. Likewise, if backing up to Azure BLOB storage – which users have access to the Azure Storage Account?
Is there any SQL Server Auditing enabled and executing to help monitor for potential security breaches?
Are there any database objects (tables, jobs, views etc) that have been created by a user that has left the organisation? There should be a standard owner of all database objects.
Have you renamed the SA account? And does it have a unique, complex alphanumeric password? Disabled accounts can be re-enabled if another account is exploited. Renaming can help protect against this with obfuscation. The guest account should also always be disabled on a SQL Server.

SQL Server Security Audit

If you don’t have the time to carry out an audit on your environment, it may be worth calling in the experts – we can help. Fill in the form below to arrange a SQL Server Security Audit of your environment today.