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.
Audit Result:
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:
FAQ
Q1. What is the requirement for running this report?
Ans:
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?
Ans:
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?
Ans:
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?
Ans:
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?
Ans:
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.
Can you please help me with steps of how to prepare this report?
can u please share the script.
Can you please help me with steps of how to prepare this report?