6. Defensive Considerations

---

• 🛡️ Defensive Considerations
• Authentication and Authorization
• Surface Area Configuration
• Extended Stored Procedures
• Updates and Patches
• General Considerations
  • Server Security
  • Application Security
  • Firewall Configuration
  • Regular Security Audits

---

🛡️ Defensive Considerations

MSSQL Server offers a large number of security capabilities which may be fine-tuned to better protect an organization’s data, as well as to comply with any necessary regulations. Although we won’t cover everything in this section, we will discuss the most important ones.

---

Authentication and Authorization

Properly configured authentication and authorization settings are crucial to securing a MSSQL Server. In Microsoft’s documentation (SQL Server Security Best Practices) they make the following recommendations:

• Windows Authentication is preferred over SQL Server Authentication:
  • Uses the Kerberos security protocol
  • Supports additional password policies
  • Simplifies user management
• Principle of Least Privilege:
  • Users should be granted the minimum permissions required to carry out their tasks.
• Strong and complex passwords should be enforced for all logins:
  • For SQL Server Authentication: enforced by the server itself
  • For Windows Authentication: enforced by the domain

Besides Microsoft’s suggestions, defenders may also:

• Avoid assigning additional privileges to the public server role, which is assigned by default to every login.
• Revoke the guest database user’s CONNECT permission if it isn’t required (Microsoft Documentation).
• Disable and remove orphaned users (users without associated logins).
• Revoke sysadmin access for the BUILTIN\Administrators group:
  • This is assigned by default during installation.
  • Note: Attackers may still escalate from a local admin to sysadmin (see NetSPI blog post).

Server and database-level roles should also be carefully considered:

• serveradmin should be treated equivalent to sysadmin since escalation is trivial.
• db_securityadmin should be treated equivalent to db_owner for the same reason.
• db_owner can:
  • Drop the database
  • Escalate to sysadmin if assigned to a TRUSTWORTHY database.

---

Surface Area Configuration

MSSQL Server has many configuration settings, most of which are disabled by default. It is recommended to keep unused features disabled to reduce the attack surface.

Example to check what’s enabled on SQL01:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

Only essential settings should be enabled. Pay attention to:

• xp_cmdshell: used to execute code
• Ole Automation Procedures: interact with COM objects and execute code
• clr enabled: run user assemblies

Note: show advanced options itself is a config setting and should remain disabled unless needed.

---

Extended Stored Procedures

MSSQL provides a large number of extended stored procedures. Some of them (e.g., xp_dirtree, xp_fileexist, xp_subdirs) may be abused to capture NetNTLMv2 hashes.

⚠️ If not strictly needed, access should be revoked.

These procedures cannot be disabled but their EXECUTE permissions can be revoked:

REVOKE EXECUTE ON xp_dirtree TO public;

Result: general users (e.g., ws_user) will no longer be able to execute them.

📝 These procedures are undocumented and used internally by the server. Always test in staging environments before revoking access in production.

If necessary, grant EXECUTE to specific logins.

---

Updates and Patches

As with any software, MSSQL Server receives security patches and feature updates. Microsoft terminology:

• Cumulative Update: Roll-up of all previous hotfixes.
• Service Pack: Tested, cumulative set of all fixes and updates.

🛠️ Always keep MSSQL instances up-to-date. But first, test updates in a staging environment to avoid breaking production.

---

General Considerations

Even with a hardened MSSQL Server, a weak environment undermines all efforts. Here are additional important areas:

---

Server Security

The security of the server running MSSQL is as critical as the database itself.

• Keep the OS up to date to prevent exploits.
• Apply the Principle of Least Privilege to server access.
• Restrict file access to MSSQL Server’s files.
• Run MSSQL Server with the appropriate service account.

---

Application Security

Applications often serve as a gateway to MSSQL via SQL injection vulnerabilities.

• Review all applications that interact with MSSQL for SQL injection.
• Host applications and databases on separate servers:
  • Prevent attackers from accessing the DB directly if RCE occurs on the app server.

---

Firewall Configuration

Use network segmentation and restrict access to MSSQL ports.

• Default MSSQL TCP port is 1433.
• Block public access to MSSQL unless absolutely necessary.

---

Regular Security Audits

Even with best practices, human error happens. Conduct regular security audits, ideally by professionals.

• At a minimum, run:

    Invoke-SQLAudit
  

  from PowerUpSQL to check for common misconfigurations.

---

Let me know if you’d like this exported as PDF, Markdown, or DOCX format!