SQL Server Security

Here are three common security threats that affect SQL Server databases:

SQL Server Security Configuration

Microsoft SQL Server provides several layers of security: conditional access, auditing, and encryption.

Conditional Access

Auditing

Microsoft provides SQL Server Audit, a tool built into SQL Server, which reads transaction logs and records information about data and object changes in your database. You can use it to create server-level audits and database-level audits, with several auditing levels.

Learn more in our detailed guide to SQL Server Audit

Encryption

Using a Firewall with SQL Server

Beyond these built-in security layers, it is strongly recommended to protect a SQL Server database with a firewall. When setting up a firewall, you need to ensure the essential ports SQL Server needs to communicate are open—these are TCP 1433 (database engine), 2383 (analysis services), 4022 (service broker), and UDP 1343 (browser access).

SQL Server Security Best Practices

Run Routine Security Audits

To help prevent potential attacks and assist post-breach forensic analysis, routine audits of server security, login, and permissions are a necessity. This is no longer a mere investment in security; HIPAA, GDPR, and other regulations now require these audits by law. Your first step in an audit is defining what you wish to monitor. This should include server configuration, schema changes, SQL server auditing, and other common compliance criteria. You should also check C2 auditing (a form of logging specified by the US Department of Defense), SQL trace, change data capture (CDC), and extended events, as well as DML, DDL, logon triggers, user logins, and login auditing. A high priority should be identifying and responding to modified or missing database objects, repeated server login failures, and permissions or configuration changes. All of these could indicate a security breach. Routine audits also contribute to maintaining a healthy database and network. Running each query may not only highlight a security threat; it may indicate an error in the SQL execution plan. Seeking out and resolving the underlying reason may prevent future issues.

Have a Strong Password Policy

All database administrator accounts require strong passwords to defend against brute force attacks. Use at least 10 characters, including numbers, upper and lower case letters, and special characters. Preferably, enforce passwords of 15 characters or more. Ensure that administrators select passwords that are not easy to guess, and educate them against using the same password on multiple systems. Ensure that administrator passwords are changed regularly. It is a good practice to use a password management tool shared by the database administration team, with a strong master key.

Deploy and Test SQL Server Updates

Operating system service packs and upgrades, as well as SQL Server updates, contain important security enhancements. Test these updates with your database application, and then apply them as soon as possible.

Use a Firewall

Firewalls are also a very effective security control. A firewall restricts network traffic according to a set of predefined rules, and can be configured to enforce your data security policy. By putting your database behind a firewall, you create another layer of security preventing malicious traffic from reaching your server. Firewalls can also help prevent malicious outbound traffic, such as command and control (C&C) communication or data exfiltration.

Use Encryption

Encryption cannot solve access control problems. However, if an attacker does manage to bypass access controls, encrypting data at rest using features like TDE can limit data loss and render the data worthless to attackers. In addition, set up secure channels using SSL/TLS and leverage the Windows Data Protection API to ensure encryption of all data in transit.

Avoid Installing Non-Essential Software

Installing more applications and components on the machine running your SQL Server instance gives attackers additional entry points. In addition, you spend more time updating and managing this software, and if you fail to update it, unpatched software results in dangerous vulnerabilities. Even within SQL Server itself, it is recommended that you only install the components and features required for a specific task. This had an additional benefit of reducing resource requirements and simplifying administration.

Use a SQL Monitoring Tool

Use a SQL monitoring tool to maintain your network, protect it from attacks, and ensure optimal performance. The tool should be capable of scanning database application processes and detecting server settings changes. It should be able to identify sessions, errors, and individual SQL statements that imply access to sensitive data. Remember that the monitoring tool is itself a potential security hazard, due to its contact with the system and its access to sensitive data. Ensure that your SQL monitoring tool itself is protected by stringent security controls.

Use a Data Access Controller

Data Access Controllers provide visibility into data access and apply data access policies. Services such as Satori inspect every transaction and database response and build an audit of data access activity, an inventory of the sensitive data locations, a policy engine to define granular data access policies and a workflow mechanism to approve or deny access to datasets.