MS SQL Server¶

Vulnerability Assessment¶

How to find VA through SSMS¶

Source: https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-vulnerability-assessment?view=sql-server-ver15
Open Microsoft SQL Server Management Studio
Right click database->Tasks->Vulnerability Assessment->Scan For Vulnerabilities
Should open up a Vulnerability Assessment Results tab + a JSON file that's saved on the computer

How to find VA through Azure Portal for a SQL server deployed on a machine¶

Go to 'Security Center'
Under 'Resource security hygiene', click on 'Recommendations'
Click on one of the controls that you think is for a SQL server (eg: 'Vulnerability on your SQL server machines should be mediated)
- Click on the specific recommendation
- Click on one of the VAs
- Scroll down to affected resources, click on the interested database

These paths don't work:¶

Go to 'Security Center'
Under 'Resource security hygiene', in 'Resource health by severity', there might be a 'Data Storage and Resources'
Once you click on it, there might be a 'SQL' tab with a list of deployed SQL servers
Once you click on the interested database, there should be a section called Recommendations
Click on the recommendations only to see a error screen that makes you question why Microsoft sucks.

Assessment Rules¶

Source: https://eitanblumin.com/sql-vulnerability-assessment-tool-rules-reference-list/#Rule_VA1051
Source: https://docs.microsoft.com/en-us/azure/azure-sql/database/sql-database-vulnerability-assessment-rules
Auditing And Logging – 3 rule(s)
- Severity: Medium – 2 rule(s)
  - VA1265: Auditing of both successful and failed login attempts for contained DB authentication should be enabled
  - VA1281: All memberships for user-defined roles should be intended
- Severity: Low – 1 rule(s)
  - VA1253: List of DB-scoped events being audited and centrally managed via server audit specifications.
Authentication And Authorization – 36 rule(s)
- Severity: High – 8 rule(s)
  - VA1020: Server principal GUEST should not be a member of any role
  - VA2000: Minimal set of principals should be granted high impact database-scoped permissions
  - VA2001: Minimal set of principals should be granted high impact database-scoped permissions on objects or columns
  - VA2002: Minimal set of principals should be granted high impact database-scoped permissions on various securables
  - VA2020: Minimal set of principals should be granted ALTER or ALTER ANY USER database-scoped permissions
  - VA2021: Minimal set of principals should be granted database-scoped ALTER permissions on objects or columns
  - VA2022: Minimal set of principals should be granted database-scoped ALTER permission on various securables
  - VA2108: Minimal set of principals should be members of fixed high impact database roles
- Severity: Medium – 9 rule(s)
  - VA1042: Database ownership chaining should be disabled for all databases except for ‘master’ and ‘tempdb’
  - VA1043: Principal GUEST should not have access to any user database
  - VA1095: Excessive permissions should not be granted to PUBLIC role
  - VA1248: User-defined database roles should not be members of fixed roles
  - VA1267: Contained users should use Windows Authentication
  - VA2010: Minimal set of principals should be granted medium impact database-scoped permissions
  - VA2050: Minimal set of principals should be granted database-scoped VIEW DEFINITION permissions
  - VA2051: Minimal set of principals should be granted database-scoped VIEW DEFINITION permissions on schema
  - VA2052: Minimal set of principals should be granted database-scoped VIEW DEFINITION permission on various securables
- Severity: Low – 19 rule(s)
  - VA1054: Excessive permissions should not be granted to PUBLIC role on objects or columns
  - VA1069: Permissions to select from system tables and views should be revoked from non-sysadmins
  - VA1070: Database users shouldn’t share the same name as a server login.
  - VA1094: Database permissions shouldn’t be granted directly to principals
  - VA1096: Principal GUEST should not be granted permissions in the database
  - VA1097: Principal GUEST should not be granted permissions on objects or columns
  - VA1099: GUEST user should not be granted permissions on database securables
  - VA1246: Application roles should not be used
  - VA1282: Orphan roles should be removed
  - VA1286: Database permissions shouldn’t be granted directly to principals (OBJECT or COLUMN)
  - VA2030: Minimal set of principals should be granted database-scoped SELECT or EXECUTE permissions
  - VA2031: Minimal set of principals should be granted database-scoped SELECT permission on objects or columns
  - VA2032: Minimal set of principals should be granted database-scoped SELECT or EXECUTE permissions on schema
  - VA2033: Minimal set of principals should be granted database-scoped EXECUTE permission on objects or columns
  - VA2034: Minimal set of principals should be granted database-scoped EXECUTE permission on XML Schema Collection
  - VA2040: Minimal set of principals should be granted low impact database-scoped permissions
  - VA2041: Minimal set of principals should be granted low impact database-scoped permissions on objects or columns
  - VA2042: Minimal set of principals should be granted low impact database-scoped permissions on schema
  - VA2109: Minimal set of principals should be members of fixed low impact database roles
Data Protection – 7 rule(s)
- Severity: High – 5 rule(s)
  - VA1098: Any Existing SSB or Mirroring endpoint should require AES connection
  - VA1221: Database Encryption Symmetric Keys should use AES algorithm
  - VA1222: Cell-Level Encryption keys should use AES algorithm
  - VA1223: Certificate keys should use at least 2048 bits
  - VA1224: Asymmetric keys’ length should be at least 2048 bits
- Severity: Medium – 2 rule(s)
  - VA1219: Transparent data encryption should be enabled
  - VA1287: Sensitive data columns should be classified
  - Surface Area Reduction – 8 rule(s)
- Severity: High – 4 rule(s)
  - VA1102: The Trustworthy bit should be disabled on all databases except MSDB
  - VA1245: The dbo information should be consistent between the target DB and master
  - VA1256: User CLR assemblies should not be defined in the database
  - VA1277: Polybase network encryption should be enabled
- Severity: Medium – 4 rule(s)
  - VA1044: Remote Admin Connections should be disabled
  - VA1051: AUTO_CLOSE should be disabled on all databases
  - VA1143: ‘dbo’ user should not be used for normal service operation
  - VA1244: Orphaned users should be removed from SQL server databases

VA1051¶

Name: VA1051: AUTO_CLOSE should be disabled on all databases
Summary and "why care" for dummies: SQL Servers might have a ton of databases.If AUTO_CLOSE is true for all of them, they'll go "offline" when a app is not using them. Let's say your app needs to authenticate a user so it'll need the database to re-start, set everything up and then eventually shut down. If this happens for a lot of users for a lot of databases on the same server, the server will get overwhelmed with the requests and just crash.
Risk Level: Medium
Benchmark: CIS v1.0.0-08-11-2017:2.16, FedRAMP
Description: The AUTO_CLOSE option specifies whether the database shuts down cleanly and frees resources after the last user exits. This rule checks that this option is disabled on all databases.
Impact: Databases marked with AUTO_CLOSE allows the DB to be closed if there are no active connections. In the case of particularly contained databases, the authentication of users occurs within teh database itself, so the database must be opened every time to authenticate a user. Frequent opening/closing of the database consumes additional resources and may contribute to denial of service attack.
How to check if you db is affected:
- In SSMS, Right click Database->Properties
- Options page and then under Automatic, if Auto Close is 'True', then it's enabled.
Bulk query to find all problem databases:

SELECT CASE
    WHEN EXISTS (
        SELECT * FROM sys.databases
        WHERE NAME = DB_NAME()
            AND is_auto_close_on = 1) THEN 1
        ELSE 0
        END AS VIOLATION, DB_name() AS [DATABASE]
    )

Remediation Steps/Script:
- Change Auto Close in Options to 'False'
- Remediation Script:

ALTER DATABASE [yourDatabaseName] SET AUTO_CLOSE OFF

VA1219¶

Name: VA1219: Transparent data encryption should be enabled
Summary and "why care" for dummies: If TDE isn't being done, any backups, etc are encrypted when stored on the computer. So if a malicious person gets access to your computer, they won't be able to decrypt the backups/logs, etc without a key.
Risk Level: Medium
Description: Transparent data encryption (TDE) helps protect against the threat of malicious activity by performing real-time encryption and decryption of the database, associated backups, and transaction log files ‘at rest’, without requiring changes to the application. This rule checks that TDE is enabled on the database.
Impact: Transparent Data Encryption (TDE) protects data ‘at rest’, meaning the data and log files are encrypted when stored on disk.
Bulk query to find all problem databases:

SELECT CASE WHEN EXISTS
( SELECT *
    FROM sys.databases
    WHERE name = db_name()
    AND is_encrypted = 0)
THEN 1
ELSE 0
END AS [Violation]

Remediation Steps/Script:
Unmanaged SQL servers:
- Enable TDE on the affected database. Please follow the instructions on https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption
Azure SQL VM:
- Security section is disabled. So....I don't know
Azure Managed Database:
- Login to Portal
- Search for 'Azure SQL' or 'SQL databases' or whatever Azure calls it that day
- Choose the interested database
- On left, click on Security
- Click on 'Transparent data encryption'
- Set Data encryption to ON
- Click on Save

VA1287¶

Name: VA1287: Sensitive data columns should be classified
Summary and "why care" for dummies: Some columns might have sensitive data that you want to run reports on later. Therefore, if they have metadata that flags these columns, it's easier to run these reports in case you care.
Risk Level:
Description: This rule discovers and characterizes potentially sensitive data in the database. The result is a collection of sensitive database columns, which should be reviewed and classified using SQL Data Discovery & Classification. This allows database columns to be persistently labeled according to their sensitivity, which enables tracking (auditing) the use of classified data and creating reports. If your sensitive database columns are unprotected, you should also consider applying one of SQL Database’s built-in security capabilities to restrict access to and protect your sensitive data.
Impact: The data residing in your database can have varying levels of business and privacy sensitivity. It is important to be aware of the location of your most sensitive data elements, so that their access can be monitored and tracked. SQL Data Discovery & Classification enables you to assign a distinct classification label to each database column and persist this information as column metadata within the database. This classification metadata can then be used for tracking and monitoring objectives. In addition, access to sensitive data should be more tightly controlled. Built-in SQL security capabilities like Always Encrypted, Dynamic Data Masking, and Row-Level Security can be used to control access and protect data.
How to check if you db is affected:
- Run the VA. If your columns have emails, dates, etc, it'll probably get flagged
Find all problem databases: Review the identified list of potentially sensitive columns and apply classifications where relevant via the Classify Data task of the database. If none of the identified columns actually represent sensitive elements in your database, you can set them as your approved baseline to acknowledge their status and to prevent them from being identified again. In addition, apply column-level data protection security measures where appropriate: Always Encrypted — keeps sensitive data columns encrypted on the server side (‘https://go.microsoft.com/fwlink/?linkid=862688’) or Dynamic Data Masking — limits sensitive data exposure by dynamically masking it to non-privileged users when data is returned from the server to the client (‘https://go.microsoft.com/fwlink/?linkid=524331’). You can also use Row Level Security to restrict access to data rows by creating a security policy based on characteristics of the user executing a query (‘https://go.microsoft.com/fwlink/?linkid=862687’).
Remediation Steps/Script:
- Run VA on your specific database to get remediation script but review each one!
  - It's stupid enough to think a date is for a Credit card and that an PK id is Financial info or National ID.

--Todo - add what kind of scripts might be generated in terms of what labels you can add yourself

VA2109¶

Name:
Summary and "why care" for dummies:
Risk Level:
Description:
Impact:
How to check if you db is affected:
Bulk query to find all problem databases:

Remediation Steps/Script:

VA1286¶

Name:
Summary and "why care" for dummies:
Risk Level:
Description:
Impact:
How to check if you db is affected:
Bulk query to find all problem databases:

Remediation Steps/Script:

VA¶

Name:
Summary and "why care" for dummies:
Risk Level:
Description:
Impact:
How to check if you db is affected:
Bulk query to find all problem databases:

Remediation Steps/Script:

Last update: July 17, 2020