SQL Server Security Audit Report
The purpose of sql server security audit report is to identify the potential vulnerabilities in the database system. This is a security audit assessment for Microsoft SQL Server and this report represents how much the database server is at risk to an attack. The vulnerabilities tested in sql server security audit report included:
- Access control
- Privilege Control
- SQL injection
- Configuration Management
Parameters have been classified into 5 categories based on the security impact. These are
- Critical: Very critical vulnerability and it has to be fixed right away
- High: High impact on security need to check based on your business requirement and environment.
- Medium: Medium level of impact
- Low: Impact is very low but still needs to be considered as a security issue.
- Info: Just an information and no impact from security prospective
The target of this sql server security audit report is to identify as much as security vulnerabilities and report it in an organized manner. This is not an evolution of how “good” or “bad” databases are constructed as we are considering only the security prospect.
This report showcases the current security status by severity, vulnerability, compliance based on the given input rules. Any final decisions on how to handle these security issues should be verified and approved by the security leader or application owner. We strongly recommend fixing the Critical Vulnerabilities as a priority.
Passed: The security parameter is passed as the configured value is safe
Exception: The parameter is vulnerable but this has to be an exception as per your business policy.
Failed: Parameter is failed in audit and focus required on this. Critical failures has to be fixed on priority
SQL Server Security Audit Report:
Q1. What is the requirement for running this report?
This is a native T-SQL script or a Powershell script to capture all these parameters. Any DBA with admin permission and also make sure that the DBA should be able to login to the SQL Server with sysadmin rights and also be able to RDS the host machine. For scripts details please have a look at references section.
Q2. What is the process for running this Audit?
- Get ready with an Admin login and RDS rights to the target machine
- Logon to the server and run the prepared script
- Complete the manual verification for the required parameters
- Fill the attached excel sheet with the corresponding values and that’s it reports are ready
When we need to do the same for enterprise environment we can use a SQL Server table to store this data and then use a reporting tool preferably Excel, SSRS or PowerBI to get these reports server wise.
Q3. On which basis we have got these parameters?
If you are an experienced in building new database systems using Microsoft SQL Server you should already have a checklist to check the current server security. These parameters have been prepared based on security best practices and Microsoft suggested practices, Security centre checklists and categorized based on our business requirement.
Note: We can change the severity, parameters, and their expected values and more things can be automated based on your business requirements and policies.
Q4. Does this report impact the performance or any other aspect? Any caution required?
No! It just showcases the database server security levels and doesn’t impact the business in anyway. We used T-SQL scripts and some of the parameters has been captured manually. Below are the statistics per server.
Scripts Run time <= 2 sec.
Data Retrieval <= 100 kb
Q10. Do we have any third party tools available for this audit?
Yes! Usually for sql server security audit report we use third party tools where we can see more options to customize the level of Audit and the richness on reporting. But the business should be ready to invest on license. This is a native T-SQL script with MS Excel Pivots and the same audit can be implemented in more effective way using Powershell scripts and PowerBI.
Excel Sheet – sql server security audit report
Thanks to Rudy Panigas and Mayur-DEW for providing wonderful T-SQL scripts.