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 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.
- The area where the failure/warning occurred.
- Reason: The reason why this section has failed or has warnings with information explaining why securing this area is important.
- Recommended Changes: This will show how to address the concerns by either T-SQL scripts and/or SQL Server Management Studio (SSMS).
- 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
