SQLServerCentral Article

SQL Server System Audit Report

,

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 (QL Server [A]nalysis and ecurity [A]udit [T]ool) and will analyze the following areas.

TRUSTWORTHY databasesAllow Remote AccessCross DB Ownership ChainingMax Worker Threads
Priority BoostLightweight PoolingStartup Stored ProceduresAffinity64 Mask
Affinity I/O MaskAffinity64 I/O MaskCLR enabledDatabase Mail XPs
OLE Automation ProceduresAd Hoc Distributed Queriessa accountRemote Admin Connections
Default Trace fileDefault SQL Port Numberxp_dirtreexp_fixeddrives
xp_enumgroupsxp_servicecontrolxp_subdirsxp_regaddmultistring
xp_regdeletekeyxp_regdeletevaluexp_regenumvaluesxp_regremovemultistring
xp_regwritexp_regwritexp_cmdshellAudit 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 nameInstallation DateMachine NameInstance Name
Edition and BIT LevelProduction Service Pack LevelProduction VersionProduction Name
Environment TypeLogical CPU CountTotal Memory (Megabytes)Maximum Memory (Megabytes)
Minimum Memory (Megabytes)IP AddressPort NumberDefault Domain Name
Service Account nameClustered StatusClusterKerberos
Security ModeAudit LevelUser ModeFileStreams
Backup CompressionCollation TypeSQL Server Errorlog LocationDefault Trace File
SQL Server Default Trace LocationTrace Flags SettingNumber 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

Rate

4.55 (29)

Share

Share

Rate

4.55 (29)