A Python-based tool for auditing Microsoft SQL Server 2019 instances against CIS security benchmarks.

• Automated checks for all major CIS SQL Server 2019 recommendations
• Per-database and per-instance security configuration checks
• Windows Authentication support by default
• Simple pass/fail output for each control

• Python 3.6+
• pyodbc library
• ODBC Driver 17 for SQL Server
• Access to SQL Server with appropriate permissions

1. Ensure Python is installed on your system
2. Install required dependencies:

   pip install pyodbc
   

3. Download the Microsoft ODBC Driver for SQL Server if not already installed

Run the script directly:

python mssql2019audit.py

By default, the script connects to a local SQL Server instance using Windows authentication.

Edit the connection string in the script to match your environment:

conn_str = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;"
    "DATABASE=master;"
    "Trusted_Connection=yes;"
)

The tool implements the following CIS benchmark checks:

• 1.1: Ensure Latest SQL Server Cumulative and Security Updates are Installed
• 2.1: Ensure 'Ad Hoc Distributed Queries' Server Configuration Option is set to '0'
• 2.2: Ensure 'CLR Enabled' Server Configuration Option is set to '0'
• 2.3: Ensure 'Cross DB Ownership Chaining' Server Configuration Option is set to '0'
• 2.4: Ensure 'Database Mail XPs' Server Configuration Option is set to '0'
• 2.5: Ensure 'Ole Automation Procedures' Server Configuration Option is set to '0'
• 2.6: Ensure 'Remote Access' Server Configuration Option is set to '0'
• 2.7: Ensure 'Remote Admin Connections' Server Configuration Option is set to '0'
• 2.8: Ensure 'Scan For Startup Procs' Server Configuration Option is set to '0'
• 2.9: Ensure 'Trustworthy' Database Property is set to 'Off'
• 2.10: Ensure Unnecessary SQL Server Protocols are set to 'Disabled' (manual)
• 2.11: Ensure SQL Server is configured to use non-standard ports
• 2.12: Ensure 'Hide Instance' option is set to 'Yes'
• 2.13: Ensure the 'sa' Login Account is set to 'Disabled'
• 2.14: Ensure the 'sa' Login Account has been renamed
• 2.15: Ensure 'AUTO_CLOSE' is set to 'OFF' on contained databases
• 2.16: Ensure no login exists with the name 'sa'
• 2.17: Ensure 'clr strict security' Server Configuration Option is set to '1'
• 3.1: Ensure 'Server Authentication' Property is set to 'Windows Authentication Mode'
• 3.2: Ensure CONNECT permissions on the 'guest' user is Revoked within all SQL Server databases
• 3.3: Ensure 'Orphaned Users' are Dropped From SQL Server Databases
• 3.4: Ensure SQL Authentication is not used in contained databases
• 3.5–3.7: Ensure SQL Server service accounts are not administrators (manual)
• 3.8: Ensure only the default permissions specified by Microsoft are granted to the public server role
• 3.9: Ensure Windows BUILTIN groups are not SQL Logins
• 3.10: Ensure Windows local groups are not SQL Logins
• 3.11: Ensure the public role in the msdb database is not granted access to SQL Agent proxies
• 3.12: Ensure the 'SYSADMIN' Role is Limited to Administrative or Built-in Accounts
• 3.13: Ensure membership in admin roles in MSDB database is limited
• 4.1: Ensure 'MUST_CHANGE' Option is set to 'ON' for All SQL Authenticated Logins (manual)
• 4.2: Ensure 'CHECK_EXPIRATION' Option is set to 'ON' for All SQL Authenticated Logins Within the Sysadmin Role
• 4.3: Ensure 'CHECK_POLICY' Option is set to 'ON' for All SQL Authenticated Logins
• 5.1: Ensure 'Maximum number of error log files' is set to greater than or equal to '12'
• 5.2: Ensure 'Default Trace Enabled' Server Configuration Option is set to '1'
• 5.3: Ensure 'Login Auditing' is set to 'failed logins'
• 5.4: Ensure 'SQL Server Audit' is set to capture both 'failed' and 'successful logins'
• 6.1: Ensure Database and Application User Input is Sanitized (manual)
• 6.2: Ensure 'CLR Assembly Permission Set' is set to 'SAFE_ACCESS' for All CLR Assemblies
• 7.1: Ensure 'Symmetric Key encryption algorithm' is set to 'AES_128' or higher in non-system databases
• 7.2: Ensure Asymmetric Key Size is set to 'greater than or equal to 2048' in non-system databases
• 7.3: Ensure Database Backups are Encrypted
• 7.4: Ensure Network Encryption is Configured and Enabled
• 7.5: Ensure Databases are Encrypted with TDE
• 8.1: Ensure 'SQL Server Browser Service' is configured correctly (manual)

Results are displayed in a simple pass/fail format for each CIS control:

CIS SQL Server 2019 Compliance Checks
------------------------------------
[✔] 2.1 'Ad Hoc Distributed Queries' set to 0 - PASSED
[✘] 2.2 'CLR Enabled' set to 0 - FAILED
[✔] 3.3 Ensure Orphaned Users Are Removed - PASSED
[MANUAL] 2.10 Ensure Unnecessary SQL Server Protocols are Disabled (Check SQL Server Configuration Manager)
...

Manual checks will be clearly indicated in the output.

• Some checks require manual verification or GUI review and are marked as [MANUAL] in the output.
• For best results, run the script with an account that has sufficient privileges to query system views and execute configuration commands.