SQL Server Security

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

• SQL server authentication—SQL Server login can be vulnerable to connection string injection attacks. When a connection string is constructed at run time, unless the string is checked for valid keyword pairs, an attacker can add extra characters that can perform unauthorized actions on the server. A better option is to use Windows authentication, which is more secure.
• Privilege escalation—SQL Server databases typically contain sensitive data, and there is always a risk that users will assume privileges of more trusted roles, gaining unauthorized access to data and database functions. To mitigate this, prefer to run users as least-privileged accounts, and block the ability to execute code from administrative or owner accounts. If you need to grant extra permissions, use certificates to sign stored procedures or use impersonation, only for the duration of the task.
• SQL injection—SQL Server, like other databases, is vulnerable to attacks in which malicious users inject commands into query strings. These commands can damage or corrupt the database or be used to exfiltrate sensitive data. To prevent SQL injection, ensure you sanitize all database inputs to ensure they do not contain characters that can be used to execute code.
• Verbose errors—in many cases, SQL Server databases return error messages that include internal information, which can help attackers identify a vulnerability or plan an attack. Ensure that all procedural code uses error handling, to prevent default error messages from reaching the user.
• Denial of service attacks—production SQL Server databases may be subject to application-layer denial of service (DoS) attacks, in which attackers flood the database with fake queries, slowing performance for legitimate users, and eventually resulting in downtime. If you run SQL Server in the cloud, you can leverage DDoS protection services, which can capture and divert malicious traffic away from your database.
• Lack of security expertise and awareness—many database administrators are not sufficiently aware or trained on security issues, and may not know how to use the security capabilities of SQL Server. Any team that works on sensitive databases must have regular security training, and should work together with security experts to ensure SQL Server is securely configured.

• Blog: Data Security Projects Keep Data Teams Away From Their Core Responsibilities
• Satori For SQL Server
• Set a demo meeting

SQL Server Security Configuration

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

Conditional Access

• Role-based access—SQL Server lets you control access to data at three levels, the entire database instance, a database, and a table. Learn more about built-in and custom security roles in our detailed guide to SQL Server Roles.
• Data masking—you can automatically mask data, by replacing it with realistic fake data, to securely copy production databases and provide data for testing, training, and similar use cases. Learn more in our detailed guide to SQL Server data masking.
• Row-Level Security (RLS) and Column-Level Security (CLS)—you can define conditional access to specific rows or columns in a database table. This is a powerful tool for protecting sensitive data, or data subject to compliance requirements. Learn more in our detailed guide to SQL Server Row Level Security and SQL Server Column Level Security (coming soon).

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

• Encrypted communications—connections to SQL Server can be protected using SSL/TLS. SQL Server transfers data using the Tabular Data Stream (TDS) protocol, which is unencrypted by default. Administrators can provide a certificate and enable SSL/TLS, either by enabling “Force Encryption” in database configuration, or by specifying the “Encrypt” option in a specific application connection chain.
• Windows Data Protection API (DPAPI)—this is an encryption capability built into Windows—the encryption algorithm used depends on the version of DPAPI present on the Windows machine. At the SQL Server instance level, the security objects it can use are logins, server roles and credentials. At the database level, the security objects are users, certificates, functions, schemes, and encryption keys.
• SQL Server Service Key—is the basic encryption key used to encrypt data in SQL Server, also protected by DPAPI. This service key is created by SQL Server during the first startup. You cannot create it, but you can backup and restore the key on the same instance or other instances. You can also reset the Service Key on an existing SQL Service instance.
• Master Key—this is an additional, optional key in each SQL Server database, which can be protected by the service key or by a secure password provided by the user. You can backup the master key and restore it on the same or another database. Regenerating the service key will also regenerate all other keys, such as symmetric or asymmetric master encryption keys.
• Transparent data encryption (TDE)—a feature provided by Enterprise, Developer, and Data Center editions of SQL Server. Lets you encrypt the entire database and its log file with a provided key. TDE is transparent for the user and applications accessing the SQL Server database. It lets you choose from several encryption algorithms including AES 256 bit and Triple DES.

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.