SQL Server is a very powerful data platform and part of this power, is to allow the user to control different settings, thus making it work the best for his/her needs. However, as in all systems, if these settings are misconfigured, or the proper precautions are not taken on the user’s side, then along with the functionality the user wants to enable, he or she possibly will create security risks.
Take for example, the ‘Password Expiration’ option. In case you have a SQL login which is used as a service account, then this could be a reason for not to have the ‘Password Expiration’ enabled for that login. If however, you have an SQL login which is used by a physical person and the ‘Password Expiration’ option is not enabled, this increases the risk of having the password guessed more easily than in the case where the password expires every X days (i.e. every 90 days) and thus the user needs to enter a new one.
|Figure 1: Start Page.|
There are many settings which can be incorrectly set by the user, either because of naivety, or lack of deep understanding about these settings, or any other reason.
By the time a DBMS hosts a single database, it is critical that you keep that DBMS instance as secure as possible. Misconfiguring your instance can be a source of vulnerabilities so you need to periodically check you instance about related security risks and take remediation actions when and where needed.
It is based on all the above that I have been developing during the last two years "DBA Security Advisor". DBA Security Advisor is a powerful tool i released a few days ago, which assesses SQL Server instances for potential security risks based on a proven best practices set of security checks. Furthermore it provides recommendations for the detected security risks as well as remediation scripts and methods.
DBA Security Advisor comes in two editions: (i) A Community Edition which is free but with a limited set of security checks and limited functionality, and (ii) An Enterprise Edition where all security checks and other features are available. You can compare the available features per edition on this link.
The workflow of DBA Security Advisor (Enterprise Edition) is very straightforward:
1. You connect to a single or multiple SQL Server instances.
2. You select the security checks to run against the connected SQL Server instance(s) and run the assessment.
3. You go through the generated report with the security findings.
4. You study the recommendations and remediation scripts/methods and act accordingly towards resolving the security risks.
5. You re-run the security assessment and check if the previously-reported security risks have been eliminated after you took actions.
|Figure 2: Connect to Multiple Instances (Enterprise Edition).|
|Figure 3: Select Security Checks (Enterprise Edition).|
|Figure 4: Embedded Report Viewer: Sample Security Risks.|
|Figure 5 Standalone Security Report Viewer with Recommendations and Remediation Scripts and Methods.|
I believe that you will find DBA Security Advisor extremely useful. It will help you secure your SQL Server instances, as well as become compliant with a large number of security best practice factors. Test the Community Edition today which is free, and after you are convinced that DBA Security Advisor can help you in your everyday SQL Server administration and hardening process, you can consider upgrading to the Enterprise Edition, and thus unlock all security checks and the rest of its powerful features.
Microsoft Data Platform MVP