9. Prevention, Detection and Mitigation Guidance

Welcome defenders 😄

The following guidance has been provided reduce the likelihood of attacks occurring against SQL Server implementations.

I encourage and welcome comments and additions to make this a more comprehensive guide.

Prevention

• If the SQL Server does not need to be exposed to other systems, disable the TCP/IP protocol via SQL Server Network Configuration Manager.
• Ensure that SQL Server and the underlying Microsoft or Linux server hosting the SQL service are encompassed in your patch management scope. Regularly apply security patches and updates.
• Follow security best practices when configuring SQL Server. Ensure that unnecessary services and features are disabled and that strong authentication methods are used.
• Regarding authentication modes, avoid using mixed-authentication mode where possible, opting for Window authentication, or certificate-based authentication in the case of Azure.
• Consider disabling the local sa user account. If this is not possible, ensure that it has a strong password that is rotated on a regular interval.
• Practice the principle of least privilege. Review who can authenticate against SQL Server databases. Ensure that "Authenticated Users" or "Domain Users" can not authenticate and map to a role. Grant only necessary privileges to users and roles.
• Review any users or groups that have been granted an impersonation role and limit access where possible.
• Consider denying impersonation logon events all together.
• Ensure that the SQL Server has been adequately segmented from network zones of lower security requirements. Validate this through network scanning from various network perspectives.
• Ensure that network routes to SQL Servers have been accounted for and limited to only an authorized set of systems, or subnets. Workstations rarely require the ability to communicate directly with SQL Servers. Consider blocking access to TCP 1433 if viable.
• Routinely evaluate integrated systems and data repositories (such as GitHub) to identify if SQL Server connection strings have been exposed anywhere.
• Remove unnecessary SQL service principal name associations.
• Encrypt sensitive content. This protects datasets from being exposed, even if the SQL Server is compromised.
• Evaluate links between SQL servers and determine the type of authentication binding the link. If possible, elect to use the current authentication security context, rather than using the context of the “sa” account.
• If using Azure SQL databases, ensure that Microsoft Advanced Threat Protection is enabled and configured to send alerts.

Detection

• Leverage SQL Server Audit to log whenever suspicious objects are called, such as:

  • CLR integration
  • OLE automation procedures
  • sp_add_job
  • xp_cmdshell
  • xp_dirtree
  • xp_fileexist

• Ensure that EDR has been configured to detect on suspicious parent/child process events, for example, sqlservr.exe calling powershell.exe.

• Implement application allow-listing to prevent arbitrary unsigned binaries, such as SQLRecon.exe, from executing on endpoints.

• Regarding custom .NET CLR DLL's being loading into SQL Server stored procedures, this article details YARA rules which can be expanded upon.

• Defenders should ensure that .NET Framework v4.8 is installed on Windows endpoints and that whatever host-based security product is being used supports AMSI for .NET. This allows the scanning of .NET assemblies in memory.

• Verify that network logging and monitoring tools are capturing SQL queries which traverse network boundaries. It would be unusual for a workstation to send SQL queries to a SQL server.

Mitigation

• Regularly conduct vulnerability assessments and/or penetration testing to identify and address any weaknesses in the SQL Server environment.
• If SQL Server needs to be exposed to environment-wide, consider using non-standard, or dynamic TCP ports for SQL Server and the SQL Server Browser service.
• Ensure that an incident response plan has been created and maintained to quickly respond to and mitigate any threats involving the SQL Server.