SQL Server Audit Checklist

Server-side events logs and everything that comes with it can be a big stress.

With SQL Server logging compliance standards tighter than ever in the face of recent data security acts such as HIPAA, PCI DSS, and SOX, there is often so much that goes into the preparation for an SQL Server Audit that it can be confusing to keep track of everything.

On the other hand, the SQL Server Audit process has been greatly improved in recent years by the addition of Extended Events logs, allowing administrators to audit everything that happens on each server, from large-scale changes to system settings right down to which user modified what specific value in a particular database at any given time.

But still, despite all of these improvements to the server auditing process, it can still be daunting to get started without a formal process already in place.

Alongside events log best practices, there are a number of related tasks you should be incorporating into any routine server best practice check, like system updates and data encryption. These additional checks serve to support the audit process as well as harden overall server security.

This checklist is designed to give you a head-start for preparation ahead of and including an SQL Server Audit. Though we've been rigorous, this checklist is just an example and is by no means exhaustive of every SQL Server security parameter.

Nonetheless, let's dive right in!

To begin with, record some basic information about yourself (or whoever is performing the server audit).

All of this information goes towards ensuring data security best practices are enforced - it's important to know who had access to what information, and when. 

Just fill out each form field in this task before proceeding with the rest of the checklist.

We're starting with the simple stuff - this kind of cleanup shouldn't take long and helps free up server space. 

It's also a good idea in general to keep only the most necessary software installed on your servers, as every 3rd party install is a potential backdoor for malicious actors.

Here's a quick rundown of the essentials for this task:

Removing unused programs on Linux is very simple. Just enter the following in the terminal:

 sudo apt autoremove

Linux systems are designed to keep track of which programs and packages aren't used or depended on, so it can trim the surplus with a simple command.

Microsoft distributes bug fixes for all of their products in "service packs".

Each pack includes updates for system drivers, system administration tools, and additional components all bundled together for ease of use. 

Service packs are cumulative – you will only ever need to install the most recent.

Check here for all SQL Server updates, and make sure your system is fully patched.

You need to make sure that the SQL Server is running on the least-privileged local account level.

If network services are required, then granting domain-level privileges is acceptable.

Your task here is to remove all unused accounts for both Windows and SQL Server.

Whether it's because of staff turnover, systems testing, or just plain oversight, old user accounts that are no longer in use present a security threat because they aren't able to be maintained to updated security standards. 

As such, they should be regularly purged so that malicious actors can't exploit them. It's good practice to delete accounts that haven't been used in over a month.

 User groups can be incredibly useful when used properly, making it easy to manage and scale large sets of people.

When poorly managed, they become a security threat and a general nuisance.

You should carefully monitor which groups have access to what, especially if you use large generic "Everyone" groups. Sometimes, cracks in system security can appear through these group access rights, so it's crucial you clamp down on this, and include it in your regular audit process.

Check of these sub-tasks once you've confirmed each of them as true:

The security of your database depends on your ability to enforce accountability. Your extensive events logging efforts are all in vain if you can't even put a name to an action, should any internal hiccups arise.

That's why it's important to ensure that login restrictions are in place. Your database admins should be accessing the SQL Servers from company machines only.

These machines have been set up to comply with specific information security compliance protocols for a reason, and it defeats security protocol best practice to allow random access from unknown external machines, even if the person remotely accessing the network is trusted.

Perform the following sub-tasks and check them off as you go:

The administrator account(s) should be renamed and passwords reset regularly.

While you're at it, make sure that membership to the local administrator group is restricted to at most two users. Usually, only the database administrator (DBA) will have admin rights in this scenario.

Encryption allows protecting the underlying database storage files and on-site/offsite backups from theft. Most encryption algorithms rely on a principle of using a secure "key" or password held by the user to secure or "lock" the data so that the key is required to access it.

Which encryption algorithm is best for you will depend on your needs and setup, as well as what kind of data needs encrypting. Follow the sub-checklist tasks below to deploy encryption on your SQL Server database.

Just as with User Group access, file and folder shares are often overlooked and many unnecessary shares are left sitting wide open for no good reason.

Take the time to review all shared folders on the server, and remove any that aren't necessary.

The more protocols that your server is configured to listen and respond to, the more vulnerabilities exist within your network. Make sure only the necessary protocols are enabled; usually, TCP/IP is adequate. 

If there are additional protocols which you need to activate for server functionality, you can easily add them to the sub-checklist above by editing this task in our template editor.

Proper port access configuration, including restrictions are important security measures for any SQL database server.

Make sure that access to all ports on the server is restricted, except for ports configured for SQL Server and database instances.

By default, the ports configured for SQL server and database instances are TCP 1433 and UDP 1434.

For security reasons, it is also important to make sure that the ports configured with SQL servers are non-default.

Failed login attempts can indicate a malicious actor is attempting to access the system.

There are four types of SQL Server login auditing to choose from:

1. None
   Turns off login auditing.
2. Failed logins only
   Audits unsuccessful logins only.
3. Successful logins only
   Audits successful logins only.
4. Both failed and successful logins
   Audits all login attempts.

Make sure events logs are enabled for all such instances:

Once you've made sure all relevant actions across the network are included in the logs, you should take measures to ensure that they are being stored properly and securely, and that relevant triggers are enabled for sensitive data log overflows.

You can use ApexSQL to setup SQL Server triggers. This useful tool allows you to capture data changes with information about who exactly made the change, to which specific items in the database, and when.

It also includes logs of application and host login records, all presented in an easily legible central repository table, which can be reported on and exported to a variety of formats.

To create a SQL Server database trigger based audit trail with ApexSQL Trigger, follow the sub-tasks below:

We're almost done - the next step is to create an SQL Server Audit object using SQL Server Management Studio, a configurable tool for managing all kinds of server events.

This process can get a bit involved, but we've outlined all of the important steps for you already. Just make sure you follow each sub-task in the sub-checklists below and you'll have your Audit set up in no time.

Your newly created audit should now appear in the "Audits" section of the Object Explorer window. It's disabled by default, so you'll want to go ahead and enable it.

Your server audit should now be all set up.

Before you finish with this checklist, the results of the audit might have triggered an automatic server shutdown for security reasons. If that's the case, it will need to be flagged for restart manually.

If the SQL Server has been forcefully shut down as a result of an SQL Audit, normal startup will be disabled. 

You need to manually enable a server restart, which can be done by adding the "-m" trace flag to single user mode startup configuration. 

Follow these sub-tasks to change the server settings and enable a restart:

• Enclave Security - SQL Server Audit Process
• Medium - All-In-One Server Audit Script
• Microsoft - SQL Server Audit Bes Practice Checklist
• Microsoft Technet - Server Security Best Practice Checklist
• TechTarget - Server SQL Security Checklist
• Brad McGehee - SQL Server Security Best Practices
• ApexSQL - What is SQL Server Tracking and How to Set It Up
• SQLShack - Understanding the SQL Server Audit

• Inventory Management Process
• Network Security Management
• Client Data Backup Best Practices
• Computer Maintenance Guide
• Server Setup Process
• Virtual Private Server Setup
• IT Support Process
• Helpdesk Management
• Server Maintenance Checklist
• Information Security Incident Response
• Server Security Checklist