--- # ══════════════════════════════════════════════════════════════════════════════ # IEC 62443-3-3 SL2 — Microsoft SQL Server Compliance # # Target type : SQL Server 2016+ on Windows Server # Connection : WinRM to the Windows host; SQL checks run via PowerShell # Invoke-Sqlcmd on the target (no direct TCP/SQL connection # from the control node required) # Collections : ansible.windows # Python pkg : pywinrm # # Inventory group : mssql_servers (see assets.yml) # Add per-host var "mssql_instance" to target a named instance: # sql-srv-01.example.com mssql_instance=MSSQLSERVER # sql-srv-02.example.com mssql_instance=SQLEXPRESS # # Run: # ansible-playbook -i assets.yml methodologies/ansible/playbooks/examples/mssql_server.yml # # Prerequisites on target: # - SQLPS or SqlServer PowerShell module (Invoke-Sqlcmd) # Install-Module SqlServer -Force -AllowClobber # - WinRM enabled (see windows_server.yml header) # - Audit user needs: VIEW SERVER STATE, VIEW ANY DEFINITION on SQL Server # ══════════════════════════════════════════════════════════════════════════════ - name: "IEC 62443-3-3 SL2 — MS SQL Server Compliance" hosts: mssql_servers gather_facts: yes vars: report_dir: "../../reports" # Override per-host with mssql_instance inventory variable _sql_instance: "{{ mssql_instance | default('MSSQLSERVER') }}" # Invoke-Sqlcmd connection string fragment reused across tasks _sql_connect: "-ServerInstance . -TrustServerCertificate -ErrorAction Stop" pre_tasks: - name: "Ensure report directory exists" ansible.builtin.file: path: "{{ report_dir }}" state: directory mode: "0755" delegate_to: localhost run_once: true tasks: # ── FR1 · SR 1.2: SQL authentication mode (Windows-only preferred) ──────── # Mixed-mode (SQL + Windows auth) allows SQL logins with weaker controls. # IEC 62443 SL2 requires Windows-integrated (Kerberos) authentication. - block: - name: "Gather: SQL Server authentication mode" ansible.windows.win_shell: | Import-Module SqlServer -ErrorAction SilentlyContinue $result = Invoke-Sqlcmd {{ _sql_connect }} -Query " SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS WindowsAuthOnly, SERVERPROPERTY('ServerName') AS ServerName" [PSCustomObject]@{ WindowsAuthOnly = [int]$result.WindowsAuthOnly ServerName = $result.ServerName } | ConvertTo-Json -Compress register: _sql_auth_mode - name: "Evaluate: SQL-IAC-01 — Windows-only authentication" ansible.builtin.set_fact: _sql_auth: "{{ _sql_auth_mode.stdout | from_json }}" - name: "Evaluate: SQL-IAC-01 — record result" ansible.builtin.set_fact: test_results: "{{ test_results | default([]) + [{ 'test_id': 'SQL-IAC-01', 'category': 'FR1 — Identification and Authentication Control', 'requirement': 'SR 1.2 — Software Process and Device Identification', 'description': 'SQL Server shall use Windows Authentication only (not mixed mode)', 'passed': (_sql_auth.WindowsAuthOnly | int == 1), 'expected': 'IsIntegratedSecurityOnly = 1 (Windows auth only)', 'actual': 'WindowsAuthOnly = ' + (_sql_auth.WindowsAuthOnly | string) + ' on ' + _sql_auth.ServerName, 'severity': 'critical', 'remediation': 'SSMS → Server Properties → Security → Server Authentication: Windows Authentication Mode. Requires SQL service restart.' }] }}" ignore_errors: yes # ── FR1 · SR 1.3: SA account disabled ───────────────────────────────────── # The default "sa" superuser account shall be disabled when Windows auth is used. - block: - name: "Gather: SA account enabled/disabled state" ansible.windows.win_shell: | Import-Module SqlServer -ErrorAction SilentlyContinue $result = Invoke-Sqlcmd {{ _sql_connect }} -Query " SELECT name, is_disabled FROM sys.server_principals WHERE name = 'sa' AND type = 'S'" if ($result) { [PSCustomObject]@{ is_disabled = [int]$result.is_disabled } | ConvertTo-Json -Compress } else { '{"is_disabled": 2}' } register: _sa_account - name: "Evaluate: SQL-IAC-02 — SA account disabled" ansible.builtin.set_fact: test_results: "{{ test_results + [{ 'test_id': 'SQL-IAC-02', 'category': 'FR1 — Identification and Authentication Control', 'requirement': 'SR 1.3 — Account Management', 'description': 'The built-in SA (system administrator) login shall be disabled', 'passed': ((_sa_account.stdout | from_json).is_disabled | int != 0), 'expected': 'sa: is_disabled = 1 (or account not found)', 'actual': 'sa: is_disabled = ' + ((_sa_account.stdout | from_json).is_disabled | string), 'severity': 'critical', 'remediation': 'ALTER LOGIN sa DISABLE; -- run in SSMS or sqlcmd' }] }}" ignore_errors: yes # ── FR2 · SR 2.1: xp_cmdshell disabled ──────────────────────────────────── # xp_cmdshell allows OS command execution from SQL; must be disabled. - block: - name: "Gather: xp_cmdshell configuration value" ansible.windows.win_shell: | Import-Module SqlServer -ErrorAction SilentlyContinue $result = Invoke-Sqlcmd {{ _sql_connect }} -Query " SELECT value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell'" [PSCustomObject]@{ value_in_use = [int]$result.value_in_use } | ConvertTo-Json -Compress register: _xpcmd - name: "Evaluate: SQL-UC-01 — xp_cmdshell disabled" ansible.builtin.set_fact: test_results: "{{ test_results + [{ 'test_id': 'SQL-UC-01', 'category': 'FR2 — Use Control', 'requirement': 'SR 2.1 — Authorization Enforcement', 'description': 'The xp_cmdshell extended stored procedure shall be disabled', 'passed': ((_xpcmd.stdout | from_json).value_in_use | int == 0), 'expected': 'xp_cmdshell value_in_use = 0', 'actual': 'xp_cmdshell value_in_use = ' + ((_xpcmd.stdout | from_json).value_in_use | string), 'severity': 'critical', 'remediation': "EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;" }] }}" ignore_errors: yes # ── FR2 · SR 2.8: Login auditing level ──────────────────────────────────── - block: - name: "Gather: SQL Server audit level (0=None 1=Success 2=Failure 3=Both)" ansible.windows.win_shell: | Import-Module SqlServer -ErrorAction SilentlyContinue $result = Invoke-Sqlcmd {{ _sql_connect }} -Query " SELECT value_in_use FROM sys.configurations WHERE name = 'audit level'" [PSCustomObject]@{ audit_level = [int]$result.value_in_use } | ConvertTo-Json -Compress register: _audit_level - name: "Evaluate: SQL-UC-02 — Login auditing records failures and successes" ansible.builtin.set_fact: test_results: "{{ test_results + [{ 'test_id': 'SQL-UC-02', 'category': 'FR2 — Use Control', 'requirement': 'SR 2.8 — Auditable Events', 'description': 'SQL Server login auditing shall record both successful and failed logins (level 3)', 'passed': ((_audit_level.stdout | from_json).audit_level | int == 3), 'expected': 'audit level = 3 (both success and failure)', 'actual': 'audit level = ' + ((_audit_level.stdout | from_json).audit_level | string) + ' (0=None 1=Success 2=Failure 3=Both)', 'severity': 'high', 'remediation': "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\\Microsoft\\MSSQLServer\\MSSQLServer', N'AuditLevel', REG_DWORD, 3" }] }}" ignore_errors: yes # ── FR1 · SR 1.1: Sysadmin role membership review (HITL) ───────────────── # Automated: lists current sysadmin members. Human reviewer confirms list. - block: - name: "Gather: [HITL] Sysadmin role members" ansible.windows.win_shell: | Import-Module SqlServer -ErrorAction SilentlyContinue Invoke-Sqlcmd {{ _sql_connect }} -Query " SELECT sp.name AS principal_name, sp.type_desc AS principal_type, sp.is_disabled FROM sys.server_role_members rm JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id WHERE rm.role_principal_id = SUSER_ID('sysadmin') ORDER BY sp.name" | Select-Object principal_name, principal_type, is_disabled | Format-Table -AutoSize | Out-String register: _sysadmin_members - name: "Display: [HITL] SQL-IAC-HITL-01 — Sysadmin role membership" ansible.builtin.debug: msg: | ══════════════════════════════════════════════════════════════ MANUAL REVIEW REQUIRED · SQL-IAC-HITL-01 · {{ inventory_hostname }} ══════════════════════════════════════════════════════════════ Requirement : SR 1.1 — Unique User Identification Check : All sysadmin members are authorised and documented Current sysadmin role members ───────────────────────────── {{ _sysadmin_members.stdout | indent(1) }} Review against your authorised administrator list. Service accounts should NOT be sysadmin unless explicitly required. ══════════════════════════════════════════════════════════════ - name: "Prompt: SQL-IAC-HITL-01 — verdict for {{ inventory_hostname }}" ansible.builtin.pause: prompt: | Do all listed sysadmin members match the authorised administrator list for {{ inventory_hostname }}? Enter verdict [pass / fail / skip]: register: _hitl_sysadmin_verdict delegate_to: localhost - name: "Prompt: SQL-IAC-HITL-01 — notes on failure" ansible.builtin.pause: prompt: "Name the unauthorised principals found:" register: _hitl_sysadmin_notes delegate_to: localhost when: _hitl_sysadmin_verdict.user_input | lower | trim in ['fail', 'f'] - name: "Evaluate: SQL-IAC-HITL-01" ansible.builtin.set_fact: test_results: "{{ test_results + [{ 'test_id': 'SQL-IAC-HITL-01', 'category': 'FR1 — Identification and Authentication Control', 'requirement': 'SR 1.1 — Unique User Identification', 'description': 'All sysadmin role members shall be authorised and documented', 'passed': ( 'skipped' if (_hitl_sysadmin_verdict.user_input | lower | trim in ['skip', 's', '']) else (_hitl_sysadmin_verdict.user_input | lower | trim in ['pass', 'p']) ), 'expected': 'Only approved accounts in sysadmin role', 'actual': _sysadmin_members.stdout | trim, 'severity': 'critical', 'remediation': 'EXEC sp_dropsrvrolemember '''', ''sysadmin'';', 'reviewer': ansible_user_id, 'notes': (_hitl_sysadmin_notes.user_input | trim) if _hitl_sysadmin_notes is defined else '' }] }}" ignore_errors: yes # ── Generate report ──────────────────────────────────────────────────────── - name: "Generate compliance report" ansible.builtin.include_tasks: ../library/report.yml