SQL Server Security Checklist - An SP with more than 70 security items to validate your database.

Views: 4.614
This post is the 15 part of 15 in the series. Security and Audit
Reading Time: 3 minutes

Hey guys!
In this article I would like to share with you a project that I have been developing since November of 2018 and today has more than 4.500 lines of code, which is a very complete Security Checklist (probably the most complete and comprehensive you will find on the Internet) , with more than 70 Security items to validate your database, including settings and parameters, permissions, programming objects and more!

After seeing so much companies, developers (and sometimes even DBAs) neglecting the security part, where we see environments where the application uses the "sa" user, we find thousands of attempts to connect with the wrong password and no one does anything , NO BACKUP environments and so many other nonsense, we decided to create a very practical and easy way to quickly get an overview of how instance security is doing, in a friendly format and with technical information at the same time, allowing to easily export to Excel and demonstrate to the customer the various problems encountered, the impact this can have on the environment and how to solve it.

Know in this article the ultimate solution to the vast majority of your SQL Server security issues.

The results of the checks are organized as follows:

  • Code: Just a unique number for easy verification item identification, even when I release the English version (spoiler !!)
  • Category: A way to group the checks according to a logical category I envisioned for these validations
  • What is verified: Verification title, which is a summary of what this item is validating in the database.
  • Avaliação: It is the result of validation. It informs you if the item has passed validation (OK), if it is just an informational item, or if a POSSIBLE problem has been identified.
  • Problem description: A brief explanation of why this item is being checked and what security risk it may bring us
  • Verification Detail: More technical and specific details of what is being checked in the instance
  • Correction Suggestion: Some guidelines on how to correct or work around the possible problem identified by the Stored Procedure
  • Validation Results: XML that returns records that failed validation and identified artifacts (some items are limited to TOP (N) records, as they may have too many records returned in XML)
  • Referral URL: Link to any article or documentation that may add or assist in understanding this check item

If the excuse for not addressing the security side of your business was not having a practical and easy way to identify breaches, you didn't know how to fix it or you didn't know what security issues were, your excuses are gone TODAY! Never again will this be a difficulty for you.

This is a project that I use in many clients here at Fabrício Lima - Data Solutions, one of the best database and BI consultancies in Brazil, and is the result of a lot of study, tests and technical discussions with several great data professionals and after talking with Fabrício, we decided to release it in a way. FREE for the entire technical community.

After so much using the Brent Ozar sp_Blitz, I always found it amazing how practical and simple it was to identify various performance, maintenance, auditing, and some security items. Thinking of something as practical as that, I was inspired by this idea to develop stpChecklist_Security, trying to deliver to you something very "F5 version".

This is not a Dirceu or Fabrício project, but yours. For this reason, I am releasing the code for this Stored Procedure on Github, so you can all download it, use it in your environment and help make it better through commit's and pull requests to bring in new features and fixes:
https://github.com/dirceuresende/checklist_seguranca (source code)

Be sure to keep up with my security articles! This is a growing theme in Brazil, especially after the LGPD (General Data Protection Act), and for this reason, I launched the course SQL Server Security - 1 Modulewhere I will go through each of these security items and explain in detail, with unique examples, and demonstrate how they can harm the instance and how we can treat them.

No more searching through various sites and dozens of articles and best practice manuals where people tell you “you should disable this”, but without explaining why convincingly and without technical arguments how it might harm your environment. .

I hope you enjoy this procedure, a big hug for you and see you next time!