SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server System Audit Report

By Rudy Panigas, (first published: 2016/02/02)

With every technology, security is in the forefront of the minds of professionals around the world. Ensuring that your SQL Server is secure is the job of every Database Administrator (DBA). The DBA(s) needs to configure the system to minimize the “attack surface” (reducing security risks) to help in the protection of the business data.

Currently when installing later versions of SQL Server, Microsoft does its best to secure the system. However this can change with the features you install and/or settings that are changed. For example, does this application require features like Full Text Search or Analysis Services? Are you using the default port number? Which protocols are turned on but not used? Depending on the answer these could increase the possibility of security risks.

Over the years I have not found a simple, easy to use, and free tool to help with the evaluation of the system. Therefore I wrote my own T-SQL script to help and is based on best practices from Microsoft, CIS (Center for Internet Security) and others with the results verified with Nessus application from Tenable.

Audit Report

The script is called SASAT ([S]QL Server [A]nalysis and [S]ecurity [A]udit [T]ool) and will analyze the following areas.

TRUSTWORTHY databases

Allow Remote Access

Cross DB Ownership Chaining

Max Worker Threads

Priority Boost

Lightweight Pooling

Startup Stored Procedures

Affinity64 Mask

Affinity I/O Mask

Affinity64 I/O Mask

CLR enabled

Database Mail XPs

OLE Automation Procedures

Ad Hoc Distributed Queries

sa account

Remote Admin Connections

Default Trace file

Default SQL Port Number

xp_dirtree

xp_fixeddrives

xp_enumgroups

xp_servicecontrol

xp_subdirs

xp_regaddmultistring

xp_regdeletekey

xp_regdeletevalue

xp_regenumvalues

xp_regremovemultistring

xp_regwrite

xp_regwrite

xp_cmdshell

Audit Level

Server Authentication

The analysis provides information on the current status of each of these areas along with a pass or fail/warning status. As the analysis is performed and a failure/warning is detected the following will be displayed.

  1. The area where the failure/warning occurred.
  2. Reason: The reason why this section has failed or has warnings with information explaining why securing this area is important.
  3. Recommended Changes: This will show how to address the concerns by either T-SQL scripts and/or SQL Server Management Studio (SSMS).
  4. References: External links are provide for further information.

The final section of the report presents a percentage of success rate. The higher the score, the more secure the system is. A list is produced of the areas of concern and are presented for review.

Additional Information

Additional information is also provided at the very beginning of the report. The following system related details are provided.

SQL Server name\Instance name

Installation Date

Machine Name

Instance Name

Edition and BIT Level

Production Service Pack Level

Production Version

Production Name

Environment Type

Logical CPU Count

Total Memory (Megabytes)

Maximum Memory (Megabytes)

Minimum Memory (Megabytes)

IP Address

Port Number

Default Domain Name

Service Account name

Clustered Status

Cluster

Kerberos

Security Mode

Audit Level

User Mode

FileStreams

Backup Compression

Collation Type

SQL Server Errorlog Location

Default Trace File

SQL Server Default Trace Location

Trace Flags Setting

Number of Link Servers

Although the information is not analyzed, the DBA(s) should review this information and make changes where needed. For example, change the MAX and MIN memory settings if they are not set. This is not a security risk but will help with performance.

Other information shown is a list of the members of “SysAdmin” group and the “ServerAdmin” group. These two sections should be reviewed be the DBA(s) and auditor(s) as these connections have full access.

Remarked Section

At the end (bottom) of the script is a section that has been remarked out. If executed, this code will show all setting within sys configurations. I have added a column called “Change Effect” to displaying when a change is made what is needed to make it take effect.

Final Thoughts

With the report generated the DBA(s) can easily address each area of concern and create documentation to explain why certain area, although have failed, are needed for application(s) support.

It is important to note that the DBA(s) still needs to ensure that user accounts have the minimal permissions need to perform their tasks. A review of users and permissions is still required. The scope of this script to check the “system” settings and not the users themselves. It is highly recommended that an internal and external auditors review all your systems.

Using an automated script like this will help secure the SQL Server system and make detection of these settings easier for the DBA(s).

The script as of this writing has been tested on SQL Server 2012 and higher. This script could also work on lower versions of SQL Server but testing and validation is required. As always, test this script on a development system and compare the results to ensure accuracy/correctness. There are no changes made to SQL Server itself by this script and therefore all changes must be performed manually. Remember, it is the responsibility of the DBA(s) and auditors to ensure a secure SQL Server environment(s).

Thank you,

Rudy Panigas

 

Resources:

SASAT-Analysis and Audit of SQL Server 2012-2014-ver4.9.sql
Total article views: 4775 | Views in the last 30 days: 11
 
Related Articles
SCRIPT

Security Change Snapshot

This script gives a server level snapshot of recent security changes

ARTICLE

A Standard Database Security Script Generator

A script generator for standard security compliance.

FORUM

Find the file size of Windows logs(Event,security & system) Using TSQL script.

Find the file size of Windows logs(Event,security & system) Using TSQL script.

FORUM

Security Managemen Systems

problem with Security Managemen Systems

ARTICLE

Deploy Security Changes with Copy-SqlLogin

Moving security changes between server instances can be a cumbersome process, but it doesn't need to...

Tags
audit    
security    
 
Contribute