Introduction:

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!

Preparation:

Record basic details

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.

System maintenance:

Uninstall unused programs on Windows

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:

  • 1
    Open the Control Panel
  • 2
    Click "Programs and Features"
  • 3
    Sort by recently used
  • 4
    Look for software that hasn't been used in the past month
  • 5
    Remove unnecessary programs as you see fit

Uninstall unused programs on Linux

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.

Install latest service packs and patches

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.

  • 1
    Updates list checked for new service packs
  • 2
    New service packs and patches have been applied for SQL Server

Access and privileges:

Check the SQL Server privilege level

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.

  • 1
    Check the SQL Server account privilege level
  • 2
    Evaluate how low the privilege can be made without affecting function
  • 3
    Adjust SQL Server privilege levels accordingly

Clean up unused accounts

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.

  • 1
    Unused user accounts removed
  • 2
    Unused SQL Server accounts removed

Restrict group access

 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:

  • 1
    Make sure the "Everyone" group cannot access SQL Server install directories
  • 2
    Ensure group membership audit policy is updated and enforced

Ensure login restrictions are applied

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:

  • 1
    Remote logins are restricted
  • 2
    Anonymous logins are restricted

Review administrator settings

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

  • 1
    Administrator account names reset
  • 2
    Administrator account passwords reset

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.

  • 1
    Remove all non-essential users from the local administrator group

Data protection:

Encrypt sensitive data files

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.

  • 1
    Choose an encryption algorithm
  • 2
    Generate a private key
  • 3
    Securely store the private key
  • 4
    Select which data to encrypt
  • 5
    Encrypt the sensitive data

Remove unnecessary shared folders

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.

  • 1
    All unnecessary shares are removed from the server
  • 2
    Access to required shares is restricted
  • 3
    Unnecessary admin shares are removed

Server security:

Disable all protocols except TCP/IP

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. 

  • 1
    Open Server Network Utility
  • 2
    Expand the Network Configuration node
  • 3
    Select "Protocols"
  • 4
    Right click "TCP/IP" and click "Properties"
  • 5
    Make sure the only protocol enabled is "TCP/IP"

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.

Configure server ports

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.

  • 1
    Relevant access granted for database server instance ports
  • 2
    Access to all other ports on the server is restricted

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

  • 1
    Ensure that SQL server port is non-default

Events logs:

Check SQL Server login audit

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:

  • 1
    All failed operating system login attempts are logged
  • 2
    All failed actions are logged across the file system

Check system event log configuration

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.

  • 1
    Event log files have been securely backed up
  • 2
    Relevant access has been given to the event logs
  • 3
    Shut Down mode is enabled for sensitive log overflows

Check SQL Server triggers

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:

  • 1
    Start ApexSQL Trigger
  • 2
    Connect to the database to audit
  • 3
    In the main grid, select the tables you want to monitor
Credit to SQLShack for the image.
  • 1
    In the Columns pane, select the columns you want to monitor
  • 2
    Click "Create triggers"
  • 3
    Click "Execute" or press the "F5" key to finalize

Create a new SQL Server Audit

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.

  • 1
    Expand the "Security" menu
  • 2
    Right-click "Audits in SSMS"
  • 3
    Select "New Audit"
  • 4
    Assign a name to the audit
  • 5
    Choose how you want the audit logs to be stored
  • 6
    Choose location of the output audit file
  • 7
    Click "Ok"

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.

Flag the server for restart

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:

  • 1
    Open the SQL Server Configuration Manager
  • 2
    Select your server instance
  • 3
    Select "Properties"
  • 4
    Go to the "Startup Parameters" tab
  • 5
    Add the "-m" trace flag to single user startup mode configuration

Sources:

Sign up for a FREE account and
search thousands of checklists in our library.

Sign up for a FREE account and search thousands of checklists in our library.