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

Views: 4.632
This post is the 15 part of 16 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 so much watching companies, developers (and sometimes DBAs themselves) neglect 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 nobody 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 using sp_Blitz so much, from the myth Brent Ozar, I always found it amazing how practical and simple it was to identify various items of performance, maintenance, auditing and some security items. Thinking of something as practical as, I was inspired by this idea to develop the stpChecklist_Seguranca, trying to deliver something very “F5 version” to you.

This is not a project by Dirceu or Fabrício, but by you. For this reason, I'm releasing the code for this Stored Procedure on Github, so that all of you can download, use it in your environments and help make it better through commit's and pull requests to bring new features and corrections:
- 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 Security in SQL Server - Module 1where 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 various websites and dozens of articles and good practice manuals in which people tell you that “you should disable this”, but without explaining why convincingly and without technical arguments on how it can harm your environment .

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