Visualizações: 0
Tempo de Leitura: 3 minutos

Hello guys!
This is a special post for me, because it’s my first post in English here in my blog. The main goal of this post is to share and show how to use the stpSecurity_Checklist Stored Procedure to perform a complete security checklist on your SQL Server instances.

After a big change in my original SP (SQL Server – Checklist de Segurança – Uma SP com mais de 70 itens de segurança para validar seu banco de dados) to allow multilanguage and spent many hours translating every string to English, I’m finally able to share with you this version of the security checklist.

I started this project in November 2018 and now with more than 6,000 lines of code, this Security Checklist (probably, the most complete you will find on the Internet), with more than 70 security items to check your database, going through the part of settings and parameters, permissions, programming objects and more!

After seeing a lot of companies, developers (and sometimes DBAs themselves) overlook the security of their SQL Server instances, where we see environments which the application uses the “sa” user, for example, or find thousands of attempts to connect with bad password and nobody does anything, instances without a well-defined backup routine, we have decided to create a very practical and easy way to quickly have an overview of SQL Server security, in a friendly format with technical information at the same time, allowing you to easily export to an Excel and demonstrate to the customer the many issues founded, the impact that this can cause on the instance and how to solve.

The output of the stored procedure is organized as follows:

  • Code: A unique number to easily identity the validation item
  • Category: A way to group the checks according to a logical category I envisioned for these validations
  • Title: Verification title, which is a summary of what this item is validating in the database
  • Result: Is the result of validation. It informs if the item passed the validation (OK), if it is only an informative item or if it has identified a POSSIBLE problem
  • How this can be an Issue: A brief explanation of why this item is being scanned and what security risk it can bring us
  • Technical explanation: More technical and specific details of what is being checked on the instance
  • How to Fix: Some guidelines on how to correct or circumvent the possible problem identified by the Stored Procedure
  • Result Details: XML that returns the records that caused the validation failure and the artifacts identified (some items are limited to TOP (N) records because they can have many records returned in XML)
  • External Reference: Link to a article or documentation that might assist in understanding this verification item

If your excuse for not treating the security problems in your company was not having a practical and easy way to identify the issues, didn’t know how to solve or didn’t know what the security problems were, your excuses are gone! This shouldn’t be a problem for you anymore.

This is a project that I ran in several clients here at Fabrício Lima – Soluções em BD , one of the best BI and SQL Server consulting companies in Brazil, and this is the result of a lot of study, tests and technical discussions with several great professionals of the data platform area and after talking with Fabrício, we decided to release it in a FREE way for the entire technical community.

After using sp_Blitz from Brent Ozar, which I think that is incredible, practical and simple to find a lot of items to check performance, maintenance, auditing, and some security features. Thinking of something as practical as this, I was inspired by this idea to develop stpSecurity_Checklist, trying to deliver something as practical as a “F5” to you.

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

I hope you like this Procedure, a big hug for you and see you next time!

14 Comments

  1. Great SP, I also thank you. I ran into an issue running from SQL Agent job (does not get this when run from SSMS).
    Msg 537, Sev 16, State 3, Line 5916 : Invalid length parameter passed to the LEFT or SUBSTRING function. [SQLSTATE 42000]
    Following is the line of the SP it is occurring on.
    SET @RetornoTabela = SUBSTRING(@xml, @PosicaoInicialVersao, @PosicaoFinalVersao – @PosicaoInicialVersao + 8)
    version (SQL2016) 13.0.5337.0 Enterprise
    collation SQL_Latin1_General_CP1_CI_AS

    v.burke
  2. Hello Dirceu,

    First of all thank you for this sp I have just tried it and I think it is quite usefull.

    I encountured a problem and wanted to give you a feedback

    I used this sp in 2 enviorments(both in test/dev)
    In One of them it worked perfectly in the other one it failed.

    The failed enviorment is;
    Microsoft SQL Server 2019 (CTP2.2) – 15.0.1200.24 (X64) Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )
    Server Collation : SQL_Latin1_General_CP1_CI_AS
    DB Collations : Turkish_CI_AS,SQL_Latin1_General_CP1_CI_AS
    Compatibility Level : 130,150

    The error was:
    Warning: Null value is eliminated by an aggregate or other SET operation.
    Warning: Null value is eliminated by an aggregate or other SET operation.
    Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
    Configuration option ‘Ole Automation Procedures’ changed from 0 to 1. Run the RECONFIGURE statement to install.
    Configuration option ‘Ole Automation Procedures’ changed from 1 to 0. Run the RECONFIGURE statement to install.
    Configuration option ‘show advanced options’ changed from 1 to 0. Run the RECONFIGURE statement to install.
    Msg 9413, Level 16, State 1, Procedure dbo.stpSecurity_Checklist, Line 5753 [Batch Start Line 0]
    XML parsing: line 1, character 15, A string literal was expected

    I have all the editions of SQL Server since 2008 in my company and I will test on them to see if I encounter any problems.

    Hope this information is usefull.
    If you’ll need more info on the error please feel free to contact me

    This release of SQL Server is in CTP so this may just be a SQL Server problem.

    Hakan Hıfzıoğlu

Deixe uma resposta