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.
The script is called SASAT (QL Server [A]nalysis and ecurity [A]udit [T]ool) and will analyze the following areas.
|Allow Remote Access
|Cross DB Ownership Chaining
|Max Worker Threads
|Startup Stored Procedures
|Affinity I/O Mask
|Affinity64 I/O Mask
|Database Mail XPs
|OLE Automation Procedures
|Ad Hoc Distributed Queries
|Remote Admin Connections
|Default Trace file
|Default SQL Port Number
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 is also provided at the very beginning of the report. The following system related details are provided.
|SQL Server name\Instance name
|Edition and BIT Level
|Production Service Pack Level
|Logical CPU Count
|Total Memory (Megabytes)
|Maximum Memory (Megabytes)
|Minimum Memory (Megabytes)
|Default Domain Name
|Service Account name
|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.
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.
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).