- Audit on SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to implement audit and control of logins (Logon Trigger)
- Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable
- Using SQL Server Standard Trace to Audit Events (fn_trace_gettable)
- SQL Server - Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)
- SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)
- SQL Server 2016 - How to “Time Travel” Using the Temporal Tables Feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent changes to tables
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to Avoid Brute Force Attacks on Your Database
- SQL Server Security Checklist - An SP with more than 70 security items to validate your database.
- SQL Server - How to know the date of a user's last login
- SQL Server - How to Avoid and Protect Against Ransomware Attacks Like WannaCry on Your Database Server
- SQL Server – Beware of server role securityadmin! Using privilege elevation to become sysadmin
- SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now.
- SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change, and Login Lockout after N attempts
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 SHIPPING 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!
Good morning, I downloaded the SP I find fantastic the work done? ! Which bank does it support? I am testing on SqlServerExpress 2008 and returned the error below!
RegQueryValueEx () returned error 2, 'The system cannot find the file specified.'
22001 Message, Level 1, State 1
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.
0 Message, 11 Level, 0 State, 6291 Line
Serious error on current command. The results, if any, should be discarded.
Good afternoon Dirceu!
I would like to thank you for the incredible work that you and Fabrício do and for helping us with the transfer of knowledge.
You are beasts!
Thanks for the words and compliments. We really do it out of passion for sharing knowledge.
It really is a great pleasure doing this and I am glad to hear that we have helped you somehow 🙂
Through this procedure, can I audit DML transactions in the tables too?
Hey Michelle, how are you?
This SP has no audit, but you can do this using:
- Audit (https://www.dirceuresende.com/blog/auditoria-sql-server-audit-dml-ddl/)
- CDC (https://www.dirceuresende.com/blog/sql-server-como-monitorar-e-auditar-alteracoes-de-dados-em-tabelas-utilizando-change-data-capture-cdc/)
- Trigger (https://www.dirceuresende.com/blog/sql-server-como-criar-um-historico-de-alteracoes-de-dados-para-suas-tabelas-logs-auditoria/)
TOP Too Dirceu !!! This will surely help a lot of people discover various problems in their environments to fix and prevent attacks! Sent too well !!
Thank you, Luiz.
Let's make sql server banks more secure !! LOL