SQL Server Security Violations Report for t-sql tuesday

Regular SQL Server scans for security violations is key to maintaining a Security Policy across the Enterprise. An effective method is to create a SQL Server Security Violations Report.

Managing SQL Servers across the Enterprise requires a different mindset.A large organisation can have thousands of SQL Server Instances.There are multiple tiers (Production and Non-Production) , multiple developer groups , multiple applications etc.

A key challenge is to identify security exceptions.

What is the purpose of  SQL Server Violations Report?

A managed process to highlight  non-compliance of  selected SQL Server Security Policies. The details  differ amongst organisations but the basic principle remains consistent.

Typically a  report is created through a scheduled scan of SQL Server Instances.  It makes sense to prioritise Production but lower environments also require attention

The security scan is managed by the DBA . The DBA usually maintain reports on different aspects of SQL Server administrations . Some examples are:

 SQL Server - Powershell and Failed Logon attempts - SQL Server DBA

Security audit for SQL public role - SQL Server DBA

 How to manage the IS_GRANTABLE security risk - SQL Server DBA

How often will the SQL Server Violation Report be generated?

 Dealing with security violations is time-consuming task. Depending on your circumstances , I’d advise to generate a report once a week.

Generating a daily report , unless required, can begin to dominate the day and distract from other important tasks

 What details are in the SQL Server Violation Report?

This is an example of the sort of violations to check. There are all sorts of possibilities and is  defined by requirements. Advice: Start of small and clear up the easy ones before moving to obscure violations

a)     All non – AD logins which do are not accounted through the exception process

b)    All instances of permissions granted directly to an object and not through a database role

c)     If the login is a SQL based login return the login if the password is blank

d)    If the login is a SQL based login return the login if the password is null

Read more on Find Weak passwords in SQL Server - SQL Server DBA

 What is expected from the DBA ?

 The DBA will :

a)     Review the Violations Report and work with the owners to either fix the issue or if a valid business justification is given – apply for an security exception.

b)The issue fix will be managed via an auditable communication trail. For example , a helpdesk request system .

 What if the owner wants to maintain the security violation ?

 This is the fun part! If the security violation needs to be retained and there is a business -  justification , some sort of Database Exception process  must be completed, otherwise the violation should be  be removed from the logon account.

 Documenting and implementing the exception process is an important step. Firstly  , it illustrates to security officers and auditors you are managing security . Secondly, it becomes a task list , which you can use as a reminder to fix the issues .

Read More on Database server security audit process

SQL Server - Database Server Security Audit Process - SQL Server ...

How to create a SQL Server Security Audit - SQL Server DBA